Locked lesson.
About this lesson
Filtering multiple PivotTables at once with Slicers.
Lesson versions
Multiple versions of this lesson are available, choose the appropriate version for you:
2016, 2019/365.
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.