Filtering Multiple PivotTables

Quick reference

• 00:04 The final thing we want to look at when we're actually playing around with filters
• 00:09 and slicing is how do we actually link multiple PivotTables together?
• 00:13 Now, you can see that we have a timeline here.
• 00:15 And if I drill into January 1st,
• 00:17 it's going to slice my information right down to show January 1st.
• 00:21 Well, as it happens, I also have another report on another page.
• 00:25 And you can plainly see that it has not been sliced down to January 1st.
• 00:29 Wouldn't it be nice if we could actually make that timeline work on this field?
• 00:33 And as a matter of fact,
• 00:34 wouldn't it be nice if we could also make something like a slicer here?
• 00:37 Where we can still drill into just Burgers, also,
• 00:40 filter the PivotTable on the other side.
• 00:43 So that's what we're going to actually look at doing here.
• 00:45 Now I'm going to clear all of these guys off right now, and
• 00:47 we're going to bring all our data back.
• 00:49 And the secret to this is to be able to take one of our slicers or timelines.
• 00:54 And go up to the Slicer Tools or Timeline Tools Options and
• 00:57 set the Report Connections to multiple PivotTables.
• 01:00 But before we do that, I want to make a change.
• 01:02 Because one of the things that I haven't been doing as we've been going through
• 01:05 this course is really following a best practice recommendation of
• 01:08 renaming your PivotTables.
• 01:10 Notice, I have two PivotTables with the exact same name here.
• 01:13 One of them lives on Report-1, one of them lives on Report-2,
• 01:16 those are the worksheets.
• 01:17 But it's really hard to tell these guys apart, so
• 01:20 one of the things that we should do is actually provide our PivotTable a name.
• 01:24 Now what I'm going to do is I'm going to click inside my PivotTable here,
• 01:27 which brings up the PivotTable Tools Analyze tab.
• 01:30 Now, my ribbon's a little bit more compressed than what yours probably is.
• 01:33 But if you'll notice, way on the left-hand side here,
• 01:35 you probably see a box where it says PivotTable1.
• 01:38 And if you rename this, this is the actual name of the PivotTable.
• 01:41 So I'm going to make this one here called SalesByProduct.
• 01:47 And at that point, it should actually show that it's taken that name.
• 01:51 I'm going to go back over to Report-2, I'm going to show you another way to do this.
• 01:54 We can right-click on the PivotTable and go to PivotTable Options.
• 01:57 And you'll notice the name up here is PivotTable1 for this one.
• 02:00 This one, I'm going to call SalesByDate, and now we can say OK.
• 02:06 And you'll notice that it actually comes into the PivotTable range as well.
• 02:11 I'm going to go back to Report-1 now, I'm going to say, all right,
• 02:14 what do I want to link to the other side?
• 02:16 Does it make sense to link a Class slicer to Report-2?
• 02:19 I mean, it's only got these two items in it on columns, so it probably doesn't.
• 02:23 But what about Category and the Date Range?
• 02:25 Well, those ones, it definitely does, so I'm going to grab Category here now.
• 02:30 I'm going to go to Slicer Tools Options, Report Connections.
• 02:33 And I'm going to check that, hey, it's SalesByProduct,
• 02:36 I'm also going to flip it to SalesByDate, and we're going to say OK.
• 02:40 And now what you'll see is that if I drill into Burgers,
• 02:43 I've only got a couple of items showing here for my weeks.
• 02:46 And now you can see that it's actually drilled into just Food and
• 02:50 reduced our total significantly.
• 02:52 Let me go back over to Report-1, I'm going to clear that out,
• 02:56 let's grab our timeline.
• 02:58 We'll go to Timeline Tools Options, we'll go to Report Connections.
• 03:02 And we'll link this one also to SalesByDate and
• 03:06 say OK, I'll now drill this one down to January 1st.
• 03:09 And we can see that it's definitely down to just January 1st,
• 03:12 where we have food items, so that's pretty cool.
• 03:16 The challenge is do I really want to keep coming back to this worksheet to
• 03:20 do my filtering?
• 03:20 I say, no, that's silly, so I'm going to grab Class, Category,
• 03:24 and our Select a Date Range timeline here.
• 03:26 Ctrl+C, I'm going to bring them back over to Report-2, Ctrl+V,
• 03:30 it's going to paste them all on here.
• 03:32 And now I'm just going to move this up into the top,
• 03:34 get them right where I want them.
• 03:36 And now what you'll see is that I can go in, I can drill into January 1st,
• 03:40 I can drill into just Burgers and get down to my $73.
• 03:44 And if I come back over to Report-1,
• 03:46 you'll see that everything is synced up nicely.
• 03:48 If I change this to Canned Beer, hm, there's nothing on here.
• 03:52 Let's go to the 2nd, let's try and run this out for a little bit more, actually,
• 03:56 and see what we can actually come up with.
• 03:58 I'm sure we must have some canned beer in here somewhere.
• 04:00 There we are, and now if we go back to Report-2, you can see that, indeed,
• 04:05 it looks like canned beer sales started on the 4th.
• 04:08 Now, the key thing is that sometimes you want to know from your PivotTable what's
• 04:11 linked to it.
• 04:12 From PivotTable Tools Analyze, we can actually go in, and
• 04:15 we can see our Filter Connections.
• 04:17 And this tells us the timelines that are actually, and
• 04:20 the slicers, that are actually selected to these things and where they live as well.
• 04:23 So that's how you can look at this from either direction and
• 04:25 keep a PivotTable in sync nicely.
• 04:28 With a consistent user interface experience between multiple worksheets.

Lesson notes are only available for subscribers.