How to Choose the Right Chart in Excel
Why are the right charts in Excel so important?
The wrong Excel chart can not only lead to confusion but may lead to the wrong decisions being made. Which should you use?
Happy's expert trainer Darren Andrews explains more in this blog.
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
Ruby Alden-Gibson2 days ago
Really enjoyed the course. It was informative and I learnt loads!
Trusted Customer3 days ago
Very good, the process was easy and communication good. Mostly spoke to Laura so it would be good if all her emails had come from her name email address.
Trusted Customer3 days ago
Fully satisfied with the service and location of the training.
Trusted Customer6 days ago
Trent Grassian7 days ago
An incredible training, which was so engaging and informative. Really got a lot out of it and would highly recommend itt o others!
Joanne Spear7 days ago
thanks, the course was simple and easy to follow and i found the presenters approach very friendly.
Wilma Wheatley7 days ago
The tutor was great in including everyone. answered all the questions i had before attending
Trusted Customer7 days ago
Happy's training courses enhanced my knowledge and gave me new skills which I can use in my job to make things more efficient.
Jodie Moore8 days ago
Great course with a great tutor. Very interactive with good learning experiences.
Trusted Customer8 days ago
Great course, enjoyable and useful.
Jackie Costin8 days ago
Roumiana Grigorova8 days ago
I am very happy, taking this training. I would say I am one step more confident. This was an interesting and very useful experience. Our lecture was a great, I have to sa...