How would you Visualize World Education Ranking Data [Homework + Video]

Here is a charting challenge to begin your Christmas week. Recently Guardian’s Data Blog released World Education Rankings data and a sample visualization (shown below).

Kaiser at Junk Charts took this data and suggested a few alternative visualizations [part 2]. (shown below).

While Kaiser‘s charts are probably more insightful, they also appear complicated to my layman eye.

Naturally I wanted to give this data a charting-shot and see what comes up.

But before I show you how I cooked my chart, I want to throw a challenge to you.

Your Homework – Make a chart from World Education Rankings data

1. Download the original data from here (or from here).
2. Make a chart (or few charts) visualizing the data.
3. Your objective is make it easy for us to understand the World Education Rankings Data
4. Upload your workbooks to Skydrive or some other public file sharing site.
5. Share the URLs, images etc with us thru comments.
6. Bask in glory!

How I visualized World Education Rankings Data

When I looked at the original data, I wanted to explore 2 things.

• How are the scores in reading, math & science are distributed? [Distribution]
• How does one country compare with another? [Comparison]

To keep it simple and compact, I made one chart that meets both these objectives.

Here is what I could come up with:

How is this chart constructed (Recipe)

• The chart is a scatter plot with scores for each area plotted with a different y value (reading = 1, maths = 2 and science = 3)
• The chart is also dynamic. Visit Excel Dynamic Charts page if you are new.
• The four selected countries and average are extra series in the chart with diff. formatting.
• The messages are constructed using RANK formula and concatenate operator &
• Other tricks used – incell dropdown boxes, text boxes with formulas, symbols, and chart formatting.

Since the process of making this chart is a bit more detailed, I made a youtube video explaining it. See it below.

[Watch the video on Youtube]

Download the Excel Workbook

Click here to download the workbook. The file works best in Excel 2007 or above. Try the Excel 2003 version if you prefer.

Now your turn,

Go ahead and download the original data. Make your own visualization of World Education Rankings and post it using comments. I am waiting 🙂

Learn more Excel Magic

If the above chart feels like magic, you will be wowed by these additional resources:

• Excel Dynamic Charts – Techniques & Downloads
• Visualization Principles – Making Better Charts
• Visualization Projects – Kickass Excel Magic