Creating PivotCharts

Quick reference

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