MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel 2019: Slicers for Pivot Tables From Two Data Sets

June 26, 2019 - by Bill Jelen

Excel Slicers for Pivot Tables From Two Data Sets. Photo Credit: Katie Smith at

Say that you have two different data sets. You want a pivot table from each data set and you want those two pivot tables to react to one slicer.

This really is the holy grail of Excel questions. Lots of Excel forums have many complicated ways to attempt to make this work. But the easiest way is loading all of the data into the workbook data model.

Both of the tables have to have one field in common. Make a third table with the unique list of values found in either column.

Two different pivot tables have a field called Store Name. Each pivot table is coming from a different source table.

If the two tables are shown above, the third table has to have Store Names that are found in either table: Brea, Chino Hills, Corona Del Mar, Dos Lagos, Fashion Valley, Irvine, and so on.

Format all three of your tables using Ctrl+T.

Use the Relationships icon on the Data tab to set up a relationship from each of the two original tables to the third table.

If you have access to the Power Pivot grid, the diagram view would look like this:

Put the new Stores table in the middle, with a relationship to the Last Year table and another relationship to the This Year table.

If not, the Relationships diagram should show two relationships, although certainly not as pretty as above.

From either pivot table, choose Insert Slicer. Initially, that slicer will only show one table. Click on the All tab and choose Mall Developer from your third table.

The Insert Slicer dialog has two tabs. The Active Tab only offers one table. You have to use the All tab to see all three tables. The Slicer should always be based on the TblStores table - the table in the center of the relationship diagram.

Try and choose a few items from the slicer. Watch your hopes get dashed as only one pivot table reacts. Once you recover, click on the Slicer. In the Slicer Tools Design tab of the Ribbon, choose Report Connections. Add the other pivot table to this slicer:

From the Slicer Tools tab in the Ribbon, select Report Connections. Hook the slicer up to both of the original pivot tables.

Finally, both pivot tables will react to the slicer.

Both pivot tables are now reacting to the slicer.

Title Photo: Katie Smith at

Bill Jelen is the author / co-author of
MrExcel 2020 - Seeing Excel Clearly

This is a 4th edition of MrExcel LX. Updates for 2020 include: Ask a question about your data, XLOOKUP, Power Query's Data Profiling tools, How Geography Data Types decide which Madison, A SEQUENCE example for descending 52 weeks, Exchange Rates support in Stock Data Types, How to collapse the Search box, How to leave effective feedback for Microsoft, How to post your worksheet to the MrExcel Board using XL2BB.