How to Choose the Right Chart in Excel

Unless you have a mind like Nobel laureate John Nash, being presented with tables of figures will provide little enlightenment. If you’re less familiar with the numbers concerned you may find it impossible to find the key story, the trends and exceptions that help you truly understand what’s happening in your organisation.

So what do us humans like to look at? Well, of course cats on the internet, but when it comes to Data we need pictures. Other than the aforementioned Mr Nash we are not naturally attuned to deciphering numbers. Using the right chart can transform these impenetrable figures into a simple message that anyone in your organisation can understand.

One of Happy’s key principles is honesty and transparency. So once a quarter Happy’s CEO, Henry, talks all members of staff through the company’s latest figures and forecasts for the year ahead at our regular staff meetings. He gives us all a small report and, because you just can’t take the trainer out of our CEO, he asks us questions about the finances to make sure we all understand them.

We struggle through the tables, but show us Henry’s charts and we get it, just like that! (By the way, it’s as much a test of how good Henry’s charts are as it is our understanding!)

How to choose the right chart

The wrong chart can not only lead to confusion, but may lead to the wrong decisions being made, damaging your organisation. This is because the same data, charted in different ways, can illustrate or obfuscate exactly the same message. Plus, in the latest version of Excel, there are 17 categories of chart type – with a myriad of variations on each category!

So, the very first thing to look at is the type of data that you want to use in your chart and what you would like to show – as that will determine which chart is the best one to use. We’ll look at the most common types of problems.

The Pie Chart

As pie charts can only display one series of data, they are perfect for displaying the values as a percentage of a whole number.

For example, in this chart, you can see the total number of sales of each brand of ice cream. It’s very easy to see at a glance that Magnum’s have been overall the most popular, followed by the Cornetto.

You could also use it to display how many sales of each ice cream you’ve had in one month, for example.

If you tried to use a pie chart to display the last year’s data, Excel would simply ignore the other columns and only show the first (very clever!).

These charts can be customised to show in 3D, as well as to show different information in the legends – with the exact figure on each slice of the pie. In our example, this might be helpful when comparing the Cornetto, Mars and Twister sales to see which is most popular – as each slice is very similar in size.

The Line Chart

You might recognise the Line Chart from school. These are the best way to show trends over time.

In a Line Chart, the vertical axis (Y-axis) always displays numeric values and the horizontal axis (X-axis) displays time or other category.

In our example, it’s very easy to see that July and August are the most popular months for total ice cream sales (as you might expect).

Line Charts can be selected with or without markers at each point (to make it easier to see where each value is), and you can add different trend lines to the graph – but any more than three, and it becomes hard to read and understand easily!

The Column Chart

The Column Chart is also known as a bar graph – and is another one that you might recognise from school!

Column Charts most effective at comparing one or more series of data points – in our example, we’ve used it to compare the sales of different ice creams during January, February and March. The colour of each month is consistent for each type month, making it easy to compare how well each product is doing in any given month.

The vertical axis (Y-axis) always displays numeric values, while the horizontal axis (X-axis) displays the time segments (or other category).

Another option is the Stacked Column Chart, which shows each piece of information ‘stacked’ on top of each other, with the timeline along the X-Axis. This chart gives a much better idea of the percentage of sales for each ice cream type.

Column Charts are also available as 2D or 3D versions. Newer version of Excel can also display the information as cylinders, pyramids, or cones instead of bars.

The Scatter Chart

Finally, the Scatter Graph is similar to the Line Chart – it shows how two series of data compare over time. It is designed to identify a relationship and correlation between two data sets. In our example, we’ve shown ice cream sales vs ice lolly sales, to show if one type of product is more popular at certain times of the year.

Data on the X Axis is labelled chronologically, so on our example December 2015 and January 2017 are shown even though they are not included in our data set.

The data points can be indicated with circles or crosses, or they can be connected with smooth or straight lines. Generally, lines work best when there are a large number of data points.

Other chart types include the Area Chart, the Stock, the Doughnut, the Bubble, and Radar.

Charts are a very powerful way of communicating and like all powerful tools must be understood and used well. While the newer versions of Excel will try to provide suggestions for which chart is best, its suggestions are not always to be trusted, and you should always consider the data you are using and the story behind them that you are trying to tell.