See all How-To Articles

How to Filter Pivot Table Values in Excel

This tutorial demonstrates how to filter pivot table values in Excel.

 

pivotfilter intro

 

In-built Filter Area in a Pivot Table

When you create a pivot table, the column headers in the data become the fields of the pivot table. You can drag these fields in the data down to various areas of the pivot table – one of which is the Filters area.

 

pivottable filters area

 

You can then filter the pivot table by this field. Select the drop-down filter and then select the item to filter one and finally select OK.

 

pivotfilters select filter

 

Your data is filtered accordingly.

 

pivotfilters show one filter

 

To clear the filter, click on the drop-down list, and then click on (All) and click OK.

 

pivotfilter filter clear

 

If you want to filter on multiple items, tick the Select Multiple Items check box.

 

pivotfilters filters multiple

 

Then, take the tick off the (All) check box and tick the items you wish to filter on. Finally, (3), click OK.

 

pivotfilters select multiple

 

The filter indicates that you are filtering on multiple items.

 

pivotfilters show multiple

 

To clear the filter, click on the drop-down list and then click on (All) once again and click OK.

Filtering by Row and Column Labels

You can also filter by the Row and Column Labels. Click on the drop down to the right of the Column Labels label and then remove the tick from (Select All) and tick the item you wish to filter on.

 

pivotfilter column filter

 

Click OK to filter the data.

 

pivotfilter column filtered

 

Similarly you can filter the Row Labels

pivotfilter row filter

 

Filter Pivot Table Values by Date

If you have dates in your pivot table, you can filter by these dates. This can be done in the Filter area, Row area or Column area.

When a date is added to the Row or Column area, it is automatically broken down into 3 fields – Years, Quarters and the actual value of the date field itself. All 3 of these fields are then added to the Row or Column area.

 

pivotfilter date field

 

You can then click the Row Labels filter drop down, and then in the Select Field drop down, select the actual field to filter on.

 

pivotfilter date select year

 

Amend the filter field to filter on Quarters instead of Years.

 

pivotfilter date select quarter

 

If you select the actual field to order on (e.g., Order Date), you’re then able to filter by Months.

 

pivotfilter date months

 

You can also select Date Filters from the menu, and then select the filter you need – for example, This Year.

 

pivotfilter date select this year

 

This immediately filters the data for this year only.

 

pivotfilter date filtered

 

You can also be more specific and select the start and end dates to filter on.

In the drop-down list, select Date Filters > Between.

 

pivotfilter date between

 

Then select the start and end dates to filter on and click OK.

 

pivotfilter date between dates

 

Notice a small filter icon in the row labels drop-down box indicating that your data has a filter applied to it.

 

pivotfilter date filter graphic

 

To clear the filter, click Clear Filter from “Years” in the drop-down menu.

 

pivotfilter date filter clear

 

Create a Filter

The Values shown in a pivot table do not have a filter automatically in place for them.

To create a filter on these values, place your cursor to the right of the pivot table, and then, in the Ribbon, select Home > Editing > Filter.

 

pivotfilter filter add filter

 

This will add a filter to the header of each of your column labels. You can then filter on the values accordingly. For example, you can select any of the individual values in the list, or you can select one of the Number filters such as values that are Greater Than a certain amount.

 

pivotfilter values