1 (888) 505-5689

Data Visualization How-To: Comparative Bubble Charts

May 2, 2013

This week during an infographic critique we were brainstorming ways to make a certain data set more effective and clear. We decided that a comparative bubble chart would be an effective method as it would allow a reader to easily compare and comprehend the difference in the values.
We started testing out the idea and I was quickly reminded that making a custom bubble chart can quickly go awry if the math behind the chart is not understood.

Math is not always a right brained graphic designer’s strength. That can be problematic when designing an infographic as effective infographic design requires more than just an understanding of what looks nice and how to make things “pop.”

Today we will walk through the custom creation of a bubble chart together. We will use the following common cooking measurement data:

1 tsp.
1 Tbsp. is 3x’s bigger than a tsp.
¼ cup is 12x’s bigger than a tsp.
1 cup is 48x’s bigger than a tsp.
1 pint is 96x’s bigger than a tsp.

The above facts translate into the following data:

chart1josh4.30

1. Where we go wrong

I will first outline the process that I see many designers go through resulting in a misleading visualization.

Step 1: Open Illustrator (or Photoshop, or whatever design program you use)
Step 2: Draw the first circle. Doesn’t matter what size as it will set the scale.
Step 3: Copy the original circle and select it.
Step 4: Go to Object > Transform > Scale
Step 5: Enter the % you want to scale the circle by. For example if you need it to be 2Xs bigger, enter 200% into the Scale field. Click Ok.
Step 6: Repeat until you’ve scaled a circle for each value in your data.

Sounds perfect, right? Wrong.
Below you will see the result you get when you follow that process with our cooking measurement data. As you can see, you get off track pretty quick and the results can be very misleading.

joshpic2

Here, the radius of the circle is being scaled. The resulting area covered by the circle is far greater than it should be. If the radius of the circle is used to represent the data values, then the area of the circle will quadruple if the data values double, for example. If that mathematical explanation is busting your brain, at least conclude that this method doesn’t work. Just take a look at the above result.

2. One way to do it right

Now we will go through how to do it correctly. I am positive this is not the only way to do it. It may not even be the easiest way to do this. Either way, I do know this way works and it will at least enable us to do it correctly. Anyone who knows of a better or easier way to do this please share your technique with us in the comments below.

Step 1: Open Microsoft Excel

(if you don’t have Excel I imagine you can also do this in the free OpenOffice spreadsheet software. You can download that here)

Step 2: Enter your data into the spreadsheet

To create a bubble chart in Excel your data needs to be in sets of 3 values. The third value is used to determine the size of the bubble. In our example data we don’t have sets of 3 values. We have sets of 1 value (# of tsp.). Since we only have sets of one value we will place filler values in for the first 2 numbers in each set. Those filler values (columns B and C) will determine where the bubbles appear on the chart. For the first value I put in 1 for each. This made it so that all the bubbles were graphed on the same y axis. For the second value I entered 5, 10 , 15, 20 and 25. These values space the center of each circle 5 apart. We may need to adjust this when we see the final chart so that the bubbles don’t overlap. If they overlap we won’t be able to trace them accurately.

joshchart2

Step 3: Create the chart

To create the chart select all the sets of values and go to Insert > Other Charts > Bubble Chart

Step 4: Adjust spacing as needed

The chart below is what was generated based on the our selected data. You will notice that our bubbles are overlapping. We need to adjust the second data point in the 1 cup and 1 pint data sets so that they don’t overlap. Again, this is controlled by the numbers in column C in our spreadsheet.

josh5pic If we change the numbers from 20 and 25 to 26 and 40 respectively, then we get the following result which is much better.

joshpic6

Step 5: Copy graph into Illustrator

Simply highlight the graph in Excel, right click it and select copy. Then go into Illustrator and hit ctrl+v. That should paste in your chart.

Step 6: Trace the graph

Next, just use the ellipse tool to draw circles of the appropriate size on top of the chart. You can group the circles and scale them to the size you need. In the image below you see I drew black circles on top of the Excel chart. In the final step I grouped the circles, rotated them and scaled them to be bigger.

Step 7: Stylize your graph to your liking

Below you will find a quick final design of the bubble chart. Once you are certain that you have circles scaled to the appropriate sizes you can adjust the styling to match your infographic. When scaling the circles just make sure you scale them all together and maintain the original size ratio. As you can see these bubbles make much more sense.

josh8pic

Before you finalize the design of the chart take one last look at the visualization to make sure the scaling is correct. In this example 1 Tbsp is 3x’s bigger than 1 tsp. Does the area covered by 3 of the 1 tsp. circles match the area of the 1 Tbsp. circle?

joshpic8