fbpx
Search
Close this search box.

A better chart to visualize “Best places to live” – Top 100 cities comparison Excel chart

Share

Facebook
Twitter
LinkedIn

Recently, I saw this chart on Economist website.

It is trying to depict how various cities rank on livability index and how they compare to previous ranking (2014 vs 2009).

Best cities to live - Chart from Economist.com

As you can see, this chart is not the best way to visualize “Best places to live”.

Few reasons why,

  • The segregated views (blue, gray & red) make it hard to look for a specific city or region
  • The zig-zag lines look good, but they are incredibly hard to understand
  • Labels are all over the place, thus making data interpretation hard.
  • Some points have no labels (or ambiguous labels) leading to further confusion.

After examining the chart long & hard, I got thinking.

Its no fun criticizing someones work. Creating a better chart from this data, now thats awesome.

So I went looking for the raw data behind this graphical mess. Turns out, Economist sells this data for a meager price of US $5,625.

Alas, I was saving my left kidney for something more prominent than a bunch of raw data in a workbook. May be if they had sparklines in the file…

So armed with the certainty that my kidney will stay with me, I now turned my attention to a similar data set.

I downloaded my website visitor city data for top 100 cities in September 2014 & September 2013 from Google Analytics.

And I could get it for exactly $0.00. Much better.

This data is similar to Economist data.

Data of top 100 visitor cities - SEPTEMBER 2014 & 2013 - Chandoo.org

Chart visualizing top 100 cities

Here is a chart I prepared from this data.

Top 100 cities excel chart - demo

This chart (well, a glorified table) not only allows for understanding all the data, but also lets you focus specific groups of cities (top % changes, new cities in the top 100, cities that dropped out etc.) with ease.

Download top 100 cities visualization – Excel workbook

Click here to download this workbook. Examine the formulas & formatting settings to understand how this is made.

How is this visualization made?

Here is a video explaining how the workbook is constructed. [see it on our YouTube channel]

 

The key techniques used in this workbook are,

Since the process of creating this visualization is similar to some of the earlier discussed examples, I recommend you go thru below if you have difficulty understanding this workbook:

How would you visualize similar data?

Here is a fun thought experiment. How would you visualize such data? Please share your thoughts (or example workbooks) in the comments. I & rest of our readers are eager to learn from you.

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

Excel School made me great at work.
5/5

– Brenda

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.

Weighted Average in Excel with Percentage Weights

Weighted Average in Excel [Formulas]

Learn how to calculate weighted averages in excel using formulas. In this article we will learn what a weighted average is and how to Excel’s SUMPRODUCT formula to calculate weighted average / weighted mean.

What is weighted average?

Wikipedia defines weighted average as, “The weighted mean is similar to an arithmetic mean …, where instead of each of the data points contributing equally to the final average, some data points contribute more than others.”

Calculating weighted averages in excel is not straight forward as there is no built-in formula. But we can use SUMPRODUCT formula to easily calculate them. Read on to find out how.

