Choose the Right Chart Type

Quick reference

• 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.