Using Pivot Charts

Quick reference

• 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.