Locked lesson.
About this lesson
Understand that not all charts are created equal and select the most appropriate chart type for your data.
Exercise files
Download this lesson’s related exercise files.
07-01-Choose the Right Chart-Start.xlsx904.2 KB 07-01-Choose the Right Chart-Complete.xlsx
904.4 KB 7.01 choose-the-right-chart-type - Exercise.docx
42.9 KB Exercise - Choose the Right Chart Type.xlsx
36.1 KB 7.01 choose-the-right-chart-type - Exercise solution.docx
84.5 KB Exercise Solution - Choose the Right Chart Type.xlsx
49.5 KB
Quick reference
Choose the Right Chart Type
Learn how to choose the right chart type for your data.
When to use
We use a chart whenever we want to visualize a dataset. It's important to know which chart type is most appropriate for our data.
Instructions
It is important to select the right chart to display our data. Not all charts are created equal. Choosing the wrong chart can make data harder to read and interpret for our audience.
When considering a chart, there are some basic guidelines we can use to help us make the right selection.
Too Much Data?
A common issue when creating charts is trying to show too much data. If we have a PivotTable that shows data for 150 countries, trying to plot these successfully on any chart is going to be problematic.
You can see in the example below, the data looks chaotic and is hard to read and interpret. We also can't see all data points as there simply isn't enough room.
To prevent this, consider refining the dataset to only show the top results.
Use a Top 10 Filter on the data to select the Top 5 countries by revenue instead. This produces a more manageable dataset.
Column and Bar Charts
Column and Bar Charts suit most types of data. If in doubt, use a column or a bar chart to represent values.
Line Charts
Line Charts are another popular chart type in Excel.
Line Charts are based used when we have time-based data as they show trends over time.
We can format line charts to show markers at each data point making them simple to read.
Pie and Donut Charts
Pie and Donut Charts are also popular chart types in Excel.
Pie and Donut Charts work best with small, comparison data. For example, maybe we want to show the number of males vs females that work at a company. This data would be perfect for a Pie or Donut Chart.
We recommend not using these types of charts if you have values that are very similar as the pie chart slices will look virtually identical.
Map Charts
Map Charts are very effective at displaying geographic data.
However, Map Charts (and other chart types) cannot directly display PivotTable data. There is a workaround for this that we will review in another lesson.
We can easily display geographical data that isn't in a PivotTable using a Filled Map Chart.
There are many other chart types in Excel that are very specifically targeted toward certain types of data. For example, Histogram charts will show data that are grouped into bins or buckets. Funnel and Treemap charts show how data is distributed etc.
We will be taking a look in more detail at some of these chart types in the next few lessons.
Login to download- 00:04 In this section of the course, we're going to shift our focus very slightly to
- 00:10 talking more about how we can visualize our analysis using charts.
- 00:15 And remember,
- 00:16 you don't necessarily have to have your data inside a pivot table in order to
- 00:20 create a pivot chart, you can create a chart based off of any table data.
- 00:25 Now in our example, we are going to use pivot table data.
- 00:29 And you can see here on the screen, we're using the same data that we
- 00:32 used in previous lessons when we were building this pivot table.
- 00:36 So this pivot table currently shows the country in the rows and
- 00:41 the sum of total profit.
- 00:42 Now what I want to talk to you about is something very important, and
- 00:46 that is choosing the right chart type.
- 00:49 Because if we get the chart type wrong, it can make our results look messy or
- 00:54 even make them difficult to read and interpret.
- 00:57 And there are a few very loose rules when it comes to the type of chart that you
- 01:02 should use based off of the data that you're trying to display.
- 01:06 So let's just dive in and take a look at what some of these rules are.
- 01:10 Now the analysis that I'm currently doing here is a very large analysis,
- 01:15 because we have so many different countries.
- 01:17 Now if I wanted to put all of this information into a chart,
- 01:21 this is going to look terrible.
- 01:23 If we jump up to PivotTable Analyze and click on the PivotChart button,
- 01:28 you can see in the preview if I wanted to do a column chart,
- 01:31 that is going to be really difficult for anybody to understand and interpret.
- 01:36 And it doesn't even have enough room to display all of the information.
- 01:41 So in a situation like this where maybe you do want to display the sum of
- 01:45 total profit by the country, you might want to think about refining
- 01:50 your data first to display the most important information.
- 01:54 So maybe, instead of just having all of the countries,
- 01:58 we might refine this so that we're only looking at the top five or the top ten.
- 02:04 So what I could do here is right-click and go to a Filter, and
- 02:08 we can choose Top 10 filter.
- 02:10 So now I can say I want to filter this list by the top,
- 02:14 and I'm just going to do the Top 5 items by Sum of Total Profit.
- 02:19 So it's basically going to give me the top five countries.
- 02:23 So this is a much more manageable dataset to put into a chart.
- 02:28 Now, something else that you want to be careful of here is if you have
- 02:32 grand totals or subtotals turned on in your pivot table,
- 02:35 you don't necessarily want to display the grand total in your chart.
- 02:39 So what we would do there is jump up to design and
- 02:42 simply turn off our grand totals.
- 02:45 So now with this smaller data set, if we choose to insert a pivot chart, and
- 02:50 I'm just going to go with a column chart, you can see that looks a lot tidier and
- 02:55 it's a lot more simple for people to read.
- 02:57 So that's the first thing, don't put too much information in your charts.
- 03:03 Now when it comes to selecting the right chart for your data,
- 03:07 if we jump into PivotChart again, what I would say to you as a general rule is
- 03:12 that column charts and bar charts, which are basically column charts that
- 03:17 are flipped the other way, are pretty good for displaying most types of data.
- 03:22 So if in doubt, go for a column or a bar chart.
- 03:26 But what about something like a line chart?
- 03:29 Well, again, you can use a line chart to display different types of data, but
- 03:34 in general it's best for displaying timeline-based data.
- 03:38 So let's take a look at an example.
- 03:40 Now, I'm going to rearrange some of my pivot table fields here.
- 03:43 And what we're going to do is we're going to organize these by, let's go for
- 03:47 Item Type, and we're going to add a date into here.
- 03:50 So let's go for Order Date.
- 03:52 And I'm going to get rid of Quarters,
- 03:54 Order Date, so that we just have the years.
- 03:57 And I think I'm going to move Item Type across to Columns,
- 04:02 and now we're going to create our pivot chart.
- 04:05 So if we jump up to PivotChart and select Line this time, you can see what we get.
- 04:10 Now, once again, this is quite a lot of information to display in a chart,
- 04:14 it can look a bit confusing.
- 04:15 We probably could get away with this, but once again,
- 04:20 maybe I might want to refine it down to just the top items.
- 04:24 So let's delete this out.
- 04:26 Let's right click and go to Filter, Top 10, and
- 04:30 I'm going to choose the top 3 this time.
- 04:33 So now our chart should be a little bit more manageable and easier to read.
- 04:40 But in general, when you have time-based data, so you can see here it's running
- 04:45 across from 2015 to 2022, a line chart is the best way of displaying this data.
- 04:51 Now, what about something like a pie chart or a doughnut chart?
- 04:55 Well, these are probably my least favorite chart types, but
- 04:58 people do still love to use these.
- 05:00 So if we use our analysis here, let's try and create a pie chart.
- 05:05 Let's see what that looks like.
- 05:06 Well, in this case, that's not going to look too bad.
- 05:09 What I would say with a pie chart is that the smaller the data set that you're
- 05:14 analyzing, the better.
- 05:15 So, for example, if I remove, let's get rid of Years and Item Type,
- 05:21 and maybe I want to analyze, we'll keep Sum of Total Profit but
- 05:26 maybe I want to analyze by Sales Channel.
- 05:29 Because we only have two sales channels so this is really the perfect type
- 05:34 of data for something like a pie chart, it's a direct comparison.
- 05:39 If we start having too many items, then the sectors start to get very small and
- 05:43 it's just really difficult for people to read and interpret.
- 05:47 Don't forget you also have a doughnut chart in here.
- 05:50 Some people like to display these as opposed to just standard pie charts.
- 05:55 And one little trick here is when you insert your chart,
- 05:57 you're going to get these little filters, these gray buttons on your chart.
- 06:01 Now I always like to right-click and hide all the field buttons on the chart,
- 06:06 just to get rid of those and make my chart look a little bit cleaner.
- 06:10 Now, some of these other chart types that we have in here are very specific to
- 06:14 certain types of data.
- 06:16 Now, things like area charts, we can use those.
- 06:19 In general, I prefer to use a bar chart or a column chart.
- 06:23 We can insert map charts.
- 06:25 Now, I'm going to show you how to do this in another lesson, because these
- 06:28 can look really effective, but they are a little bit trickier to insert.
- 06:32 And some of these other charts that we have down here, things like Treemap,
- 06:35 Sunburst, Histogram, we're going to take a look at a couple of these, but
- 06:39 one thing to note here is that if I select Histogram, can you see it says you
- 06:43 can't create this chart type with the data inside a pivot table.
- 06:46 And the same for a Sunburst chart, Treemap, so on and so forth.
- 06:51 Now, a lot of the time you'll find this, and we have this with the map chart as
- 06:55 well, that if your data is inside a pivot table, you can't use these charts.
- 06:59 So I'm going to show you a workaround for this in the next lesson.
- 07:03 But for the time being,
- 07:04 that's a very swift guide to how to select the right chart type.
- 07:08 If in doubt, go for a bar or column chart.
- 07:11 If your data set's pretty small, you could choose a pie or doughnut chart.
- 07:15 And if you have time-based data,
- 07:19 in general, a line chart works really well.
Lesson notes are only available for subscribers.