How to Use Pivot Table Slicers in Excel

Written by

Mel Jenkins

Reviewed by

Laura Tsitlidze

Last updated on March 12, 2023

This tutorial demonstrates how to use pivot table slicers in Excel.

 

Slicer intro

 

One option for manipulating and filtering data in Excel is to use pivot table slicers. Read on for how to create and format slicers, how to use them to filter data, and additional useful settings.

Create Slicer

  1. Click in your pivot table, and then, in the Ribbon, go to PivotTable Analyze > Insert Slicer.

 

Slicer ribbon

 

  1. Tick the slicers to insert.

 

Slicer select slicers

 

  1. Click OK to add these next to your pivot table.

 

Slicer slicers

 

  1. You can drag them around in the current spreadsheet to organize their position on the screen by clicking in the title bar of the slicer and dragging the slicer box across the screen.

 

Slicer move

 

Format Slicer

If you want to easily differentiate between the different slicers (if you have added more than one), you can format the slicers to be shown in different colors.

Click on the slicer and a new tab called Slicer is shown in the Ribbon. In this tab, click Slicer Styles, and then choose a style for your slicer.

 

Slicer format

 

Slicers as Filters

To filter using the slicer, you can add or remove items from the list in the slicer. For example, in the slicer graphic below, all the data is shown by Sales Person.

 

Slicer Filter include

 

  1. Click DP on the slicer to deselect it and filter it out of the data shown in the pivot table. Salesperson DP is excluded.

 

Slicer Filter exclude

 

  1. To only show one salesperson’s data, click each of the other salespeople in the slicer to remove them.

 

Slicer filter single

 

You can choose more than one entry in the list, so you can filter on more than one salesperson at a time.

 

Slicer filter multi select

 

  1. To only allow the selection of one item on the list at a time, click to switch off the Multi-Select button (or press ALT + S).

 

Slicer filter single select

 

  1. To clear the filter and add all the salespeople back into the pivot table, click the Clear Filter button (or press ALT + C).

 

Slicer filter clear filter

 

You can now only filter on one item at a time. In the example below, the sum of the Ordered amount is shown for a single salesperson and a single supplier.

 

Slicer filter single filter

 

Slicer Settings

There are a few settings you can customize in your slicer, such as sorting the list items into ascending or descending order or hiding items when they don’t exist within the current slicer selection(s).

  1. Click on the slicer and then, in the Ribbon, go to Slicers > Slicer Settings.

 

Slicer settings

 

  1. Tick Hide items with no data and click OK.

 

Slicer filter hide items

 

  1. Any items that do not have any data, are removed from the list of items. For example, in the graphic below you are filtering on the supplier Telecom. As there is only data for two salespeople for Telecom, only these two items are shown on the Sales Person slicer.

 

Slicer filter hide data

 

Tip: Try using some shortcuts when you’re working with pivot tables.

AI Formula Generator

Try for Free

See all How-To Articles