Filtering Multiple Pivot Tables

Quick reference

• 00:04 One of the really cool benefits that slicers offer us, as report designers,
• 00:08 isn't just the ability to hand something to our users and have them click it.
• 00:12 But it also gives us the ability to keep multiple pivot tables in sync
• 00:17 really easily.
• 00:18 Before slicers came along, the only way to do that,
• 00:21 if you were changing a report filter on one pivot table and
• 00:23 wanted to keep another pivot table keeping the same filters.
• 00:27 You had to use Visual Basic for
• 00:28 application macros in order to keep them in sync.
• 00:31 But slicers don't have that problem.
• 00:33 If you check I've got a nice little pivot table that's set up here and
• 00:36 I can slice into canned beer.
• 00:38 No problem.
• 00:39 I also have another pivot table on another page.
• 00:42 Completely different format but
• 00:44 I'd really like to be able to slice this the same way.
• 00:47 And drive it from my original sheet.
• 00:50 And I can certainly do that.
• 00:51 And the way that I'm gonna do it is first I'm gonna do a little bit of clean
• 00:56 up work to make sure that everything is gonna make logical sense.
• 00:59 In my pivot table, I'm gonna go to pivot table tools and analyze or in Excel 2010,
• 01:04 pivot table tools options.
• 01:07 On the very top left hand corner you'll notice that
• 01:09 we have the ability to provide a pivot table a name.
• 01:12 So I'm gonna call this one here something like SalesByProduct.
• 01:21 Something descriptive.
• 01:25 And then I'm gonna go over to Report2, and I'm gonna look at this table.
• 01:29 And you'll notice it also is helpfully called PivotTable1.
• 01:31 I'm gonna call this one SalesByDate.
• 01:36 So I'm giving both of these pivot tables names.
• 01:38 And this important and
• 01:39 its actually a best practice that I haven't really covered off until now.
• 01:43 This is why.
• 01:44 So, now we're gonna go back to Report1 and
• 01:47 we're gonna select the slicer that we wanna link to our SalesByDate.
• 01:53 And on the slicer tools options tab,
• 01:55 you'll notice that we have this big button called report connections.
• 01:58 When I click on that, it says.
• 02:00 Hey, this is linked to SalesByProduct, which is on worksheet Report1.
• 02:05 I would also like to link it to SalesByDate.
• 02:08 Okay, and now when I go over to SalesByDate.
• 02:12 You'll notice it's been shortened quite a bit.
• 02:15 And if I go back and remove these filters.
• 02:19 And go back again.
• 02:21 It looks like it's all back there.
• 02:23 What about week.
• 02:24 If I slice into week three.
• 02:27 Mm, no. No changes there.
• 02:29 And that's because I haven't linked this one.
• 02:31 So if I go and click on Week > Options > Report Connections, and
• 02:34 link him to SalesByDate.
• 02:37 And now I go over there, looks like it's working nicely.
• 02:40 Now that's great, but do I really want to have to bounce back to Report1 every
• 02:45 time to kind of filter something?
• 02:47 That seems like an awful lot of work.
• 02:48 Wouldn't it be nice if I could have these exact same filters,
• 02:53 including this one which I'm now going to link across as well.
• 02:58 Wouldn't it be nice if I could have these exact same filters over on this sheet.
• 03:00 Well why not? Let's click in the pivot table.
• 03:03 Pivot table tools analyze or options in Excel 2010.
• 03:06 Insert slicer.
• 03:07 We'll go and we'll grab what I need.
• 03:09 I needed the class, the category and the week.
• 03:12 And say okay.
• 03:13 You'll notice they don't show up in the order I selected them, but
• 03:17 that's unfortunate but okay.
• 03:19 So we'll move this one to Class and to Category and we'll put Week over here.
• 03:23 And I'll make this one a little bit wider.
• 03:26 And we'll select one, two, three of them.
• 03:29 We'll align them to the top and we'll shorten it up a little bit.
• 03:33 We can do all the formatting later.
• 03:35 That's not really super important.
• 03:37 Although I do want to see some more columns in this, just so
• 03:40 that I can prove it's working.
• 03:42 Now, what I'm going to do with this guy is I'm going to grab category,
• 03:46 report connections and you'll notice it's already linked.
• 03:49 And that's because I created it against this pivot table
• 03:52 that was already linked to the previous worksheet.
• 03:55 You'll also notice when I'm looking at week here,
• 03:58 it's already filtered to Week 3.
• 04:00 And if I change this to Week 2.
• 04:02 It's changed to Week 2.
• 04:03 When I jump back over, it's already Week 2 on this side.
• 04:06 Let's drill into Bottled Beer, and go back and prove that indeed it's working.
• 04:11 The one thing that I will point out on this is it's very difficult to unlink
• 04:15 crosslinked sheets.
• 04:18 In order to get rid of these things, if I were to uncheck this now, you'll see that
• 04:21 we get a nice little information here that says, hey if you disconnect it from this
• 04:26 PivotTable, I'm disconnecting all of your slicers from the PivotTable.
• 04:28 That's really not all that good.
• 04:30 The only way to get past this really is to go back and delete these guys off and then
• 04:36 unlink it from the other set of slicers in order to clear the whole cache out.
• 04:40 So that we can reset it.
• 04:41 It's a bit of a pain but, to be honest with you,
• 04:43 the number of times that I've had to unlink slicers,
• 04:46 pales in comparison to how many times I'm actually linking these together.
• 04:50 One other thing that I want to point out with this,
• 04:52 is that you can also bury these guys on different sheets.
• 04:55 If you don't wanna have the filter on the main page.
• 04:57 I can cut this class, go put them on a completely blank worksheet, and paste.
• 05:03 Now I can drill into alcohol here.
• 05:05 Jump over to the other guys, and it's working even though it's not showing here.
• 05:08 So we can hide slicers away if we need to as well.
• 05:11 Some pretty cool things to control multiple things and keep them in sync.

Lesson notes are only available for subscribers.