About this lesson
Filtering multiple Pivot Tables at once with Slicers.
Exercise files
Download the ‘before’ and ‘after’ Excel workbooks from the video tutorial and try the lesson yourself.
Filtering Multiple PivotTables .xlsx207.9 KB Filtering Multiple PivotTables - Completed.xlsx
199.2 KB
Quick reference
Topic
Filtering multiple PivotTables at once with Slicers.
Where/when to use the technique
When you want to control multiple pivot tables and keep their filters in sync.
Background
- Until slicers were added, it required VBA macros to link report filters for multiple pivot tables
Instructions
Naming your PivotTables
- Select a cell in your PivotTable
- Go to:
- Excel 2013: PivotTable Tools > Analyze
- Excel 2013: PivotTable Tools > Options
- Change the name in the top left corner of the ribbon tab
Setting Filter Connections
- Select a slicer
- Go to Slicer Tools > Report Connections
- Select the name(s) of the PivotTable(s) you wish to connect it to
- Repeat for others slicers
Using Multiple Slicers
- Assuming you have the following setup:
- Sheet1: PivotTable-One
- Sheet2: PivotTable-Two
- Sheet1: Slicer-One (linked to both PivotTables)
- You can create a slicer on Sheet2 that is linked to both
- Be aware that it is very difficult to remove cross connected filters!
- 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.