Creating PivotCharts

Quick reference

• 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 amazing is
• 00:13 the ability to actually develop a report and send it out for a user to use.
• 00:17 One of the key elements in that is a pivot chart.
• 00:20 So to create one,
• 00:21 we click a cell in our PivotTable, go to PivotTable tools options > PivotChart.
• 00:26 And then we have to pick the chart we think is best representative of the data
• 00:30 we want, I'm gonna go with the stacked column chart, and we'll say okay.
• 00:35 Now, it creates this nice looking little chart for us.
• 00:38 There's a ton of data summarized inside here.
• 00:40 It's also got a lot of different series, I don't think I really need all these.
• 00:44 I don't really need the percent of 2009, doesn't really make a lot of difference.
• 00:49 Or the percent of prior month, just trying to fit a whole bunch of stuff in there.
• 00:52 So that's starting to look a little bit better.
• 00:55 Now, I've got these little filter drop down arrows on the chart that I could use
• 00:58 to filter, but, you know what?
• 01:00 I could also use a slicer.
• 01:01 Check this out.
• 01:03 Go to PivotChart tools > Analyze > Insert Slicer.
• 01:07 And I'm gonna Grab Years and Class and
• 01:09 Category, and create all three of those guys.
• 01:13 And now what I'll do is I'll select all three of the slicers,
• 01:16 plus my chart actually.
• 01:18 Right-click, Size and Properties.
• 01:21 And we're going to make sure that our Properties don't move or size with cells,
• 01:25 we'll say okay.
• 01:28 And then I'm gonna grab my slicers and then very quickly go to slicer settings,
• 01:33 and I am going to leave these actually as they are.
• 01:37 Except for the show items deleted from data source,
• 01:39 we don't need those, say okay.
• 01:42 And good enough for right now.
• 01:43 So we'll move our years over here.
• 01:46 And put them at the top here, and class we can move somewhere as well.
• 01:52 And we'll put the category on the left hand side.
• 01:54 Now with these guys in place, I have to say that I
• 01:59 probably don't really need to have these little buttons on the chart anymore, do I?
• 02:05 So what if I get rid of them?
• 02:06 We'll click on those.
• 02:08 Go to pivot chart tools and
• 02:09 it's actually on the Analyze tab > Field buttons > Hide all.
• 02:14 That's good.
• 02:16 Now we can right-click on the format legend.
• 02:20 We can send that to the bottom, and like with any chart we can go to format,
• 02:25 and actually sorry, layout and go and add a chart title
• 02:31 above the chart called something like sales trend.
• 02:37 There we go.
• 02:39 Now, the other thing that I like to do with these things once I've got all this
• 02:42 stuff sort of lined up where I want it and
• 02:44 all those kind of wonderful things, is on the side,
• 02:48 I probably don't need to see the pivot table that's actually sitting behind this.
• 02:53 Because, really, at the end of the day I'm interested in this.
• 02:56 So I'm gonna go and hide this, and there we go.
• 03:00 I have now managed to pick up and have a whole bunch of information that I can
• 03:05 drill into and see how things work, which is really quite nice.
• 03:11 Okay, so we'll clear all those off.
• 03:13 And this is kind of cool because we can now start building dashboards out of
• 03:16 the information that we have.
• 03:18 So, I'm gonna grab all four of these guys here and slide a little bit more sideways,
• 03:23 cuz I'd like to go and pick up something from the Sales by Year tab as well.
• 03:27 PivotTable Tools > Options, we'll grab a PivotChart.
• 03:32 We've got multiple years here, the best thing for showing these kinda trends is
• 03:35 a line chart, so I grab this little guy right here on the left, and say okay.
• 03:40 And you can see I've got a ton of information on here.
• 03:42 A lot of junk, by the looks of things, too.
• 03:44 I already know I don't need these filter buttons so
• 03:47 Pivot Chart Tools > Analyze > Field Buttons > Hide All.
• 03:50 We'll get rid of those.
• 03:52 I'm not married to this particular format.
• 03:54 I want the category, I don't think I need the class, and
• 03:57 I think I might actually want to flip these around a little bit.
• 04:00 Let's go the other way and see what happens.
• 04:02 There we go.
• 04:03 This gives me a little bit more trending.
• 04:06 What I'm gonna do now is I'm gonna grab this.
• 04:08 Press Ctrl+x to cut it.
• 04:11 And move it over to my other worksheet.
• 04:15 And now what you'll see is that I can actually get multiple charts on one page.
• 04:22 The only thing that's left to do is to actually
• 04:27 link my slicers to both data sets.
• 04:30 And that way I'll be able to actually use them to drive through and
• 04:35 build an interactive dashboard that can just be refreshed out
• 04:39 of the data that I'm refreshing and I can send this to a user.
• 04:44 They can go and
• 04:45 do all the slicing and dicing and what-not to discover the trends that they want.
• 04:49 So, pretty cool stuff.
• 04:50 And when I need it updated, we just have to go to data, refresh all, and
• 04:54 it'll refresh all the data and allow the user to keep on going

Lesson notes are only available for subscribers.