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
215.5 KB
Filtering Multiple PivotTables - Completed.xlsx
218.3 KB

Quick reference

Filtering Multiple Pivot Tables

Filtering multiple PivotTables at once with Slicers.

Where/when to use the technique

When you want to control multiple PivotTables and keep their filters in sync.

Background

  • Until slicers were added, it required VBA macros to link report filters for multiple PivotTables

Instructions

Naming your PivotTables

  • Select a cell in your PivotTable
  • Go to PivotTable Tools > Analyze
  • 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 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.

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