Quick Guide:
How to create a Bubble Chart in Google Sheets in a few easy steps.
1. Prepare the data with proper structure
2. Select the data. Go to Insert→ click chart
3. Double-click the chart to open the chart editor window
4. Navigate to Setup→Chart type→ click the drop-down box and select bubble chart
Pop! You have successfully created a bubble chart in Google Sheets.
Sample Google Sheets template with formula here.
Google Sheets allow its users to create a number of charts that permit users to visualise several data variables at once. One such example is the bubble chart. The bubble chart in Google Sheets allows the users to visualise not one, not two, but a minimum of three variables at once!
While the first two variables are the (x,y) coordinates on the graph, the third and the subsequent variables measure the sizes of the (x,y) coordinate. Bubble Chart in Google Sheets allows you to measure correlations between all the permutations of variables that have been plotted in the chart
Let’s learn how to create a bubble chart in Google Sheets through the steps below.
Steps to create a Bubble Chart in Google Sheets
Let’s assume we work at a bank. We are required to understand the relation between the size of the population, the number of borrowers and the % of borrowers who have borrowed more than $10 million. To do so, we create a bubble chart in Google Sheets using the steps below.
Step 1: Prepare the dataset according to the format mentioned below:
a. Column 1– Should represent the label of each bubble
b. Column 2 – Value for X-axis
c. Column 3 – Value for Y-axis
d. Column 4 – Different values here would be shown using different colors in chart
e. Column 5 – the size of the bubble that will be displayed
To complete our task, our data needs to be formatted as shown below:
a. Column 1: represents the name of the city
b. Column 2: represents the population to be displayed on the X axis.
c. Column 3: represents the number of borrowers to be displayed on the Y-axis
d. Column 4: represents the colors of the bubbles which will be formed. This column can be used to show a trend or differentiate between variables on a completely different parameter.
e. Column 5: The size of the bubble is represented by the percentage of people who have borrowed more than $1 million.
Once formatted accordingly, our dataset will look like this:
Step 2: Select the data. Navigate to Insert→ Click Chart.
On clicking the Chart option, you will find a chart displayed on your screen.
The default chart type based on your settings will be displayed on the sheet.
Step 3: Double-click on the chart to open the Chart editor window. Navigate to Setup→ under the Chart type option click on the drop-down box → select Bubble chart.
Once, you select bubble chart, the following graphic will be displayed on your screen.
We observe that borrowers in Chicago pose a huge risk to the bank in case of default when compared to the other cities. This is due to the fact that not only does Chicago house a larger population but also the percentage of borrowers with a loan amount of more than $1 million is huge, relatively.
Editing the bubble chart in Google Sheets
To edit the bubble chart in Google Sheets, double-click on the chart and head to the customise tab in the chart editor window.
The different sections under the customise tab are used to edit different aspects of the bubble chart in Google Sheets.
- Chart Style: To edit the background colour and border colour of the chart.
- Bubble: To edit the font style of the text inside the bubble.
- Chart and axis titles: To add and edit the font style of the text inputted as titles
- Series: To edit the style of the bubble and the colour used to represent data in the bubble.
- Legend: To edit the positioning of the legend of the chart along with the font style
- Horizontal axis: To change the style of the data represented in the horizontal axis
- Vertical axis: To change the style of the data represented in the vertical axis.
- Gridlines and ticks: To decide whether gridlines and any kinks need to appear on the axes or not.
As you make the changes, they will simultaneously be reflected in the bubble chart created. After making a few changes, the same bubble chart as above can be modified to resemble one as shown below:
When to create a Bubble Chart in Google Sheets?
Much like a scatter plot, a bubble chart is used to understand the correlation between the numerical values. However, the addition of the third variable allows us to compare three different variables instead of two. Through the bubble chart in Google Sheets, we can make 3 distinct comparisons (between X and Y, Y and Z, and X and Z) and a three-way comparison among all three variables.
To understand and learn how to calculate correlation click here.
Conclusion
The article summarizes how we can create a bubble chart in Google Sheets and make quick data-driven decisions when we have three variables that are related to each other in one way or the other.
To learn to create more such informational charts, read the articles on our blog.
See Also
You may have a look at the following articles for more guides on how to create different charts in Google Sheets:
How to create a Pareto chart in Google Sheets