The Pivot table filters guide
A field in a pivot table's Filters area picks which rows of the data go into the table, without adding a line per item. Put Region there, pick North, and the whole table is about North; pick South and it redoes itself.
Filters, Rows, Values
- Rows: one line per item of the field, such as one per product.
- Values: the number worked out for each line: Sum, Count or Average.
- Filters: a field that decides which rows of the data count at all. It sits above the table as a small dropdown,
Region | North, and doesn't add lines.
Build it
- Make the pivot table as usual: click the data, Insert → PivotTable. See your first pivot table.
- Tick Product (Rows) and Sales (Values). Open the field in Values and choose Average.
- Drag Region to Filters. A dropdown appears above the table, showing (All).
- Pick North in the dropdown. In this practice sheet, use Move to Filters in the field list and pick the item there.
Filter or Rows?
With Region in Rows next to Product, you get a line for every region and product. That answers a different question, and anyone who wants their own region has to search the table for it. In Filters, one table answers "for this region", and switching the dropdown answers it for the next one.
A slicer (PivotTable Analyze → Insert Slicer) does the same job with buttons on the sheet and shows what is selected. A filter set to several items only says "(Multiple Items)", so say which ones in the title.
Common mistakes
- The field in Rows instead of Filters. The table lists every region instead of one.
- Sum left on. Values starts on Sum for a number field. An average sale per product needs Average.
- A filter left on. A pivot table filtered to North last week still shows only North. Check the dropdown before you read a total.
The formula way to the same answer is AVERAGEIFS with a region condition, or FILTER for the rows themselves.
FAQ
- How do I filter a pivot table by a field that isn't in it?
- Drag the field to the Filters area of the field list. A dropdown appears above the pivot table; pick an item and the whole table shows only rows with that item.
- What is the difference between a pivot table filter and a slicer?
- They filter the same way. A slicer shows buttons on the sheet and makes the selection visible; a report filter is a dropdown above the table.
- How do I show an average instead of a sum in a pivot table?
- Click the field in the Values area, choose Value Field Settings (in this practice sheet, the dropdown) and pick Average.
- Can I filter a pivot table by more than one item?
- Yes. In the filter dropdown, tick Select Multiple Items and choose them. The filter cell then says (Multiple Items).



































































