4 Charts – Same Data. Which do you think is best?

Often when you’re charting data it can be tricky to find the best chart for the job.

If you get it wrong at best it can be a waste of everyone’s time, and at worst it can be misleading.

That’s why you need to know the motivation for the chart up front. What is the question that the chart is supposed to answer?

Of course sometimes you will be analysing data to look for patterns and a story to tell. In either case, knowing which chart displays what type of information is important.

Here we’re going to look at 4 different ways to present the same data and the different questions each chart answers.

The Data

Below is some data which I found on The Science Goddess’s site, Excel for Educators.

The Science Goddess used this data to show you how you can improve on the default charts that Excel spits out. It’s worth a read.

Caveat: Since I am not familiar with this data I don’t know what the ‘question’ is that we need to answer. Instead I'm going to look at the different charting options we have available for this type of data, and explain what questions each chart allows you to answer. As a result some of the charts may analyse the data in a way that wouldn't apply in reality. Just so you know 😉

Chart 1 – Panel Chart or Small Multiples

Panel Chart or Small Multiples Key Points

1. Don’t repeat labels or axes. I have just one vertical axis on the first chart which serves all 5 charts.
2. Likewise, if space is limited reduce axis labels where they can be implied, as I have done for my horizontal axes where I’ve only labelled 2004 and 2013 for each chart.
3. Make sure all charts are the same size and colours are consistent. Note also that only 1 legend is required for all 5 charts.
4. Fix all charts to the same minimum and maximum axis height and align them. It’s natural that people will compare one chart to the next so it’s imperative that each chart uses the same scale so you don’t mislead the reader or make it difficult to read.
5. You can add data labels if you want, like I have done in the first chart. If you do this it’s a good idea to do it for every chart and then you can delete the vertical axis as it’s redundant.
6. You can build these charts by creating 5 separate charts and then aligning them together, or you can create a ‘panel chart’ in Excel with some jiggery pokery.

Panel Charts or Small Multiples are good for

Allowing comparison of each category side by side (in this example, ethnicity and student/staff), enabling you to detect patterns in the data.

For example we can quickly see that Students outnumber staff in every ethnicity except for White. Asian, American Indian and Black have not changed significantly since 2004. Hispanic students have increased the most while White students and staff have both decreased.

This approach assumes there is a relationship between staff and student numbers, which may not be the case.

Sparklines

Sparklines are a new feature in Excel 2010. They can be used in a similar way to the Small Multiple charts although they take the ‘small’ idea further, in that they fit into a single cell.

Sparkline Key Points

1. They typically don’t show an axis.
2. You can choose from line, column or win/loss Sparklines.
3. You can use them to show trends in a series and highlight maximum and minimum values by applying markers to the Sparkline.
4. They are applied to the background of the cell as opposed to being an object that floats above the worksheet like a regular chart.

Sparklines are good for

Enhancing a table of data, as patterns can be difficult to see in a mass of numbers. A Sparkline gives you a quick way to see a pattern thus giving the numbers context.

In the above example the reader is less able to make comparisons between the categories and so the message is focussed on the individual ethnic group’s pattern over time.

Line Chart

Line Chart Key Points

1. Because there are quite a few lines in this chart it’s easier for the reader if you label each line as opposed to using a legend.
2. This chart isn’t trying to do too much. It only plots Students. To include Staff would make it too cluttered.

Line Charts are good for

Seeing trends over time. The line implies there is a relationship from year to year.

Plotting each ethnic group in the one chart allows the reader to make comparisons between the groups. Who is biggest, second biggest etc. as opposed to making comparisons between student and teacher trends like we did in the first panel chart.

Bar Chart A

Bar Chart A - Key Points

1. This bar chart removes the time category and instead focusses on two points in time (2004/05 vs 2012/13) and the overall change.
2. By overlapping the two columns we’re able to make a quick visual comparison of then and now.
3. The labels tell us the exact change with an upward facing triangle indicating a positive change and a downward facing triangle indicating a negative change.
4. Sort the data to aid interpretation. For example we can easily see which ethnic group is biggest, second biggest etc.  Note: if your data already has an implied order then sort by the implied order e.g. Highly Developed, Developed, Developing… etc.
5. By segregating Students and Staff we imply that there isn’t a relationship between the two groups.
6. A gap between each bar emphasises that the categories are discreet. Unlike a line chart which is continuous to indicate there is a relationship from one category (typically time) to the next.

Bar Charts are good for

Comparing a large number of classes or groups.

They are also good for displaying data with long titles as they avoid the need to angle or wrap them like you do with a column chart.

Bar Chart B

Bar Chart B - Key Points

1. This is a variation of bar chart A; however instead of two columns, 2004/05 is represented by a marker.
2. This chart is cleaner/less cluttered and slightly easier to read. Unfortunately inserting the marker requires a bit more effort (and possibly some Red Bull) than Bar Chart A with the two overlapped columns.

Summary

I hope I’ve illustrated (pun intended 😉 ) that there isn't a 'best' chart out of the ones above. It comes down to choosing the chart that best conveys your message.

By no means is this a comprehensive list of different ways to display this data visually. If you have an example you’d like to share please email it to me via the Help Desk and I’ll add it to this post, or leave me a comment below.

Want More?

If you’d like to learn more about data visualisation and dashboards please consider my popular Excel Dashboard course.

I’ll teach you the fundamentals for data visualisation and I’ll show you the tricks I used to create the charts above, plus a load more.

And to give you a headstart download my free 30 Tips for Better Charts eBook here.

Data Visualisation Inspiration and Help

If you already know how to create amazing charts in Excel, but you’re not sure how to best display your data then Helpmeviz.com is a great place to ask your question and receive feedback, suggestions and examples from members of the data visualisation community.

Or just take a look at some of the previous posts for inspiration.