How countries spend their money – chart alternatives

Share

Facebook
Twitter
LinkedIn

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.

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Overall I learned a lot and I thought you did a great job of explaining how to do things. This will definitely elevate my reporting in the future.
Rebekah S
Reporting Analyst
Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

21 Responses to “Make a Pivot Table in Excel [15 Second Tutorial]”

  1. [...] First step is to create Pivot Table. Here is a video tutorial on making pivot tables. [...]

  2. Wilson says:

    Dear Sir,

    I would like to study pivot table from basics detailed.Pls send me the tips of that

  3. baran says:

    merccccccccccccccccccccccccccccccccccccc

  4. [...] Click here to see a video tutorial of making pivot tables in excel | One more Pivot Table Video Tutorial [...]

  5. surbamanyam says:

    it is very useful

  6. jitender says:

    hello sir

    please send me some information of pivotable. and how to use it
    because I am beginer so how can i learn it the pivotable.
    and sir will you send me pivotable data because i have no data and i don't
    know how to applyed for the pivotable. give some videos so I learn quikely.

    thank you
    and my best
    anticipation

  7. keri says:

    Hi,

    This tutorail was helpful. If possible can you please email me some data, so I can create another pivot table? I am trying to learn ASAP and need some info.

    Thank you

  8. yemi says:

    Top of the day to you. can you pls send me a detailed tutorial on the pivot table tutorial? Thanks.

  9. karthick says:

    Hi,
    please tell me how to use Pivot table.

  10. Pooja says:

    Dear Sir,
    I visited this website of urs for the very first time and trust me this is fabulous.i learnt pivot table through this.Thanks a lot.In corporate world nobody gonna make u learn these things.m very grateful to u.This is a great initiative from ur side.Commendable job done.Hats off to u.For beginners also this is very helpful.The way this website makes u learn excel is very good.

    Pooja

  11. Priscilla says:

    Can you please email me information to put into a pivot table and possibly a complete set of instructions?  I am a beginner to pivot tables and am possibly interested in obtaining a position that requires pivot tables.  thank you.

  12. [...] Click here to see a video tutorial of making pivot tables in excel | One more Pivot Table Video Tutorial [...]

  13. Kawsar says:

    It's simply great..!!

  14. James Perry says:

    Cool!
    Fast but not furious.

  15. Adepoju Teniola says:

    Pls email me details of pivot table. I want to learn it asap because I just got a job that requires this. Thanks

  16. nihal ahmed says:

    it is very useful..............thanks

  17. Tabassum says:

    Sir please can u send me the complete explanation of pivot tables from basics

  18. Guruprasad says:

    hello,

    I wanted to know how to put pivot with emp id, emp name, emp position in one row.

    Ex:-
    cel A4 should have Emp id
    Cell B4 should have Emp Name
    Call C4 should have Emp Position

    many thanks in advance

  19. George Dorne says:

    Nice information, thanx my dear.

Leave a Reply