Pivot Table Slicers

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.

First PivotTable

Select only the Director and Film options. Your order should look like this.

Second PivotTable

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.

Excel 365 Update

  • New Slicer Formatting Options: Excel 365 offers more options for customizing the visual appearance of slicers. You can now change the background color, font, and border styles, allowing for better integration with your spreadsheet design.
  • Improved Slicer Filtering Experience: Excel 365 provides clearer visual indications of filtered items within slicers. When selections are made, the corresponding buttons are highlighted, making it easier to understand the impact of applied filters.
  • Slicer Selection with Drill Down: In Excel 365, you can hold the ‘Ctrl’ key while selecting slicer items. This enables you to drill down into specific data points within the pivot table, providing a more granular level of analysis.

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.