Sometimes you have to strangle software to get what you want. I was looking for a new way to compare world growth across analytical groups. Starting with an excel bubble chart, I noticed that sorting the values by growth rate, and sizing them by GDP value, produces a very beautiful visualization of the distribution. Looking closer, however, I noticed that excel literally draws the graph in the sorted order (lowest to highest in this case), resulting in some of the smaller balls being hidden by the larger ones:
To fix this turned out to be quite complicated, requiring some software hopping. First you have to copy and paste the chart into Powerpoint, then right-click/save-as-picture into an enhanced metafile (.emf), which you can then open in Illustrator where you can bring all the hidden balls to the front. Anyways, the end result is below. I hope the technique is useful to anyone looking to do some post-production excel chart tweaking.
An addictive collection of beautiful charts, graphs, maps, and interactive data visualization toys -- on topics from around the world.
10 Responses to Elegant Growth
Noah Iliinsky
April 19th, 2012 at 1:53 pm
Couldn’t this also be solved by using open circles instead of filled 3D bubbles?
Johann
April 19th, 2012 at 3:16 pm
You can also ungroup (a couple of times maybe) the pasted chart in PowerPoint, and send the large bubbles to the back. No need for Illustrator.
Dustin
April 19th, 2012 at 4:02 pm
Johann – I tried that with a paste special, but the quality goes to shit for some reason and the balls start doing weird things like taking bites out of each other. Using Powerpoint 2007 anyway…
Noah – good idea. But not as aesthetically pleasing perhaps. 😉
Michael Thompson
April 19th, 2012 at 4:06 pm
I was dealing with this same problem two weeks back. What I ended up doing was creating a matrix with x,y coordinates, and creating an algorithm for assigning the data points to a coordinate. Ended up with some better clustering. This works as long as the only variable that interests you is the ‘size’ variable. But then again, if only size matters, then maybe a different kind of chart is in order?
Andy Kriebel
April 20th, 2012 at 2:00 am
First, why would you ever use 3D bubbles? What value is the 3D adding to your viz?
Moving the larger bubbles to the back won’t solve the problem either. You need to use transparency.
This can all be accomplished in under 10 seconds with Tableau.
francoisinjapan
April 20th, 2012 at 5:52 am
Yes, I agree that the 3D effect is killing the first impression you get from this graph!
Josh W
May 1st, 2012 at 12:44 pm
Isn’t this only a problem because you are using the sort order to determine both the x-axis and the order of plotting?
Dissociate the two by creating a variable with the rank to use for the x-axis location, then you can sort by size (or indeed anything else) to get the desired plotting behaviour.
Akshay C
May 5th, 2012 at 11:49 am
Do you have the source data for this? I’d be interested in seeing the detail at country level
Stuart
May 12th, 2012 at 10:13 pm
I’m somewhat unclear as to whether GDP is proportional to the radius of the bubbles, area of these bubbles (i.e. if the bubbles are projected onto a 2D surface), or the volume of the bubbles.
Dust
May 14th, 2012 at 2:54 pm
Yep, the 3d does make it confusing. The label should read “area of circles = GDP in USD)