Pivot Table Filter
How to Filter PivotTables in Excel
Much like you can with basic data ranges and tables in Excel, you can filter a PivotTable to focus in on a smaller portion of data.
For example, instead of showing the sales values for every destination, you can add the Commission field as a report filter to show only the sales for which an agent earned a commission.
Add a Filter Field
- Click any cell in the PivotTable.
- Drag a field to the Filters area in the PivotTable Fields pane.
The filter field appears at the top of the PivotTable. The text (All) is displayed so you know the data is not currently being filtered.
- Click the list arrow for the field you’ve added as a filter.
A list of sorting and filtering options appears. The items available in the menu will differ based on the field you’ve added as a filter. Additionally, a Search field appears at the top of the filter list. If desired, manually type the criteria by which you want to filter.
You can also filter the row or column headings in a PivotTable by clicking the Row Labels or Column Labels list arrow and selecting only the value(s) you want to display.
- Select the item(s) you want to use as a filter.
- Click OK.
The PivotTable updates to display only the values that meet the filter criteria.
Clear a Filter
Once you’re done analyzing the filtered data, clear the filter to see all the data in your PivotTable again.
- Click the filter icon next to the filter field.
- Select All.
- Click OK.
The filter is cleared from the PivotTable, showing all the data once again.
Remove a Filter
When a filter is cleared, all the data is visible; however, the filter remains in the PivotTable to use again. If you want the filter gone completely, you’ll need to remove it.
- Click and drag the field out of the Filters area.
You could also uncheck the field in the fields list.
The filter is removed from the PivotTable entirely.
FREE Quick Reference
Click to DownloadFree to distribute with our compliments; we hope you will consider our paid training.
- LinkedIn-ready certificate
- Skill assessments
- Bite-sized, interactive lessons
- Saved progress