This is a quick and easy way to change MULTIPLE PIVOT TABLE FILTERS on your Excel reports. For example, you may have multiple worksheets of Sales People, and they all have their own individual reports. Every month you need to update the Pivot Table Filters with the new monthly report.
Now, this is a very simple example with only four regions. Imagine if you had 15 or even 20 areas. Who wants to manually update all of these reports?. no one right?.
You don’t need to go into each of the Pivot Tables if you follow this process.!
Create your first Pivot Table.
So, first, you need to create your Pivot Table. If you need a tutorial on this feel free to view my YouTube Video below on how to do this.
Create Individual Reports For Each Region.
Next, create your multiple Pivot Tables, one for each Region or area. The easiest way to do this if they require their own Worksheet tab is to use the Show Report Filter Pages feature.
- Select Your Pivot Table
- Pivot Table Group
- Pivot Table
- Options
- Show Report Filter Pages
Select the Region Filter and hit OK. You will now generate all individual reports for your Sales People on an individual worksheet. How cool is that?. Now, onto the next step.
Create A Slicer To Select Monthly Reports.
Now, you need to insert your slicer which control your multiple Pivot Tables. To insert your Slicer follow the steps below:
- Click in one of your Pivot Tables
- Insert Tab | Filters Group
- Insert Slicer
- Select Date as the slicer
- Hit ok
Connect All Pivot Tables To The Slicer.
Now we have the Slicer to change the Regions, all we need to do is make sure that all of the Pivot Tables are connected to the slicer so we can change all of the filters at the same time.
- Right Click in your Slicer
- Select Report Connections
- Tick the Pivot Tables you want to control with the Slicer
- Hit Ok
Test The Slicer Solution.
So, now to test the solution, change the slicer to Mar. All of the reports in the workbook change to Mar. Cool right?.
So, if you want more Excel and VBA tips then sign up for my Monthly Newsletter. I share 3 Tips on the first Wednesday of the month. You will receive my free Ebook, 30 Excel Tips.