It's very simple example how to control two or more Pivot Tables with one Slicer without using Data Model.
Basic rule:
All tables should have common Primary Key (column with unique value for each row)
For easy to use we can change Range Tables to Structured Tables
- click anywhere inside Range Table
- Ctrl+A
- Ctrl+T
Do the same for each other Range Tables
Now we need to merge all single tables into one with Primary Key condition.
(see Third sheet in example)
To do this you can use formulas such as:
- INDEX(...,MATCH(...,...,...))
- VLOOKUP(...,...,...)
- or any other.
After that select any cell in the third table and create FirstPivotTable then SecondPivotTable by using Insert tab from the ribbon
- Insert - Pivot Table
- Untick Data Model if exist (for higher version than 2010)
Click on the first Pivot Table and from the tab PivotTable Tools, Filter, select Insert Slicer (what kind of slicer it's up to you)
then right click on slicer and select Repoert Connections...
and select these PTs which you want to manage by this slicer
That's all.
Like I said above this is very simple example to show how you can use single slicer with two PTs
Of course you can say: use PowerPivot or PowerQuery and Yes, I can but this example is for users without these features or add-ins.
If you like it - use Add Rep.
Bookmarks