Deploying Single Slicer on Multiple Pivot Tables With Different Data Sources

Welcome to Excel Jet Consult blog post. In this tutorial, we want to learn how to deploy single Slicer to filter multiple Pivot Tables with different data sources. Let’s get started

In the caption below, we have two Excel Tables. The first Table spans from column A to C while the second Table spans from column E to F

In the first table, we have Account Manager’s name in column A which contains many repeated values. This would serve as our Foreign key. In the second table, we have Account Manager’s name in column E which are unique records. This would serve as our Primary key

Add To the Data Model

We want to load the two Tables into the Data Model (Power Pivot). Note that, the Power Pivot tabs must be available.

• Click any cells in the first table
• In the Power Pivot tab, click on Add to Data Model

• Press CTRL + Tab to return the Excel
• Click any cells in the second table
• In the Power Pivot tab, click on Add to Data Model

In the caption below, we have the two tables loaded into the Data Model

Create Relationship

We want to create one-to-many relationship between the Account Manager in the Data1 and Account Manager in Data2.

• In the View group of the Home tab, click on Diagram view
• Drag Account Manager from either of the two tables and connect to Account Manager in the corresponding table. Data1 represent the Foreign key (repeated values) and Data2 represent the Primary key (unique values). See the caption below

PivotTable Report

• In the Home tab, click on PivotTable and choose location to place the PivotTable. In this case, I choosed New Workbook.
• Next, from the Data1 field, I dragged and dropped Account Manager and Total into the Rows and Values areas respectively. I have the report as seen in the caption below

• Next, I copied the first PivotTable report using CTRL + C and Pasted in B16 using CTRL + V
• Removed Account Manager and Sum of Total
• From the Data2 field, I dragged and dropped States and Account Manager into the Rows and Values areas respectively. In the caption of the report below, there are 3, 4, 3 Account Managers operating in Abuja, Lagos and PHC States respectively

Insert Slicer

To insert the slicer:

• In the Filter group of the PivotTable Analyze tab, click on Insert Slicer
• Check the Account Manager box in Data2 as seen in the caption above

• Click OK.

In the caption below, the Slicer for Account Manager is available to filter the Data2.

However, we want the Account Manager Slicer to govern the PivotTable1 (Data1).

 

Report Connection

To connect to the PivotTable1, we need to create Report Connection:

• Select the Slicer and click on Slicer contextual ribbon tab
• Click on Report Connection
• In the intermediate dialogue box, check the PivotTable1 box

• Click OK. Job Done

Now, when we filter any Account Manager from the Slicer, both PivotTables with different data sources dynamically responds as seen in the caption below

 

Thank you for taking time out to read our blog. See you in the next time