12 Responses to “A better chart to visualize “Best places to live” – Top 100 cities comparison Excel chart”

  1. Herbert says:

    Hi Chandoo,
    This is a great chart (table). I can make good use of this in my dashboards, especially when adding a scroll-bar form control.

    regards,

    Herbert

  2. Andreas Schuderer says:

    Thanks for the article, that's a nice interactive table, kudos.

    When improving charts, I like to take a step back and try to figure out the chart's purpose. Should it tell a story? If yes, what is that story? Or is it just there to make a single, concise point? As we know, the same data can be expressed in a wide variety of charts.

    Chandoo's solution is a hearty "let the user decide," which is valid. It certainly encourages playing with the data and exploration.

    I think the fun lies in trying to figure out the intention of the original chart and improving on it. In this example, the authors apparently couldn't really make up their mind if they wanted to focus on *change* or on *ranking*. That's why Chandoo's solution is so ingenious.

    I wonder if we tried to focus on showing *change* and tried to chart this question expressively, what we could come up with...

    • Andreas Schuderer says:

      ... continuing my thoughts from yesterday:

      Actually, slope graphs can be quite suitable to show change, e.g. slope graphs are good to identify the exceptions of a general trend. Unfortunately, in the above example, there is simply too much going on, so the authors split it in 3 parts, by which they forfeit some good reasons for drawing a slope graph in the first place. Slope graphs can be, if not too busy, good in identifying differences in magnitude of change (something done by giving numbers in Chandoo's table).

      One way to improve on the original chart would be to make one slope graph per region or continent, and put them all next to one another on one page. Also, don't show any data points that currently don't have a label, and make only one slope graph per region (not 3 for up, unchanged, down).

      This chart would show several things:
      - Do particular regions have particular trends (i.e. do the cities of these regions develop in the same direction, or do ups and downs seem random within one region).
      - How do the trends (if any) between the regions compare?
      - Are there exceptions to trends within one region (where e.g. a particular city goes down while others go up, or one city goes up or down much stronger than others)
      - If these are the top X cities, which continents are overrepresented, which are underrepresented.

      Here is a quick sketch to give an idea (the layout is pretty rough):
      http://schuderer.net/pub/experiment_slope_graph_cities.png

  3. Nick Ehrlich says:

    Chandoo,

    Been following you for awhile now. Love the tips.

    I love this exercise. It's a great example of taming an over ambitious infographic.

    When making these types of tables I always start with pivots, so here's a rework of your workbook using only pivots and no controls. I started by reorganizing your data into a table with a year field and then using pivots to do a lot of what you did with the extra calcs and tables in your data. Let me know what you think.

    http://nickehrlich.com/Top10Cities_nge.xlsx

  4. […] created an interactive chart to compare the top 100 cities from 2 time periods, from his website visitor data. Is your city in the list? Mine […]

  5. Matt Johnson says:

    I loved the new table. I especially liked how it did not use macros, but was fast and effective and displaying the data. One question. I have something similar I need to do, but it also has to filter data selected by the user. So, if the user only wanted to look at cities with visits over 10,000, would there be a way to add that into this model? Thanks in advance. Love this site!

  6. Nick Ehrlich says:

    @Matt Johnson: Here's a solution that adds several switches (two as calculated fields in the first pivot and one as a calculation field in the table range) that provide for filtering on predetermined thresholds. If you wanted the user to enter their own threshold in you could add a named cell for user input and have the calculation reference that threshold in the formula. I've included both methods in the following update, but I'd only choose one method before rolling out to users. Having both a hard coded switch and a variable switch would probably cloud up the UX.

    http://nickehrlich.com/Top10CitiesV2_ngeV2.xlsx

    • LeonK says:

      Nick, your solution using pivots and slicers has the advantage of being used through Microsoft onedrive online with Android devices where a model with object controls cannot be; they are stripped out on opening. This affords a greater degree of flexibility and brings data modelling up to date with some of the mobile technology in the workplace. IOS cannot, as far as I can tell, utilise slicer controls. I've tested on a Nexus 7 2nd Gen and iphone 6 with ios8 both using the office.com site and a onedrive account - not conclusive but it's what I have.

      One could argue that you've just pushed the barriers out that little bit further. As modelers, we should be finding ways to implement new features of Excel to create faster solutions that are easier to support and flexibly delivered to where they can be used by the biggest audience.

      No one has a monopoly on good ideas and, Nick, your solution is a very good one. Thank you for sharing it. I will be building on the concept in my workplace.

  7. MF says:

    Cool. I like your way to self-make the "Slicer" for selection by using Form Control + Shape. Nice. 🙂

  8. Sameer says:

    have two software outputs in excel to integrate and import data see below

    Sheet 1 (My data Dictionary column names for soft1 and soft2 )
    Soft 1 ----- Soft2
    Code --------- codenumber
    Fname ------- first name
    IDCODE-------- id

    Soft2 data sheet columns

    Codenumber : First name :id

    I want vba to pick column names from softdata2 sheet and find soft1 equivalent and replace in soft2 data ;

  9. Ally says:

    Hi All
    I would like to learn how to create the form control on the report tab, is there any Youtube tutorial or website materials that you can point me to?

    Thanks

Leave a Reply