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 Unsplash.com

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 Unsplash.com


Bill Jelen is the author / co-author of
MrExcel LX – The Holy Grail of Excel Tips

A book for people who use Excel 40+ hours per week. Illustrated in full color.