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 Pivot Charts.xlsx10.9 MB Creating Pivot Charts - Completed.xlsx
10.9 MB
Quick reference
Creating PivotCharts
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
- Go to 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 As an accountant,
- 00:05 I'm used to putting out a report that has columns and columns of numbers.
- 00:09 But sometimes,
- 00:10 I need to put something out that my audience is actually going to read.
- 00:13 And that takes a little bit of visualization work.
- 00:16 This pivot table contains a ton of information right now and
- 00:18 it's all set up exactly the way that I'd present it.
- 00:20 But my audience wants a chart.
- 00:22 So, I'm going to go to Pivot Table tools Analyze and
- 00:25 I'm going to insert a pivot chart.
- 00:28 Now because this is going to be a sales trend for multiple years,
- 00:30 I'm going to start with this one here, a clustered column.
- 00:33 And I'm going to say OK, and this shows the first big problem with
- 00:36 trying to convert a pivot table into a chart is that there's too much data.
- 00:41 So what I'm going to do is I'm actually going to remove a couple of things.
- 00:43 This percentage of 2009, we'll get rid of it and
- 00:46 the percentage of prior month, we'll pull that off so
- 00:49 that our chart is now down to just a couple of series with food and alcohol.
- 00:53 And that's awesome, except that,
- 00:55 look what it did to my original pivot table it's actually changed it.
- 00:59 The pivot chart is linked to the pivot table,
- 01:01 that's the key thing that you want to remember.
- 01:03 Every time that you change one, it changes the other.
- 01:06 And for that reason, it's a good idea to take a copy of your
- 01:09 pivot table before you start turning it into a chart.
- 01:12 It also means that you probably want to right click and hide these guys.
- 01:16 So that people can't start dragging fields in your pivot table and
- 01:20 messing with the chart that you get this perfect.
- 01:22 Speaking of perfect charts, this isn't one yet, I'm going to make some changes.
- 01:26 We're going to right-click on my legend here, Format Legend and
- 01:30 send the legend to the bottom.
- 01:31 So that that's out of the way.
- 01:33 I'm also going to turn off these horrible 1995 looking filter buttons.
- 01:37 Because you know what, if I do that,
- 01:39 pivot chart field buttons hide to get rid of those things.
- 01:43 We can obviously, go and say insert slices and get into much more
- 01:47 attractive looking buttons that people actually want to click.
- 01:51 So I'm going to do that we'll move years up here and class up here and
- 01:55 category over here.
- 01:56 I'm going to drag them around like this, will grab all three of them,
- 02:01 line them to the top.
- 02:02 I'm going to say, I don't need this one anymore and
- 02:04 we'll just shrink these guys up.
- 02:06 So, there's about two rows of buttons on those, just slightly less than that.
- 02:11 Good enough, will grab our years.
- 02:13 Move it to two columns and the class, why not as well?
- 02:17 And there we go, the final thing we need to do is right click, say, size and
- 02:21 properties and say don't move or size with cells.
- 02:24 The last piece that's missing on my chart, every chart inside Excel 2019 and
- 02:29 higher gets a good title, except for a pivot chart.
- 02:31 So, you have to go to Design> Add chart element> Chart title and say Above chart.
- 02:38 And at this point, we can put a nice chart title on it that says Sales Trend,
- 02:43 and away we go.
- 02:44 After that, it's just a little bit of work about making it taller,
- 02:47 seeing how it works.
- 02:48 We can no slice into alcohol.
- 02:50 We can slice into things like canned beer, cooler cider, draft beer, for example.
- 02:54 We can compare multiple years and
- 02:56 even remove the filters on some of these things if we want to get rid of them.
- 03:00 So, just like that we've got a nice little sales trend chart that's all hooked
- 03:04 up to an actual pivot table behind the scenes.
- 03:06 The cool thing about pivot tables though,
- 03:08 of course, is that we have another one over here.
- 03:11 And what if I wanted to grab another pivot chart?
- 03:13 Well, let's do it, go grab a pivot chart.
- 03:17 This one's going to be sales by year, so,
- 03:19 it probably makes sense to do it as a line chart.
- 03:21 And we'll say OK, now it gives us this crazy looking thing.
- 03:25 And once again, we're going to make some changes.
- 03:27 I don't think I need class on here.
- 03:29 And to be honest with you, I don't know I want my categories here.
- 03:32 I'd rather have my years on the bottom, so, I'm going to switch category and
- 03:35 years around.
- 03:36 There we are, that looks a little bit better and
- 03:39 I'll get rid of those nasty horrible filter buttons.
- 03:42 Just like this, I've now got a sales trend, but it's a lot of information.
- 03:45 It's every single one of my categories.
- 03:47 So, what I'll do is I'm going to say CTRL+X, we'll cut that, look,
- 03:51 it just changed the pivot table.
- 03:53 We'll come back over to Sales Trend> Quick> Ctrl+V and now,
- 03:58 I've got a nice little chart.
- 04:00 And if I move some things around, I can actually get all of this into one page.
- 04:05 So that I now have a really nice little dashboard that I can actually slice
- 04:10 into multiple categories and compare how things are going.
- 04:13 Let's take a look at draft beer, and burgers and breakfast, and there we go.
- 04:18 No, it's not linked to this other chart, well that's easy to do, isn't it?
- 04:23 Slicer > Report connections > Sales By Year and say OK.
- 04:29 Now, there's obviously a lot to learn about charting.
- 04:32 We have an entire dashboarding course in GoSkills as well.
- 04:35 But this gives you the real, real quick look at how to be able to actually build
- 04:39 a dashboard off of multiple pivot tables and sync them together using slicers and
- 04:43 timelines and different things like that in order to get everything done.
- 04:47 The key thing to remember, as you start modifying your chart,
- 04:50 it does modify the underlying pivot table.
- 04:52 So be careful around that.
Lesson notes are only available for subscribers.