Home > Pivot > Macros > Filters Change Multiple Filters Automatically Change the report filter in one Excel pivot table, and macros change the same filter in other pivot tables automatically. Also see: Pivot Table Report Filters

Macros: Change Multiple Pivot Filters

In Excel, you can use Slicers to filter pivot tables. A Slicer can be connected to one pivot table, or multiple pivot tables.

However, in some cases, a Slicer can't do what you need, because of the following limitations:

• Slicers only work for pivot tables based on the same source data
• Slicers were not available in Excel 2007 and earlier

In those situations, you can use one of the Excel pivot filter macros shown in the sections below.

1) Filter All Pivot Tables With One Slicer

This example works for pivot tables that use different source data, but have at least one field name in common. It uses a Slicer, and requires Excel 2010 or a later version.

In this Excel sample file, there are 2 data sheets -- Data1 and Data2.

• Each sheet has a small Excel table, with sample data
• There is only one field in common for the two tables - Market

Multiple Pivot Tables

On another sheet, named Pivots, there are 3 pivot tables, from the two different data sources.

• Two pivot tables are based on the Data1 table
• One pivot table is based on the Data2 table.

Click a Market name in the Slicer at the top of the sheet, and all 3 pivot tables are filtered automatically.

How It Works

The Pivots sheet slicer is connected to a pivot table on another worksheet, named Lists. That pivot is based on the Market table, which is also on the Lists sheet.

• When you select a market in the Slicer, that updates the Lists sheet pivot table
• The PivotTableUpdate event code runs automatically, and changes the filter on all pivot tables on the Pivots sheet

Get the Excel File

To see the code, and copy it to your own workbook, download the Filter All Pivot Tables with One Slicer sample file. The file contains macros, and is in xlsm file format. Requires Excel 2010 or later version.

2) Change Report Filters - Cell Drop Down

In this Excel sample file, on the Pivot Sales sheet, there are several pivot tables, all based on the same source data.

• In cell C2, select a region name from the data validation drop down
• All pivot tables on the Pivot Sales sheet are filtered for the selected region.
• Pivot tables on other sheets are not changed.

Get the Excel File

To see the code, and copy it to your own workbook, download the Filter Cell Drop Down sample file. The file contains macros, and no Slicers, and is in xlsm file format.

3) Change All Pivot Tables - Specific Field

In this sample file, change a specific report filter in any pivot table, and that report filter field is changed in all pivot tables in the workbook. All pivot tables are based on the same source data.

• In the pivot table filter macro, the Region field is specified
• Change the Region filter in any pivot table, and all pivot tables in the workbook are filtered for the selected region
• If any other report filter is change, no other pivot tables are affected

Get the Excel File

To see the code, and copy it to your own workbook, download the Change Specific Field sample file. The file contains macros, and no Slicers, and is in xlsm file format.

Get More Sample Files

Here are a few more Excel sample files, with variations on the "Change All Pivot Tables" macros for changing pivot table filters.

1. Change Pivot Table Fields on Specific Sheets -- Change any page field in a pivot table, and the same selections are made in all other pivot tables that contain that page field. Specify which worksheets to change, and which pivot tables and pivot fields to ignore. Uses Slicers, if version is Excel 2010 or later. Sample code from Jeff Weir. PivotMultiPagesChange_JW.zip
2. Change Specific Page Fields with Multiple Selection Settings -- Create a list of page fields that should be automatically changed. Change any of the specified page fields in a pivot table, and the same selections are made in all other pivot tables that contain that page field. Also changes the "Multiple Item Selection" settings to match changed page fields. PivotMultiPagesChangeSet2010.zip
3. Change All Page Fields - All Sheets or Active Sheet -- This sample file has 3 variations on the "Change All Page Fields" code:
   • a) Change any page field in a pivot table, and all matching page fields, on all sheets, are changed.
   • b) Change any page field in a pivot table, and all matching page fields, on the active sheet only, are changed.
   • c) Change a specific page field in a pivot table, and that page field, on the active sheet only, is changed. PivotMultiPagesChangeAllVar.zip

Video: Using Report Filters

See the steps for adding and applying pivot table report filters in this short video tutorial. There are written instructions on the Pivot Table Report Filters page.

Get Monthly Excel Tips!

Don't miss my monthly Excel newsletter! You'll get quick tips, article links, and a bit of fun. Add your email, and click Sign Up.

Next, when you get my reply, click the Confirm button. I add this step to protect you from spam!

More Pivot Table Help

Pivot Table Report Filters

Report Filter Macros

FAQs - Pivot Tables

Pivot Table Introduction

Running Totals

Summary Functions

Clear Old Items in Pivot Table