Log In

Pivot Table Filter

How to Filter PivotTables in Excel

Excel Training

Excel Training Features:

  • Interactive bite-sized lessons

  • Get Certified

  • Accurate skill assessments

  • Customizable, LMS-ready content

Prices
Users love us
4.9 stars out of 5 on G2

4.9 out of 5 on G2

Pivot Table Filter

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

  1. Click any cell in the PivotTable.
  2. Drag a field to the Filters area in the PivotTable Fields pane.
    Filter Pivottables

    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.

  3. 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.

  4. Select the item(s) you want to use as a filter.
  5. Click OK.
    Filter Pivottables

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.

  1. Click the filter icon next to the filter field.
  2. Select All.
  3. Click OK.
    Filter Pivottables

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.

  1. Click and drag the field out of the Filters area.
    Filter Pivottables

    You could also uncheck the field in the fields list.

The filter is removed from the PivotTable entirely.

You Get:
  • LinkedIn-ready certificate
  • Skill assessments
  • Bite-sized, interactive lessons
  • Saved progress

Links to Other Courses