How to add slicers to a pivot table

Pivot table slicers provide the same function as filters - they allow you to selectively display and hide the items that appear in fields. However, instead of drop-down menus, they provide large friendly buttons that are always visible.

Let's take a look.

Here we have a pivot table that shows sales by Product. If we wanted to filter the entire pivot table by Region, we know from an earlier video that we could just add Region as a Report Filter, and use it to filter as needed.

Slicers work the same way as Report Filters, but they are easier to use.

To show how slicers work, let’s add a slicer for Region and try it out.

To add a slicer to your pivot table, select a cell in the pivot table and navigate the Options tab on the PivotTable Tools ribbon. Then click the Insert Slicer button and select a field from the field list. We’ll check Region, and click OK.

Slicers provide buttons that work just like filters. Click a button to show only that item in the pivot table. Note that the pivot table immediately filters on that item and the Region drop-down displays the filter icon. If we check the filter settings for Region, you can see that the slicer had the same effect as manually filtering.

To enable more than one item at a time, hold down the Control key as you click the buttons. You can also use the Shift key to enable all items between two buttons.

Again, the Report Filter is updated to match the slicer.

Slicers are directly connected to fields in a pivot table. If we add Region as a column label, then filter on that field, the slicer responds as well.

To clear a filter set by a slicer, click the Cancel Filter icon in the upper right.

You can easily add more than one slicer to a pivot table. Let’s add Category as a row label above Product, then add another slicer for Category.

As before, use the Control key to enable multiple values. Notice that the field filter settings match the slicer.

There is no requirement that a slicer field be included in the pivot table. We can remove both Category and Region from the pivot table, and the slicer continues to function normally.

Use the Clear Filter button to reset a slicer, or you can clear all filters at once using the Clear button on the ribbon.

To remove a slicer from your worksheet, just select the slicer and delete.