A PivotTable report filter lets you show only the records that match a chosen item, without changing or deleting the source data. In Excel for Microsoft 365 on Windows, add a field to the PivotTable Fields pane’s Filters area; Excel then places a filter control above the PivotTable.
What a report filter does
A report filter applies a selection to an entire PivotTable. For example, suppose a sales table has columns for Region, Salesperson, Product, Date, and Revenue. If Region is placed in the Filters area, you can choose West to see results for that region alone, or choose East, North, or another available item. The PivotTable recalculates its totals using records that match the selection.
Excel’s field list calls the area Filters. Many guides and users call a field placed there a report filter, page filter, or PivotTable filter. The control appears above the PivotTable, usually as a field name followed by a drop-down. You can place more than one field in Filters; the controls appear one below another.
A report filter changes what the PivotTable displays, not the underlying records. Choosing West does not remove other regions from the worksheet source. You can switch the selection later, and the PivotTable can still summarize the other regions. This makes a report filter useful for focused views while preserving the ability to explore the full dataset.
How to add a report filter
- Click a cell in the source data. For the most dependable results, use a rectangular table with one header row, a distinct heading for every column, and no completely blank rows or columns within the data.
- Choose Insert > PivotTable. Check that the proposed table or range covers the records and headings you intend to summarize, choose where to put the PivotTable, and select OK.
- In the PivotTable Fields pane, drag the field you want to filter by, such as Region or Year, into the Filters area. If the pane is not visible, click inside the PivotTable and choose PivotTable Analyze > Field List.
- Use the new drop-down above the PivotTable to choose an item. The displayed totals update to reflect that selection.
For instance, put Revenue in Values, Product in Rows, and Region in Filters. Choosing West produces a product-by-product revenue summary for the West region. You could also put Year in Filters to narrow the results further. The filter controls are separate from the row and column fields that organize the summary itself.
In Excel for Mac, the PivotTable Builder pane provides the field areas, and you can place a field in Filters there. Ribbon labels and pane layout may differ somewhat from Windows. In Excel for the web, available PivotTable editing options can depend on the workbook and current web version; if a command is missing, open the workbook in the desktop app.
Choosing items and filtering multiple values
Click the report filter’s drop-down to see the available items. Choose one item to display just that item, or choose (All) to clear the restriction for that field. If the source contains a blank value, Excel may show a blank item in the list; selecting it filters for records whose field is empty. An item may also appear because it exists in the PivotTable’s saved item list even if it is no longer present in the current source, depending on the PivotTable settings.
To select several items in one report filter, open its drop-down and select the Select Multiple Items check box. Then tick the items to include and confirm the selection. For example, selecting East and West shows those two regions together, while leaving Central and North out. The control may display (Multiple Items) rather than listing every selected value.
When multiple report filter fields are used, their selections combine: records must match the selection in each field to contribute to the result. Choosing Region = West and Year = 2025 therefore summarizes the West-region records for 2025, not all West records plus all records from 2025. This interaction can make a report precise, but it can also explain why a PivotTable shows no data if the chosen combination has no matching records.
Report filters versus other PivotTable filters
Report filters are not the only way to narrow a PivotTable. The best option depends on whether you want to filter a whole report, hide particular row or column items, or give readers a more visual control.
- Report filter: Applies a field selection to the whole PivotTable and places the control above it. It works well when a report should focus on one region, department, or year at a time.
- Row or column label filter: Opens from a field’s drop-down within the PivotTable. It can hide particular labels or apply label and value conditions to the row or column items.
- Value filter: Filters row or column items according to summarized values, such as showing products whose total revenue exceeds a threshold. It is not the same as selecting a source-data value in a report filter.
- Slicer: Adds clickable buttons on the worksheet for filtering a PivotTable. Slicers are more visible and can be easier for other people to use, especially when the report has several choices.
To insert a slicer, click the PivotTable and choose PivotTable Analyze > Insert Slicer, then select the fields to display. A slicer can be connected to compatible PivotTables that share the same data source or PivotCache: select the slicer, open the Slicer tab, and choose Report Connections or PivotTable Connections, depending on the Excel build. A report filter, by contrast, is a compact drop-down belonging to its PivotTable.
Creating separate sheets for filter items
If you need a separate worksheet for each item in a report filter field, Excel for Windows includes Show Report Filter Pages. First place the desired field in the Filters area and set up the PivotTable as needed. Click inside it, then choose PivotTable Analyze > Options > Show Report Filter Pages. Select the report filter field when prompted and confirm. Excel creates worksheets with PivotTable views for the available items in that field.
This feature is useful for preparing region-specific or department-specific views, but it can create many sheets if the field has many unique items. It also creates separate worksheet views rather than a single interactive filter control. Review the output before distributing it, especially if the workbook contains sensitive data: hiding a region with a filter is not the same as removing its data from the workbook.
Common problems and practical checks
If the Filters area or filter control is missing, click inside the PivotTable and check that the PivotTable Fields pane is open. If the field list itself is hidden, use PivotTable Analyze > Field List. Confirm that the field has actually been placed in Filters, rather than Rows, Columns, or Values; those areas give the field a different role.
If an expected choice is missing, check the source column for inconsistent spellings, extra spaces, or values stored in different forms. For example, West and west may be treated as distinct text items. If new records were added after the PivotTable was created, refresh it using PivotTable Analyze > Refresh, or right-click the PivotTable and choose Refresh. Refreshing updates the summary from its source; it does not necessarily expand a fixed source range to include rows added outside that range. Using an Excel Table as the source can help new rows be included when the PivotTable is refreshed.
A report filter that produces an empty summary may be working correctly: the selected combination could have no matching records. Try setting each filter to (All) and then reapply selections one at a time. Also check whether a row or column filter is active, since multiple filters can narrow the result together. If totals look stale, refresh the PivotTable and verify that the source data contains the latest values.
Finally, a filter is not a security feature. Anyone with access to the underlying workbook may be able to change the filter or inspect the source data. If people should not receive particular records, do not rely on selecting a report filter to conceal them; create and distribute an appropriate copy that excludes those records, following your organization’s data-handling rules.
When a report filter is a good choice
Use a report filter when readers need to view one category or a small selection of categories at a time and a compact control above the summary is sufficient. Typical choices include territory, fiscal year, business unit, or product category. It is especially effective when the PivotTable layout stays the same while the selected slice of data changes.
Choose a slicer if the filter needs to be obvious, if users may not notice a small drop-down, or if they need to select among many items quickly. Use row or column filters when the aim is to rank or exclude items inside the layout—for example, to show only products with high summarized revenue. You can combine approaches, but explain the controls clearly so a reader can tell which selections are active.
Frequently Asked Questions
What is the report filter area in an Excel PivotTable?
The report filter area is the Filters section of the PivotTable Fields pane. A field placed there appears as a drop-down above the PivotTable and can limit the summary to selected values.
Can I select more than one item in a report filter?
Yes. Open the filter drop-down, select Select Multiple Items, and check each value to include. Excel may show (Multiple Items) when more than one choice is active.
Does filtering a PivotTable delete or change the source data?
No. A report filter changes which source records contribute to the visible PivotTable summary. It does not delete or edit the underlying rows.
Can one report filter control more than one PivotTable?
A report filter drop-down belongs to its PivotTable. To filter multiple compatible PivotTables together, use a slicer and connect it to the other PivotTables through Report Connections or PivotTable Connections.
Why does my report filter show (blank)?
At least one source record may have an empty value in that field. Check the source column for genuinely blank cells, formulas returning empty text, or incomplete records, then refresh the PivotTable if you changed the source.
How do I remove a report filter?
Choose (All) in its drop-down to stop restricting the PivotTable by that field. To remove the filter control altogether, click inside the PivotTable and drag the field out of the Filters area in the PivotTable Fields pane.
Related
- Capitalize First Letter in Excel Quickly
- Quick Search in Excel: Find Data Fast
- Superscript in Excel: Quick Key and Formatting Steps
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel COUNTIF Greater Than: Formula & ExamplesTo count cells in Excel that are greater than a specific value, use the formula =COUNTIF(range,">"&value). For example,…
- Excel Rank Using COUNTIFS: Formula, Ties, and GroupsTo rank numbers in Excel with COUNTIFS, count the values greater than the current value and add 1.…
- Capitalize in Excel Without a Formula: 3 Easy MethodsTo capitalize text in Excel without a formula, use Data > Flash Fill: type the capitalization you want…
- Excel COUNTIF Text Value: How to Count Cells with TextTo count cells that contain any text value in Excel, use =COUNTIF(range,"*?"), and to count cells with a…
- Excel Filter Duplicates: Is Matching Case-Sensitive?No. In Excel for Microsoft 365 on Windows, the regular filter and built-in duplicate tools do not distinguish…
- Excel Date Difference in Years: Formulas That WorkTo find the number of complete years between two dates in Excel, use =DATEDIF(A2,B2,"Y"), with the earlier date…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.