Hi everyone, Kevin here. Today we're going to look at how you can make pivot charts in Excel. Pivot
charts allow you to look at your data visually so you can see patterns and also get insights from
your data. The great thing about pivot charts is you can explore your data simply by dragging and
dropping your mouse. Let's check this out. Here I am in Microsoft Excel, and if you would like to
follow along today, I've included a link to this workbook down below in the description. Here I
have sales data for the Kevin Cookie Company. Now I could look through it and try to make sense of
it, but I think it would be a lot easier if I turn this into a visual, and I think a pivot chart will
be the perfect tool for that. Here I have all of my data in a tabular format. It's all organized
by columns, and to use pivot charts, you'll want to make sure that your data is organized just like
this. To insert a pivot chart, first off, make sure that your selection is within your data.
Then up on the top tabs, let's click on insert, and right here in the center, there's an option
for charts. Over on the right-hand side of charts, you'll see the option for pivot chart. Let's click
on this, and this opens up a dialog. First off, it wants you to select the data that you would
like to include in the pivot chart, and here it's automatically detected all of the data here. That
looks great. Down below, you can choose to place your pivot chart on a new worksheet or an existing
worksheet. I'll place it on a new worksheet, and then let's click on okay. This drops us into a new
worksheet, and here we have our pivot chart, but to be honest, it really doesn't look like a chart
yet, and that's because we still have a little bit of work to do. Over on the right-hand side,
we have a pane that opened up titled pivot chart fields, and right down here,
we see all the different field names. Now, you might recognize some of them. These are all the
headers from our source data sheet. Here, we have the customer name the cookie type. If I
jump back into the source data, here we see the customer name, the cookie type, here are all of
our headers. Let's now jump back into the pivot chart. I now want to start constructing this pivot
chart. Over on the right-hand side, again, I have all of my different field names, and under that,
I have these different areas, and we're going to use these areas to pull together the chart.
I would like to see revenue by customer, so I'd like to organize or categorize by the customer.
If I look down below, there's one area titled axis or categories. Well, I want revenue by customer,
so let's take the customer name, and I'll drag it down into this area. Over on the left-hand side,
you'll see that the pivot table updated to show me all of the different unique customer names as just
separate rows. Now I would also like to see the revenue, so here I have a field titled revenue,
and I can drag and drop that down into values. Typically, you'll put something into the values
category when you want to calculate something. Well, I want to see the sum of revenue by
customer, so that sounds like the right place, and I'll release. And look at that! Our chart
now shows us all of our different customers and the associated revenue. Now currently, it's not in
dollar formatting, so let's highlight all this and let's shift it to a currency. I'll click on that;
let's get rid of the decimals, and that was pretty easy. I just had to drag and drop to start pulling
this together. Let's say that along with the revenue, I also want to see the cost. Within
my fields list, here I have an item for cost. I can take that, and I can drag and drop it under
the revenue, and now I have the revenue alongside the cost. It looks like as the revenue goes up,
so does the cost, and let's say I don't want to see the cost in there anymore. I can simply click
on this field, and I can drag it out release, and that removes the cost. Look at how easy it is to
start modifying what this chart looks like. Now, one thing that you could do with pivot
charts that you can't do with traditional charts. Let's say that along with seeing the customer,
I also want to break it down by the cookie type. Right up above, I have a field for cookie type.
I'll drag this item and I'll place it under the customer name under rows and look at that. Here
it shows me all the customers as the top level, and then cookie type is under that, and then I
see it broken down by cookie type, but let's say I actually want to see it by cookie type as the
top level and then customer name as that second level. I could take cookie type, drag it up on top
of customer name, and now, here if I expand the chart, now it's organized by the cookie type and
then the customer name, and maybe I just want to see it by customer so I can drag cookie type out
and look at that it just automatically updates. That's how easy it is to work with pivot charts.
You just drag and drop your data in and out, and then you can visualize what it looks like. When we
inserted this pivot chart by default it inserted a column chart, but you might be wondering what
if there's another chart type that would better visualize this data. Luckily, we could very easily
swap out the chart type. For example, here, we have all the customers and the revenue. For this,
maybe a pie chart would work well to show the part of the whole that each customer makes up.
I can right-click on the chart, and here's the option to change the chart type. Alternatively,
with this chart selected, I can also go to the top tabs, and over here, I'll click on design,
and here, too I can also change the chart type. Let's click on this. This opens up the chart type
gallery, and right at the top, you'll see some of the most popular types of charts. You have column,
line, pie, and bar, and then you have a number of different options. One thing to call out, with
pivot charts, you're unable to create tree maps or any of these types down at the bottom. For this,
I want to create a pie chart. I'll select this option. I'll go with the default variant, and
here, if I hover over, I can see a sample of what this will look like. I think that looks great, so
down below I'll click on okay. And look at that! It's now changed it to a pie chart, and it looks
a little bit like a cookie, probably with a lot of food coloring added to it. With pivot charts,
you have all the same design and formatting tools that you have with just a traditional chart in
Excel. With this chart selected, up on top, I'll click into the design tab, and over on the
left-hand side, you can add or remove different chart elements. You can also apply different
quick layouts. For example, maybe I want to see the percentage on top of the slice. Here, I'll
select this option, and over here, I could adjust the colors. I could also change the styles. Let
me go with this one. That way, the names and also the percentages really stand out. Right up on top,
I could also click into format and here, I could adjust all sorts of different formatting options
related to this chart. Down here with this chart selected, I could also click on this plus icon,
and here, too I could also add and remove different chart elements, and I could also apply
different styles and coloring right from here. At The Cookie Company, I'm in charge of Wholesome
Foods and Saveway, so maybe I just want to filter this view just to those two customers. Over on the
right-hand side in the fields, let's scroll to the top of the list where we see customer name.
Then click on this down arrow, and over here I can filter which customers we see in this chart view.
I'll deselect select all, and down at the bottom, let's check Saveway and Wholesome Foods and then
click on ok and look at that it's now filtered this view just to those two customers. To turn off
the filter, over on the right-hand side, let's go back to the same place, click on this down arrow,
and over here, I could either clear the filter or we could select all and then click on ok,
and now I see all of the customers again. Next, I want to analyze customer revenue by year, and a
pie chart won't work at all for that, so for now, let's delete this pie chart, and just like that,
our pivot chart has disappeared. But don't worry. It's really easy to get a pivot chart back. Let's
click into the pivot table and then go to the insert tab, and here in the center, I have all
of my different charts. Now I could again also insert a pivot chart, or alternatively, I can
just choose one of the charts right up here. Let's go with a 2D column chart. I’ll click on this and
then select this option, and this is now inserted a column chart, but because I had my selection
within the pivot table, it automatically turns this into a pivot chart. Over on the right-hand
side, here again, I see all the different pivot chart fields, and I could just start constructing
my pivot chart directly from here. Like I said, I want to see revenue by year by customer, and right
up here, I have a field for the date. Let's click on date and then drag it down, and we'll place
it into the legend or series category. Here I'll release my mouse, and over on the right-hand side
of the chart, you'll now see that we have a legend with the date. So here we can see the growth from
2022 to 2023, and look at that! Our marketing and sales teams have been doing a fantastic job. Look
at how much revenue has grown. Now maybe you'd rather categorize or group by the year instead of
the customer, and you'd like to see the customer as the legend. To do that, I basically have to
swap the customer name and place that into series, and I'd have to take all the different dates from
the legend and move that into the categories area. Sounds like a lot of work. Instead, up on top,
let's click into the design tab, and right here, we have the option to switch the row and
the column. When I click on that, look at that. It automatically does all that work for us. So here,
I see the customer name now as the legend, and I see the year right down here as the axis and the
category, and I can simply click on this to switch back and forth between these two different views.
So, you can experiment with that to see which view better serves your data. To wrap things up,
I would like to see which customer sold the most fortune cookies in 2023. When I look down here,
I can see that Amazin sold the most overall cookies in 2023, but what about fortune cookies?
If I scroll up all of my different fields, there's one field for cookie type. Let's see if we could
filter this. Over here, I'll click on the down arrow and here I see all of my different cookie
types. Let's deselect this, and let's just go with fortune cookies and then click on okay. But
you'll notice it hasn't at all changed this view. The filter is not working, and the reason why is
this field is not currently in one of these areas, but I don't want to add it as a category. I also
don't want it to be a legend. Well, right here, we have another area for filters. Let's take the
cookie type and drag it down into filters and then release, and here we see that actually, Saveway
had the most revenue for fortune cookies in 2023, even more so than Amazin. To adjust this filter,
in the top left-hand corner on the pivot table, I can click on this filter icon, and here I can
see that it's filtered to fortune cookie. I can also go over to the right-hand side, and just like
we did before, I can click on this filter icon, and here, too, I can also select fortune cookie,
then click on okay. To remove the filter, I can take cookie type from the filters area, and I can
drag that out, release, and that has now removed the filter. All right, well, that was a quick look
at pivot charts in Excel. They are such a powerful tool. To learn more about charting and also pivot
tables in Excel, I've included two videos down below in the description that go more in detail.
That way, you'll be analyzing and getting insights from your data in no time. To watch more videos
like this one, please consider subscribing, and I hope to see you in the next video.