Locked lesson.
About this lesson
The ins, outs and shortcomings of Pivot Charts
Exercise files
Download this lesson’s related exercise files.
Using Pivot Charts.xlsx55.3 KB Using Pivot Charts - Completed.xlsx
67.6 KB
Quick reference
Using Pivot Charts
Creating charts based on Pivot Tables.
When to use
When you wish to use a chart bound to Pivot Table based data.
Instructions
Creating a PivotChart
- Select a cell inside your PivotTable --> 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)
Hints & tips
- Unfortunately, not all charts have a Pivot Chart version. If you want to use a non-supported type of chart, you’ll need to land data to a table and base the chart off that instead
- Before linking a slicer to a Pivot Chart, remember to name the underlying Pivot Table
- 00:04 The last thing that I'd like to add to this dashboard is I would like to add
- 00:09 a chart that shows the monthly sales.
- 00:12 Now, the thing is is that we've got data that looks like this.
- 00:16 It's showing us a monthly setup already and it's got our sales, but
- 00:19 it would be nice to visualize that.
- 00:20 So we're gonna use a pivot chart to do this because
- 00:24 that's how we summarize our data nicely.
- 00:27 Now, here's what we don't do.
- 00:28 We don't grab a pivot table like this and say, all right,
- 00:32 let's go to Pivot Table Tools > Analyze, and let's go and insert a pivot chart.
- 00:37 It's unfortunate cuz it looks like we got a pretty cool chart over here,
- 00:41 where we'll say OK.
- 00:41 But the thing is is that you'll notice that when we actually bring up our pivot
- 00:45 chart, we've got our PivotCharts Fields here now,
- 00:47 when I start saying, you know what, I don't want profit and
- 00:50 I don't want vendor on here, look what just happened to my pivot table.
- 00:55 It destroyed it, so this is kind of a sad thing, we don't want that.
- 00:58 So we're gonna go undo these things and
- 01:01 we're gonna get rid of this particular pivot chart.
- 01:03 Every pivot chart needs its own brand new pivot table to drive it, okay?
- 01:07 Never actually take one that you're so happy with the formatting and
- 01:10 then start adding a pivot chart to it.
- 01:12 We're gonna go to the source data table again,
- 01:14 we're gonna start completely from scratch on this one, and we're gonna say Insert,
- 01:19 and you'll see that on the charts area, we have a big PivotChart button.
- 01:24 We're gonna put this on an existing worksheet, I'm gonna go drop it over onto
- 01:28 my summaries area, or I'll just put it down here around, say row 15, and say OK.
- 01:32 And this will create me both the pivot chart and the pivot table field for it.
- 01:38 So what do I think I need on this?
- 01:40 Well, I think I need Month End on my axis, and sum of sales on my values.
- 01:46 And there we go, I start getting a pretty decent chart.
- 01:50 Now, I know this is gonna become an issue a little bit later, so
- 01:53 I'm very quickly gonna just pop over to my pivot table,
- 01:56 go to Pivot Table Tools > Analyze, and I'm gonna give this a nicer name.
- 01:59 I'm gonna call this one something like PVT sales chart.
- 02:02 Even though this is the pivot table, it's driving this particular chart.
- 02:09 Now I have the ability to go back and start messing around with my chart.
- 02:13 Does it need a legend that says Total?
- 02:17 Probably not.
- 02:18 I'll delete it.
- 02:21 What about my title?
- 02:22 Well, I could dynamically link it to a cell, but for right now,
- 02:25 I think we're just gonna put this one down and say this is just a sales trend.
- 02:29 That's good enough for me right now.
- 02:31 And I'll go and do the other things that I normally do,
- 02:33 like add data labels to it and maybe get rid of my grid lines, and
- 02:37 maybe get rid of my axis there, there we go.
- 02:39 And that looks pretty good for now, except for these ugly buttons.
- 02:46 These things are actually filters, but they're so nasty looking?
- 02:52 So why would we use that when we've got really attractive looking slicers?
- 02:57 So what we're gonna do is we're gonna go up to the Pivot Table or
- 03:00 our Pivot Chart Tools, which show when we actually have our pivot charts.
- 03:04 We're gonna go over this Field Buttons area and
- 03:06 we are gonna say Hide All, and we'll get rid of those nasty looking things.
- 03:11 And now with this in place, we can actually go back and we can start
- 03:15 fooling around with some different things to make it look a little bit better.
- 03:18 Maybe again, we wanna change our number format.
- 03:20 So go to our Value Field Settings, and we'll go over to our Number Format.
- 03:25 Number, 0 decimals.
- 03:28 And we'll go with 4361, and say OK and OK.
- 03:33 And that modifies the format for our data labels, which look pretty good.
- 03:37 We could go and do the normal stuff that we do.
- 03:39 We'll get rid of the pivot chart field dialog, format our data setup here, maybe
- 03:44 make our gap width a little smaller so our bars are looking a little bit better.
- 03:49 That's not so bad.
- 03:50 Maybe we can even make some date format changes.
- 03:53 We'll go to Field Settings, Number Format, and
- 03:56 let's see here, under Date, do we have some other options?
- 04:04 How about this one here?
- 04:05 We can go with MAR and a year, so
- 04:08 that will look a little bit shorter and maybe looks a little bit better as well.
- 04:13 With the chart in place, I'm pretty happy with that, so now I can go and say,
- 04:17 you know what, I could do two things.
- 04:19 I could cut it or I could copy it.
- 04:20 So I'm just gonna copy this guy, bring it over, drop it here, CTRL+V, and
- 04:26 once again, we'll go and resize it to be the size that we actually want.
- 04:32 Perfect, there we go.
- 04:34 That looks lovely.
- 04:36 But there's a small problem.
- 04:38 We'll just zoom this down so we can actually see the problem happening here.
- 04:42 When I click on Accessories, the chart doesn't change.
- 04:44 When I click on Sleeping Bags, it doesn't change.
- 04:46 And I want it to update with everything else that's going on here.
- 04:50 So select the slicer, go to Options, Report Connections,
- 04:55 and we'll link this to the PVT Sales Chart and say OK.
- 05:00 And what you'll see now is that as we go through and
- 05:04 we start making our selections, the sales trend chart,
- 05:08 including all of the other tables, all update nicely, and we are good to
- 05:14 go with a beautiful dashboard that is working exactly as we'd intend.
Lesson notes are only available for subscribers.