The Bubble Chart in Excel is a hugely useful and powerful chart that is often overlooked and under utilised. It’s so useful because it can be used to represent three values and show the relationship or correlation between them.
In the example below we have three measures imaginatively named Measure1, Measure2 and Measure3.
We will plot each of these measures on a Bubble Chart placing Measure1 on the vertical Y axis, Measure2 on the horizontal X axis and Measure3 will be used to determine the size of the Bubble (the Z axis).
To do this we first highlight our data (without the headings) and then go to the Insert Ribbon and select the Bubble Chart from the Charts menu (shown below).
We now see something like this:
We are now in a position to give the bubble chart a makeover; but before we do this lets first check which measures are shown on each axis. To do this right click on the chart and choose Select Data. In the box that appears click on the Edit button that appears under Legend Entries (Series). You should now see a box that looks like this.
We can see that the horizontal X axis is represented by column A, the vertical Y axis by column B and the Bubble Size (Z axis) by column C which is what we wanted. However we could change these settings using the little box at the end of each Series reference (the little box with the Red arrow in it). We may even choose to use the same measure twice, once for the Y axis and once for the size perhaps.
Now we can add a title, change the style of the chart and adjust the chart settings accordingly. In the below I have changed the chart style to style 8, which can be selected from the Design Ribbon, and I have changed a couple of Format Data Series options which we can access by right clicking on any of the Bubbles and selecting Format Data Series.
- Fill set to Vary Colours by point
- Series Option set to Scale bubble size to 75. This reduces the proportionate size of the bubbles and reduces any overlap should you have this issue
We can now clearly see that as Measure1 increases Measure2 increases also, but there is no correlation between Measure3 and the other two measures.