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