How countries spend their money – chart alternatives

Posted on September 22nd, 2015 in Charts and Graphs - 4 comments

Econimist’s daily chart is a one of my daily data porn stops. They take interesting data sets and visualize in compelling ways. While the daily chart page is insightful, sometimes they make poor charting choices. For example, this recent chart visualizing how countries spend their money uses a variation of notorious bubble chart. Click on the chart to enlarge.

20150912_woc650_1

What is wrong with this chart?

Bubble charts force us to measure and compare areas of circles. Unless you have a measuring tape somehow embedded in your eyes and you are a walking human scientific calculator, you would find this task impossible.

So when you look at the chart and want to find out what percentage Japanese spend on restaurants or how much Americans pay for housing, your guesses will have large error margins.

Not only bubble charts are difficult to read, they are very hard to align. So when you have a bunch of bubbles, no matter how hard you try, your chart looks clumsy (see how the Russian food bubble eats in to Mexico’s bubble, as if it is too hungry 😉 )

Let’s check out a few alternatives to this chart

The simplest alternative for all the bubble madness? Use bar charts!

Bar charts are easy – you can make them in no-time, your audience can read them in no-time. 2X time saved. What not to like 🙂

Alternative 0 – Straight replacement of bubbles with bars:

This one is simple. We take the data, apply conditional formatting > data bars on top of it. We can add an additional rule to show only MIN & MAX values in each row and hide the rest of the values with a custom formatting code – ;;;

This is what you get:

alternative-0-how-they-spend-it-chart

The above chart is way better than bubbles. If you want to shift the focus from country to expense category, you can transform the same chart.

Related resources:

Alternative 1 – Transformed bar chart

alternative-1-how-they-spend-it-chart

Again, same techniques, applied on transformed data set.

Alternative 2 – Highlighting above & below average values in different colors

While conditional formatting data bars are fun and simple, they can only show up in one color. So if you want a few bars to be in different color based on a condition (for ex: all values less than average in different color), you need to venture beyond the data bars.

We can use 2 techniques:

  1. Create in-cell bar charts, using REPT formula and color the bars with conditional formatting
  2. Create a regular bar chart with two series of data – above & below average and color them differently

REPT formula approach is fun and easy. Using that, we get this:

alternative-2-how-they-spend-it-chart

Related resources:

Alternative 3 – Adding labels to MIN & MAX values too

Once we have the REPT() based chart, we can add extra columns to conditionally show the data labels too.

This is what we get:

alternative-3-how-they-spend-it-chart

Download ‘how they spend’ chart alternatives

Click here to download the Excel workbook containing all these charts. Examine the formulas & conditional formats to learn more.

More charting stories & case-studies

Check out below examples to learn few more powerful ways to tell stories using charts. In cell panel chart to visualize survey results

How would you visualize this data?

What do you feel about the bubble chart? If you think it is a poor choice, how would you visualize this data? Please share your thoughts and implementations in the comment section.

4 Responses to “How countries spend their money – chart alternatives”

  1. Ben C says:

    Hey Chandoo,

    Great post and interesting to see this use of REPT formula. I learn something new every time I visit your site!

    I think the original bubble chart could improved slightly by displaying values when you hover over a given bubble, that way at least you could get exact values.

    I agree that the bubble chart is notoriously difficult to read from, so only gives a sense of the underlying data. The bar chart version is definitely easier to compare, although with the bars being so small and having no axes or labels, it's still quite hard to compare them.

    I think there's always going to be a trade-off when you try to display this much data in a single chart.

    Thanks,
    Ben

  2. Chris says:

    Chandoo,

    Always like these references too - they continue to do some great work with visualisation.

    For my money though, it appears that it is a % of total spend for each country?

    And as such it would be good to see the Total Spend available compared for each country in some fashion.

    CF

  3. Rahul says:

    Hi Chandoo,
    How do I show only MIN & MAX values in each row

  4. Vikram says:

    Hi Chandoo,
    Saw these awesome bubble charts with overlays - http://www.bloomberg.com/politics/graphics/2015-presidential-money-map/

Leave a Reply