Elegant Growth

In: Global Economy Graphic Tools Innovative

19 Apr 2012

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:

image

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.

image

10 Responses to Elegant Growth

Avatar

Noah Iliinsky

April 19th, 2012 at 1:53 pm

Couldn’t this also be solved by using open circles instead of filled 3D bubbles?

Avatar

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.

Avatar

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. 😉

Avatar

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?

Avatar

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.

Avatar

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!

Avatar

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.

Avatar

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

Avatar

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.

Avatar

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)

Comment Form