Use One Slicer For Pivot Tables From Different Data Sets

June 15, 2023 - by Bill Jelen

Problem: I have two pivot tables that contain a Product column. The pivot tables come from different data sets, so I can not use one slicer to drive both pivot tables.

Strategy: Use a Joiner table that is connected to both data sets. Follow these steps:

1. Convert each of the original data sets to a table using Ctrl+T
2. Create a third data set that contains a unique list of products from each table.
3. Add all three tables to the Data Model using the Power Pivot tab or Power Query.

4. Build a relationship from each table to the joiner table. In Power Query, it will look like this:

The diagram view in Power Query. Expense Table is on the left. Revenue table is on the right. Both are joined to a tiny Product Joiner table in the center. — Figure 1147. The joiner table connects to both data sets.

5. Rebuild your pivot tables using the tables from the Data Model.
6. With one pivot table selected, use Insert, Slicer. Go to the All tab and choose Product from the ProjJoiner table.
7. With the new slicer selected, choose Report Connections from the Slicer Tools tab of the Ribbon. Hook this slicer up to both pivot tables.

The result: Each pivot table reacts to the slicers.

Both the Revenue pivot table and the Expenses pivot tables react to the slicer based on the Product joiner. — Figure 1148. Pivot tables from different data sets reacting to one slicer.

This article is an excerpt from Power Excel With MrExcel

Title photo by Julia Craice on Unsplash