Bubble charts are great, but they are not always so easy to read, especially when the bubbles overlap by a significant amount. Consider instead, the Cherry Chart:
A bubble chart plots three different characteristics of the data. So let’s imagine that we want to plot the percentage growth and profitability margins of ten different products plotted on the X and Y axes of our graph. However we also want to show the sales volumes, which would normally be represented by the size of the bubble. Instead of having a bubble though, we will use a line to show the sales volume instead. The length of the line increases as the volume of sales increases. To make sure that the line fits in the graph, we will also have to scale the line to match the size of the vertical axis. How is it done? You can download a sample excel sheet here.
Step 1: Set up your data
Create a data table showing the three characteristics as shown below:
Then create four new columns, “Y”, “Y2”, “X” and “X2”. We are going to calculate the values in these columns to use as the series for the chart.
Assuming column “Y” is column E and you’ve used the same columns and rows as above, the formula in cell E2 should be:
If you remember, to make sure that the line of the cherry (size of the line = sales volume) fits in the graph, we also have to scale the line to match the size of the vertical axis. Therefore, underneath the table, enter the value you want to use to scale the line. I’ve used 150,000. You can find it in cell D14.
Assuming column “Y2” is column F, and your scaling factor is in cell D14, the formula in cell F2 should then be:
The “$” indicates that this value (both the column letter and the row number) should not change when / if you copy and paste this cell, which we will be doing soon.
Assuming column “X” is column G, the formula in cell G2 should be:
And finally, assuming column “X2” is column H, the formula in cell H2 should be:
Once you have entered the calculation for the first row of each of the four new columns, highlight these four cells, and then position the mouse over the bottom right of the highlighted section. The cursor should change into a thin black cross shape instead of a white fat one. Once that happens, drag the cursor down until you have covered the remaining blank rows. Excel will automatically copy and paste the first row into the blank rows, filling your table with data.
If the formatting of the table is messed up as a result of the last action, click on the small auto-fill options icon that immediately appears when you stop dragging, (you can see it in the picture above at the bottom right of the table). Choose to fill without formatting as shown below:
Step 2: Create the chart
Highlight columns A, E, F, G, and H and then in the ribbon, click on Insert > Scatter Chart > Scatter with Straight Lines
At the moment it doesn’t look anything like the first chart, but don’t worry – we have some customization to do. First right click on the chart and choose “Select Data”. In the pop up menu box that appears, click on the “Switch Row/Column” button and press the “OK” button.
This means that we now have lines for each of the different products. Go ahead and delete the legend as we are not going to need it. Right click on the graph again and choose “Select Data”. Click on the first series (product A). The problem is that Excel hasn’t added the columns into the graph the way we want them, so press the “Edit” button.
For product A the series X values should be:
The series Y values should be:
You can either type this in or click on the browse worksheet button and use your mouse to highlight the correct columns / rows as explained above. Repeat this for each product. Your chart should now look like this:
Step 3: Formatting
The rest of the chart is actually just formatting. First let’s adjust the axes. Double click on the vertical axis.
On the Axis Options tab (as shown above), adjust the axis so that it crosses the horizontal axis at the maximum axis value (last check box). Then click on the number tab, change the number to percentage, and change the number of decimal places shown to zero. You can then hit the close button.
Then double click the horizontal axis, and click on the number tab. Change the number to percentage, and change the number of decimal places shown to zero. You can then hit the close button. Your chart should now look like this:
The next step is to add the cherry to the bottom of the line. Click once on the first series in the chart. Two small squares should appear at the top and bottom of the line. Click on the bottom square and then press Ctrl+1 to open up the formatting pop up menu box.
Without pressing the close button, click on the next line, and its bottom point and add a cherry to that line as well. Repeat until every line has a cherry. You can then press the close button.
Finally, we need to add data labels. Ensure that the graph itself is selected, then on the ribbon click on the layout tab of the chart tools section. Then click on Data labels > Left to add a standard data label to every point.
Excel automatically uses the value of the point as the data label. To change this, click on the first data label and press Ctrl+1.
In the pop up menu that appears (above), deselect the Y value label and instead select the series name label. If necessary, you can adjust the position of the label as well by selecting right etc. By default, both the top and bottom points of the line have a label. To get rid of the top one, while both are still selected (and with the menu box still open), click once on the top label and then press the delete key. You can then click on the next line to repeat the procedure until every line is correctly labelled.
And that’s it. As mentioned earlier, you can download the spreadsheet I used here. Hope you find it useful!