Centralized View – Connect one Slicer to two pivot tables
Sometimes you may need to link one Slicer with two Pivot tables based on the same data source. Slicer is a powerful way to filter pivot table data. We can click on an item in a slicer to filter the data in the pivot table. Slicers provide buttons that you can click to filter tables or Pivot table.
Let’s understand how with the help of an example (Figure 1: Employee Data)
You want to summarize designation wise salary and department wise salary from employee data with Region as filter.
Figure 1: Employee Data
Step 1: Select the employee data from Column A to Column G from 1st row till 21st row.
Step 2: Click on INSERT tab
Step 3: Click on PivotTable
Step 4: Create PivotTable dialog box appears
Step 5: Click on OK
Step 6: Pivot Table sheet appears
Step 7: Drag Design to ROWS area
Step 8: Drag Salary to VALUES area
Step 9: Go to DATA Sheet
Step 10: Select the employee table from Column A to Column G from 1st row till 21st row.
Step 11: Click on INSERT tab
Step 12: Click on PivotTable
Step 13: Create PivotTable dialog box appears
Step 14: Select Existing Worksheet as we want to analyse both the Pivot Tables in the same sheet
Step 15: Change Location to Sheet3 and Cell I3
Step 16: Click on OK
Step 17: Pivot Table sheet appears
Step 18: Drag Dept to ROWS area
Step 19: Drag Salary to VALUES area
Step 20: Go to ANALYZE tab
Step 21: Click on Insert Slicer
Step 22: Insert Slicers dialog box appears
Step 23: Tick Region from the Insert Slicers option
Step 24: Click on OK
Step 25: Region slicer appears
Step 26: Now we have to connect slicer to both the PivotTables
Step 27: Select the Region Slicer
Step 28: Go to OPTIONS tab
Step 29: Click on Report Connections
Step 30: Report Connections (Region) Dialog box appears
Step 31: Select PivotTable2 tick box
Step 32: Click on OK
Step 33: Now Slicer is connected to both the PivotTables
Step 34: Let’s check with Selecting Canada Region
Step 35: We get the correct result in both the Pivot Tables.
Step 36: Let’s select Canada & Italy region from the slicer by pressing Ctrl and selecting both the region
Step 37: We get the desired result in both the Pivot Tables.
Step 38: After formatting the Salary in USD in both the Pivot Tables.
Step 39: Press Alt+C or Click on Clear Filter button provide in the Slicer box to clear the selection
Step 40: After clearing the slicer filter.
Scope of usage
- Can be used to filter data in a table or Pivot Table
- Can be used to connect one slicer to multiple Pivot Tables
- Can be used to analyze multiple Pivot Tables
- Can be used to create a central data view