All language subtitles for 1. Creating a Pivot Chart

In this section of the course, we're going to shift our focus from pivot tables to pivot charts because

these two really do go hand in hand.

A lot of the time, once you've created a pivot table to analyze your data, you're going to want to

present that data visually using a pivot charts.

As we know, a picture speaks a thousand words.

And sometimes it's a lot easier to make sense of data when it's displayed in a chart.

Now I will say, when you're thinking about putting together a chart, you really need to consider the

data that you want to display and only display the data that is relevant.

And not all charts are created equally.

For example, something like a pie chart or a donut chart tends to work better for smaller datasets.

Whereas if you have time based data, which we do here, we're looking at values over two years, 2018

and 2019.

These in general are best displayed with something like a line charts.

Now, currently, if I want to put the data that I have in this pivot table into a chart, my chart

is going to be quite chaotic looking.

So let's take a look at that, first of all, and then we'll explore better ways of working with pivot

charts.

So once you've created your pivot table, simply click in it.

Go up to the pivot table, analyze ribbon and over in the tools group, we have a pivot chart button.

And this is going to open up the insert chart dialog box.

And there are many, many different chart types in Excel.

And these chart types are divided down into different categories, and each category has many different

iterations of that style of chart.

Now, if you click on one of these categories and you get a message like this, it means that based

on your data, Excel can't produce this type of chart.

Now, as I said, this isn't ideal information because it is time based data.

If I go to line chart and just selects to insert one of those.

Take a look at what we get now.

I'm going to drag this out.

It's doing a pretty good job of displaying it, but take a look at what we've got down here.

We kind of have it split down premium luxury standard, premium luxury standard, so on and so forth.

And because I have quite a bit of data, it means that this chart ends up being quite wide in order

to display this effectively.

I would really need to stretch this out quite far.

I couldn't have it sort of like that on the page because then no one can see the information clearly.

Now I could very well just make it wider and display it like that.

That isn't too much of a problem in this case, but I might want to take a look at the data that I'm

choosing to display and refine my data before putting it into a chart.

So let's delete out the chart, just pressing delete on my keyboard.

Maybe I look at this data and I think to myself, You know what, actually, I'm going to rearrange

this a little bit.

So maybe I want to present a chart to some key stakeholders, and they're only really interested in

seeing the gross sales per country by year.

They're not really interested in the breakdown of the products.

So I'm going to make some changes to my pivot table.

I'm going to remove the product field and I'm also going to remove the range field as well.

So now I'm simply left with a list of countries and the years, and this is a much more manageable dataset

to put into a chart.

So now if I go back to pivot table, analyze and click on pivot charts, I could choose something like

a column chart.

Or maybe I want to go back to a line chart and display it in that way.

Now, I think in this case, I'm going to choose a column chart and click on, OK, now what about if

I have my data organized in a different way again?

So let's delete out this chart.

What I'm going to do here is we're going to remove year.

I'm going to drag country into columns, and this time I'm going to drag Monk's name down into rows.

Now it might be that I'm only interested in displaying the top five months, so the top five months

with the most gross sales.

So what we can do here is we can right click and we can apply a filter.

If we go down to filter, I have a top 10 filter in here.

So if I'm only interested in the top five by gross sales, I can take this down to five.

I can see items by total gross sales.

Click on OK, and now I can put this data into a pivot chart.

Let's go to analyze pivot charts, and this time I'm going to go for line charts, clicking OK.

And I have my data represented in a different way.

So the point I'm really trying to make here when you're thinking about putting your data into a pivot

chart, is not only think about what is important to display in the chart, but also think about what

is the most appropriate chart type.