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