Connect Slicer to Multiple Pivot Tables (Step-by-Step)

Excel Pivot Tables help us to analyze a large set of data in a matter of seconds.

We can apply slicers to Pivot Tables and filter our data based on certain criteria. Even if you have multiple Pivot Tables, we can use the same slicers to filter data across all Pivot Tables.

To do that we need to connect the slicer to all the Pivot Tables.

In this article, I’ll show you how to connect a slicer to multiple Pivot Tables.

Table of Contents

Connect Slicer to Multiple Pivot Tables (Same Data Source)

Sometimes we create many Pivot Tables from the same data set to analyze the data from different angles.

Using common slicers across all Pivot Tables is a smart idea. Otherwise, when comparing data in different Pivot Tables, the user has to select the same criteria from multiple slicers. It is a waste of time and may lead to errors.

Below I have two pivot tables that I have created using the same data source. The first Pivot Table shows the product-wise sales analysis and the second Pivot Table shows the day type-wise sales analysis.

Two pivot tables created using same data source

Now I want to add a common slicer to filter by the agent.

First, I need to add a slicer to one of the Pivot Tables. I can do it the following way.

Adding a Slicer

In this case, I am going to add the agent slicer to my first Pivot Table.

First, select any cell on my first Pivot Table to activate the PivotTable Analyze tab.

Go to the PivotTable Analyze tab and click the “Insert Slicer” icon. We can find the “Insert Slicer” icon in the “Filter” group.

Select the fields that we need for slicers from the Insert Slicers dialog box. In this example, I want the agent field for my slicer. So, I put a checkmark on the agent check box.

Select the fields you want to show in the slicer

Click the “OK” button of the Insert Slicers dialog box.

Now I have inserted the agent slicer for my first Pivot Table.

slicer inserted for the first pivot table

Currently, this slicer is connected only to the first Pivot Table. If I filtered “Agent A” from the slicer, the first Pivot Table (Product wise) is filtering for Agent A. But, the second Pivot Table (Day type wise) is not filtering for Agent A. I can see both Agent A and Agent B on the second Pivot Table.

Slicer filters data in one pivot table only

Now, I want to connect this slicer to my second Pivot Table.

Connecting a Slicer to Another Pivot Table

Now, I am going to connect the Agent slicer of the first Pivot Table to the second Pivot Table.

Select the slicer to activate the “Slicer” tab.

Go to the Slicer tab and click on the “Report Connections” icon (which is in the Slicer group).

Select all the Pivot Tables that we need to connect with the selected slicer from the “Report Connections” dialog box. As I want to connect the slicer with the Pivot Table 2, I am inserting a check mark for the Pivot Table 2 check box.

Select all the pivot tables to connect the slicer

Click the “OK” button in the Report Connections dialog box.

Now, I have linked the Agent slicer to both Pivot Tables. So, when I filter my data for Agent A, both Pivot Tables will be filtered for Agent A.

Slicer filters data in multiple pivot tables

Also read: How to Group by Months in Excel Pivot Table?

Connect Slicer to Multiple Pivot Tables (Different Data Sources)

Sometimes we create Pivot Tables that are from different data sources. Still, we may need to add a common slicer for the Pivot Tables. We can add common slicers to filter data by linking those two data sources.

I have a sales data set and a returns data set for a stationery manufacturer.

The below sheet shows the sales data.

The below sheet shows the returns data.

Now I want to create two Pivot Tables to analyze sales and returns. Further, I want to have a common agent slicer for the two Pivot Tables.

Create Pivot Tables Using a Data Model

As the data sources for two Pivot Tables are not common, first I need to create two Pivot Tables using a Data Model.

Create a new sheet and rename the sheet. I am creating a new sheet called “Pivot Tables”.

Select a cell on this new sheet to insert the Pivot Table for sales data. Then, go to the “Insert” tab and click the “Pivot Table” icon.

Click on the pivot table option in the insert tab

Select the “Sales” table for the Table/ Range.

Insert a checkmark to “Add this data to the Data Model”.

Check the option at this data to the data model

Click the “OK” button.

Now, I can see the Pivot Table Fields and I can create the Pivot Table as creating a normal Pivot Table.

This is the Pivot Table that I have created for sales data.

Following the same steps above, I am creating a Pivot Table for “Returns” data.

Now, I have two Pivot Tables in the “Pivot Tables” sheet.

Another paper table is created for the returns data set

I am renaming these Pivot Tables as follows for easy reference.

Connect Pivot Table Data Models

Now, I want to connect these two Pivot Table data models. To do that I have to follow the steps below.

Identify the common data field for both data sets. In this example, I am choosing the agent column.
Next, go to any of the two data sets and copy the data in the identified column. So, I am going to the “Sales” sheet and copying the Agent column using the “Control + C” shortcut.

Copy the identified column in the pivot table source data

Insert a new sheet to paste the data and rename the sheet. I am inserting a sheet called “Agent”. Paste the copied data in this sheet.

Select the pasted data and go to the “Data” tab.

Click the “Remove Duplicates” icon (which is in the Data Tools group).

Check the “My data has headers”, column name “Agent” and click the “OK” button of the “Remove Duplicates” dialog box.

Select the column from which you want to remove the duplicate

Now Select the unique entries and create a table. To create a table, I can use the “Control + T” shortcut key.

Rename the Table for easy reference. So, I am renaming this table as “Agents”.

Go to the “Pivot Tables” sheet and click any cell of any Pivot Table and activate the “PivotTable Analyze” tab.

Go to the “PivotTable Analyze” tab and click “Relationships” (which is in the “Connections” group).

Click on the relationships option in the pivot table analyze tab

Click the “New” button in the “Manage Relationships” dialog box.

Now, I can see the “Create Relationship” dialog box. Go to the “Table” box. Expand the dropdown list and select “Data Model Table: Sales”.

Go to the “Column (Foreign)” box. Expand the dropdown list and select “Agent”.

Expand the dropdown list and select Agent

Go to the “Related Table” box. Expand the dropdown list and select “Data Model Table: Agents”.

Then, click the “OK” button of the “Create Relationship” dialog box.

Click the “New” button again in the “Manage Relationships” dialog box.

Go to the “Table” box of the “Create Relationship” dialog box. Expand the dropdown list and select “Data Model Table: Returns”.

Select the table as data model table returns

Go to the “Column (Foreign)” box. Expand the dropdown list and select “Agent”.

Go to the “Related Table” box. Expand the dropdown list and select “Data Model Table: Agents”.

Then, click the “OK” button of the “Create Relationship” dialog box.

Click the “Close” button of the “Manage Relationships” dialog box.

Inserting a Slicer

Now, I have created the relationship between two Pivot Tables. Next, I am going to insert the agent slicer. I have to follow the below steps for that.

Click on any of the two Pivot tables and go to the “PivotTable Analyze” tab.

Click the “Insert Slicer” on the “Filter” group.

Click on the insert slicer option in the filter group

Go to the “All” tab of the “Insert Slicers” dialog box.

Select the “Agent” field of the “Agent” tab and click the “OK” button.

Linking the Slicer to Multiple Pivot Tables

Next, I have to link the slicer to my other Pivot Table. I can do it by following the steps below.

Select the slider and go to the “Slicer” tab.

Go to the Slicer tab and click on the “Report Connections” icon (which is in the Slicer group).

Select all the Pivot Tables that we need to connect with the selected slicer from the “Report Connections” dialog box. As I want to connect the agent slicer of the sales Pivot Table with the Returns Pivot Table, I am inserting a check mark for the Returns Pivot Table check box.