How to Choose the Right Chart in Excel
Why are the right charts in Excel so important?
Hi, we are Happy
We are leading a movement to create happy, empowered and productive workplaces.
How can we help you and your people to find joy in at least 80% of your work?
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.
Why not sign up to our newsletter?
Sign up to our monthly newsletter, full of tips, tricks and news to help you to be happier and more productive at work.
Darren is one of Happy's Senior Trainers, able to train almost every IT course on our course programme. He worked for Happy for 12 years and has been an Associate Trainer since 2018.
Testimonials from happy Customers
Siobhan Brennan-Robson7 days ago
Ebiji was an excellent teacher. I will be keeping her on speed dial.
Sophie Allen7 days ago
the initial invitation didn't come to all participants so I wasnt sure if the training was confirmed
Trusted Customer8 days ago
Amazing work! Honestly, I have had such a pleasant experience with Happy whether it be online or in their offices. Hopefully, in the future, I get to have more sessions w...
Trusted Customer10 days ago
I found the Office 365 training really useful. It consolidated my self-taught learning and showed me some new things that will make my life easier as well as doing some t...
Trusted Customer10 days ago
Found the session interactive and engaging throughout. Range of tools shared.
Helen Twigg11 days ago
Really enjoyed the Liberating Structures Online Immersion Workshop. High energy that kept you involved all day and have been able to use the learning immediately. Woul...
Ruth Gibbins11 days ago
Great facilitation, very interactive and organsied
Emma Willcox11 days ago
Great course, with lots of opportunity to practice techniques and not just learn about them. The day is packed full of content, so be ready to embrace the fast pace and t...
Judy Duff11 days ago
Happy are a creative, unique, relevant and modern-day approachers of workshop delivery and content.
Trusted Customer13 days ago
Excellent course and facilitator :) The course had good interactive sessions and well thought out content. It was made relevant to our roles and the trainer was supportiv...
Scott Haines14 days ago
I thought the training was excellent. It was really enjoyable and engaging and gave me lots of ideas to take away.
Toks Kinoshi14 days ago
An excellent session led by Henry where I learnt some new, refreshing techniques