Locked lesson.
About this lesson
Creating PivotCharts out of a PivotTable.
Lesson versions
Multiple versions of this lesson are available, choose the appropriate version for you:
Exercise files
Download the ‘before’ and ‘after’ Excel workbooks from the video tutorial and try the lesson yourself.
Creating PivotCharts.xlsx10.9 MB Creating PivotCharts - Completed.xlsx
11.7 MB
Quick reference
Topic
Creating PivotCharts out of a PivotTable.
Where/when to use the technique
When you want a visual summary of your data.
Instructions
Creating a PivotChart
- Select a cell inside your PivotTable
- Excel 2010: PivotTable Tools > Options > PivotChart
- Excel 2013: PivotTable Tools > Analyze > PivotChart
- Choose a chart type to start with
- Modify your PivotTable to perfect the display
Filtering PivotCharts
- Can be done via the built in filter icons
- Can also be done via slicers hooked to the PivotTables
Removing the chart filter buttons
- Select the PivotChart > PivotChart Tools > Analyze > Field Buttons > Hide All
Caveats
- Every PivotChart is based on a PivotTable
- Modifying the PivotChart modifies the underlying PivotTable
- PivotTables don’t play well with combination charts (one chart type takes over on slicing)
Building Dashboards
- Create the PivotTable that drives your PivotChart
- Create the PivotChart
- Attach slicers to the PivotTable
- Optionally cut the chart/slicers to a new worksheet
- Hide the PivotTable (or the entire worksheet)
- 00:04 Now, tables of numbers are fantastic to an accountant, but not necessarily so
- 00:08 much to a regular audience.
- 00:09 One of the things we talked about with pivot tables that makes them so
- 00:12 amazing is the ability to actually develop a report and send it out to a user to use.
- 00:17 One of the key elements in that is a Pivotchart.
- 00:19 So to create one, we click inside our pivot table, Analyze and
- 00:24 PivotChart, and now we get to choose from a variety of charts and
- 00:29 Excel suggests the best one it thinks.
- 00:31 I'd like this one here.
- 00:32 So we're gonna say OK.
- 00:35 And you'll notice that we get a PivotChart right away and
- 00:38 it's also got a whole bunch of extra columns on it.
- 00:40 And the reason for that is because it's got a column for
- 00:43 every column you've declared in your pivot table.
- 00:46 Well, I don't really want all these.
- 00:48 So they add a little bit of clutter and noise.
- 00:49 Let's pull off percent of 2009.
- 00:53 Let's pull off percent of prior month.
- 00:55 And you'll notice that the PivotChart is reformatting, it looks much nicer but so
- 00:59 is the pivot table.
- 01:01 So when your building a PivotChart,
- 01:02 you have to have a pivot table that is dedicated to that chart.
- 01:05 So if you want to display your pivot table and you get it all knocked up nicely,
- 01:09 you might wanna duplicate it before you create your PivotChart or
- 01:11 you'll throw away your hard work.
- 01:13 Now, it's got filter buttons on here, which are kinda cool.
- 01:16 I could use those to filter by years.
- 01:18 But, if I go to PivotChart Tools > Analyze > Insert Slicer, and
- 01:23 let's insert a slicer for the Years, the Class, and the Category, and say OK.
- 01:29 You'll notice that I get a few different slicers.
- 01:32 I'm gonna make sure that I grab some settings right off the back.
- 01:35 Right-click > Size and Properties.
- 01:37 We will go to Properties.
- 01:39 Don't move or size with cells and we'll close those.
- 01:43 We'll also go with Slicer Settings and hide items with no data.
- 01:48 There we go.
- 01:49 I'll move these guys around a little bit now so we can see what ends up happening
- 01:53 with it, but these slicers actually work for our PivotChart.
- 02:00 So, if the slicers work for the PivotChart then the question we would have to ask is,
- 02:06 why do we need these filter fields?
- 02:09 So let's take them off.
- 02:10 We'll select the chart, we'll go to Analyze > Field Buttons > Hide All.
- 02:16 Now, with any chart, we can right-click on our chart labels and
- 02:20 we can go with Format Legend.
- 02:22 Sorry, the legend.
- 02:23 Move it to the bottom.
- 02:24 That looks much better.
- 02:25 And of course we can also go and go to the Design tab.
- 02:30 Add a chart element like maybe a chart title
- 02:35 above the chart and call this one Sales Trend.
- 02:40 One of the things that I often do with my pivot tables when I'm setting these things
- 02:45 up this way and my controls and
- 02:47 all these different pieces is I'll hide the PivotTable that's underneath it.
- 02:51 Because I really don't need to see it and I don't want the user messing with it.
- 02:55 Looks like I forgot to move my chart around.
- 02:57 So now I've got some filters that work with my PivotChart, which is kinda cool.
- 03:04 What about going over the Sales by Year tab?
- 03:07 Let's grab a PivotChart for this as well, we'll go to Analyze, PivotChart.
- 03:12 And this is gonna be multiple years so it makes sense for a line chart.
- 03:16 We'll say OK.
- 03:18 Well, I'm going to get rid of those Analyze buttons cuz I know that I can go
- 03:21 and use slicers for those.
- 03:22 Get rid of that noise and
- 03:23 I'm not really sure I'm in love with the way this is working out.
- 03:27 I can right-click on the charts and say Show Field List and
- 03:30 that will bring the field list just as if I were selecting the pivot table.
- 03:33 Now I can move things around.
- 03:35 So, what if I decided that maybe I didn't want Class on here at all and
- 03:39 I really wanna flip these two things around here.
- 03:42 So we'll just move Years down to where that is, well, that's kind of interesting
- 03:46 actually, and then we'll move Category back up, there we go.
- 03:49 There's a chart that I think I can probably work with, and now,
- 03:52 I'm gonna right-click and cut it.
- 03:54 I'm gonna move it over to my other worksheet, and I'm gonna paste it.
- 04:02 So I've now got a nice, tall chart over here.
- 04:04 Maybe a little bit too tall, so let's move all this stuff over here a little bit.
- 04:09 We'll go all the way over here and make this guy a little wider.
- 04:14 And what you'll see is that I can take each of my slicers, and
- 04:18 I mean, you already know this.
- 04:20 And link them to the variety of report connections that I have.
- 04:25 And now I can build a full dashboard out of multiple pivot tables that I can
- 04:30 go through and slice and everything will work together, nicely.
- 04:37 All I have to do if I want to update it is just go in to Data and Refresh All and
- 04:43 it'll reach out, refresh all the tables and the charts and I'm ready to go.
Lesson notes are only available for subscribers.