🥳 GOSKILLS TURNS 10: Get 10 days of free access with code 10YEARS

GoSkills
Help Sign up Share
Back to course

Filtering Multiple PivotTables

Compact player layout Large player layout

Locked lesson.

Upgrade

  • Lesson resourcesResources
  • Quick referenceReference
  • Transcript
  • Notes

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 .xlsx
207.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!
Login to download
  • 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.

Timelines
03m:48s
Show Details
05m:04s
Share this lesson and earn rewards

Facebook Twitter LinkedIn WhatsApp Email

Gift this course
Give feedback

How is your GoSkills experience?

I need help

Your feedback has been sent

Thank you

Back to the top

© 2023 GoSkills Ltd. Skills for career advancement