This tutorial shows how to make a bubble chart.
Problem: When you try to create a bubble chart in Excel, it creates as few series with as many data points as possible. But usually we want to have a lot of series with just one set of variables (x and y axis values and bubble size).
Solution: Create a chart, and manually add many series
Problem with Solution: It takes too much time to do that, you have to go and add every single series. if you have 30, that's a lot of repetitive work
Solution: Use bubble chart templates
here are some:  
typical bubble chart: one series with many sets of data points
desired bubble chart: many series with single sets of data points
step1. let's set up our table first. I used the "Series Name", "X axis", "Y axis", "Bubble Size" convention
Series Name |
X-axis Value |
Y-axis Value |
Bubble size |
A |
1 |
3 |
5 |
B |
0.5 |
0.8 |
50 |
C |
1.5 |
2 |
10 |
|
|
step2. without selecting the table, insert a bubble chart (Alt, N, O)
|
|
step 3. while the empty chart is selected, hit select data (Alt, J,C,E). The following menu will pop up:
|
|
step 4. hit add, then this will pop up:
|
|
step 5. now, you have to enter four data points for each series.
Once one is entered, repeat step 4 until all of your data has been entered into the chart. |
|
with a couple of easy changes, here's what my bubble chart looks like (i played with the formatting a little bit...)
sample graphs
in the template i played around with a couple of different chart types. the first one was countries and population. to make it really boring, i put their coordinates for X and Y values. check it out:
about the author / improving bubble charts
I work at a boutique management consulting firm. We often have to use bubble charts to compare several indicators.
For example, as part of a business benchmarking study, a typical chart would plot the " innovation" metric for the company vs number of employees and also show the overall revenue (size of the bubble).
It gets quite interesting when we decide to add other dimensions. Sometimes adding the z axis can be interesting, but I try to avoid it because it's hard to read and interpret. By playing with the border of bubbles (thickness + style + color) it's possible to add profit margins or other indicators. It's also interesting to turn the bubble into a harvey ball (or a percentage, or a pie chart for that matter) to indicate growth, margins, etc. |
