With PivotTable slicers, you can filter a PivotTable using visual buttons. Slicers also show the current filtering state, by greying out positions that are not present in the result.
Pivot Table slicers vs filters
Slicers are not necessary to filter data inside a PivotTable, they are just a helpful way to make filtering more visually appealing than with standard filters.
Creating a PivotTable and adding a slicer
Before you add a slicer, you have to create a PivotTable from a standard table. You can download the data I used under this link.
Insert a PivotTable (Insert >> Tables >> PivotTable).
Click the PivotTable and on the right side, inside the PivotTable fields check: Film, Director, and Actor.
Drag Actor to filters and Director and Film to ROWS.
This configuration will create a list of directors and movies that they made. There is also a filter where you can choose only movies where the selected actor played a role.
Now, let’s create a slicer. Click the PivotTable and go to PivotTable Tools >> Analyze >> Filter >> Insert Slicer or Insert >> Filters >> Slicer.
Select Director to create a slicer.
You can click any button inside the slicer, and the filtered data will change accordingly.
The multiple-column view
As you could see inside the Insert Slicers window, you can add more than just one slicer.
All of the selected slicers will be connected to one another. This time select Film, Director and Actor. It will create three slicers, which you can drag and scale.
If you choose one button, the available option will be highlighted.
A few ways to filter a slicer
To select another position on the slicer just click it. You can also select multiple options by holding the Ctrl button and clicking the other option.
Now, you are going to see all the available options. In this case all movies from both directors and all actors on the list that played a role in these movies.
Multiple columns in the slicer
The Actor slicer is a bit too tall and you can’t see all the results. In Excel, you can choose to display the slicer buttons in more than one column.
Click the slicer and go to Slicer Tools >> Buttons. Here, there is a column field. Change the number of columns to 3.
It keeps the same width, so only part of the text will be visible. But you can easily control the height and width by dragging one of the edges.
Connect the slicer to multiple pivot tables
Copy the PivotTable and make a copy, by selecting all cells of the pivot table and pressing Ctrl + C. To paste click a cell where you want to paste and press Ctrl + V.
Modify both PivotTables.
Select only the Director and Film options. Your order should look like this.
In the second PivotTable Genre and Film. This is the order of the second PivotTable.
This is going to give you the following result.
Click the first PivotTable and choose PivotTable Tools >> Analyze >> Filter >> Insert Slicer.
If you click any of the buttons, you will see that only the first PivotTable changed and the second one is not affected. It works this way because you’ve created slicer only for the first PivotTable.
But there is a way to create a slicer that will affect any number of PivotTables.
Click the slicer and go to Slicer Tools >> Options >> Report Connections.
Select both PivotTables and click OK.
Now, if you click a button, both pivot tables will change accordingly.