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
Right level, including and relaxed feeling.
The course was well structured and the tutor was very patient and knowledgeable and helped us all. My headset decided to die about 10 minutes from the end but he wasn't p...
Trainer was excellent. Involved the whole group. Speed and tempo of training was just right. Were able to bring our own experiences and plans for the future.
Amanda Adegoke1 day ago
Really informative and relevant contact and the delivery was excellent.
Sam Giles3 days ago
Instructor was patient and tailored everything to people of all levels.
Trusted Customer3 days ago
Online classes are not a good idea when you have to use one screen to look at three things at the same time. Listen to the tutor, look at what they do on one screen and a...
Trusted Customer3 days ago
Training was great, easy to follow.
Pablo Barrio3 days ago
Great tips during the training. I liked a lot the spreadsheet that helps decide what structure is better depending on the problem at hand.
Trusted Customer4 days ago
Straightforward and efficient. Course was easy to book; communication was straightforward; course joining instructions were clear.
Trusted Customer13 days ago
Fairly good customer experience - all training materials provided on time. Good reminders leading up to the course.
Tom Ellis14 days ago
Good course, with a good and involving lead presenter on it. Felt it really delivered against the brief.
Trusted Customer14 days ago
Excellent communication throughout