Excel PivotTable Filter and Slicers

Filter a PivotTable

Filters let you focus on part of the data, for example one region or the best-selling products.

The source data does not change. Only the PivotTable shows less.

Start with a PivotTable that has Category in Rows and Sum of Sales in Values, formatted with a thousands separator. See Create a PivotTable and PivotTable Values.

The Filters Area

A field in the Filters area filters the whole PivotTable.

Let's show the sales of the Johto region only:

1. Drag Region to the Filters area
2. Excel adds a filter above the PivotTable, in A1:B1. It shows (All)
3. Click the arrow in B1
4. Choose Johto
5. Click OK

Only the Johto orders are counted now. Johto sold 3,363,650 of the 9,416,800 total.

To pick more than one region, check Select Multiple Items at the bottom of the list. The filter cell then shows (Multiple Items).

To remove the filter, choose (All) again.

Sort a PivotTable

Set the Region filter back to (All). Then uncheck Category and drag Product to the Rows area.

Excel lists the products from A to Z. To put the best sellers on top:

1. Right-click any number in the Sum of Sales column
2. Choose Sort > Sort Largest to Smallest

The Poke Ball is the best seller. The Antidote sells the least.

To sort by name again, click the arrow in A3 (Row Labels) and choose Sort A to Z.

Read more about sorting in Excel Sort.

Label Filters and Value Filters

Click the arrow in A3 (Row Labels). The menu has two kinds of filters:

• Label Filters filter by the names of the items, for example products that contain a word
• Value Filters filter by the numbers, for example the top 3 products by sales

Label Filter

Show only the products with "Potion" in the name:

1. Click the arrow in A3
2. Choose Label Filters > Contains
3. Type Potion
4. Click OK

The sort from before still applies.

To remove the filter, click the arrow in A3 and choose Clear Filter From "Product".

Value Filter: Top 3

Clear the label filter first. Then show the three best-selling products:

1. Click the arrow in A3
2. Choose Value Filters > Top 10
3. The dialog says Top 10 Items by Sum of Sales. Change 10 to 3
4. Click OK

The Grand Total only adds up the items you can see: 5,044,800, not 9,416,800.

The same dialog can show the Bottom items instead, or a percent or sum of the total instead of a number of items.

Clear the filter before you go on.

Slicers

A slicer is a box of buttons that filters the PivotTable.

It is easier to use than the filter arrows, and it shows which filter is on at a glance.

Remove Region from the Filters area first. Then add a slicer for Region:

1. Click any cell in the PivotTable
2. Click PivotTable Analyze > Insert Slicer
3. Check Region
4. Click OK
5. Click Johto in the slicer

To select more than one item, hold down Ctrl while you click, or turn on the Multi-Select button at the top of the slicer.

To show everything again, click the Clear Filter button in the top right corner of the slicer.

Timeline

A timeline is a slicer for dates. It needs a column with real Excel dates, like the Date column.

1. Click any cell in the PivotTable
2. Click PivotTable Analyze > Insert Timeline
3. Check Date
4. Click OK
5. The timeline shows months. Click MONTHS in its top right corner and choose YEARS
6. Click 2025

With Johto in the slicer and 2025 in the timeline, the PivotTable shows the Johto sales for 2025:

The Date field is not in the PivotTable at all, but the timeline can still filter by it.

One slicer can also control several PivotTables that use the same data: right-click the slicer, choose Report Connections, and check the PivotTables it should filter.