To add a filter to an Excel PivotTable, click inside the PivotTable, open the PivotTable Fields pane, and drag the field you want to filter into the Filters area. Excel adds a report-filter drop-down above the table; choose an item there to show only matching records in the summary.
What a PivotTable filter does
A PivotTable summarizes source records into grouped totals, counts, or other calculations. A filter limits which source records contribute to that summary. For example, a sales PivotTable might show revenue by product and month; filtering its Region field to West recalculates the visible totals using West-region records only.
Filtering a PivotTable does not remove or alter rows in the source data. It changes the view and calculation in the report. That distinction matters when you share a workbook: another person can clear or change a PivotTable filter and see a different result, while the underlying records remain intact.
Excel offers several ways to filter a PivotTable. The Filters area creates a report filter above the body of the table. Row and column label filters work directly on categories in the report, and value filters show categories that meet a numeric condition. Slicers and timelines provide clickable controls for the same general purpose.
Add a report filter step by step
- Click any cell inside the PivotTable. The PivotTable contextual tabs appear on the ribbon.
- If the field list is not visible, select PivotTable Analyze > Field List.
- In the PivotTable Fields pane, locate the field you want to use, such as Region, Salesperson, or Order Date.
- Drag the field into the Filters area at the bottom of the pane. You can also select the field’s check box first, then move it from the area where Excel placed it into Filters.
- In the PivotTable itself, open the new drop-down above the table and select an item. Select multiple items if the menu offers that option and you want more than one value included. Click OK if Excel displays an OK button.
The filter usually appears above the report with the field name and a selected value. If you add more than one field to Filters, Excel stacks additional filter controls there. Their order in the Filters area determines their order in the report, so drag fields up or down if you want to rearrange them.
Choose the right place for a field
Moving a field to Filters is not the only way to control what appears. The best location depends on whether readers need to see the categories as part of the report or use them as report-wide controls.
- Filters area: Use it for a report-wide selection, such as choosing one department while keeping the report layout compact. The selected value is displayed above the PivotTable.
- Rows or Columns area: Use these areas when categories should appear as labels in the report. Their drop-downs can hide selected labels without taking up a separate report-filter row.
- Values area: This area calculates measures such as Sum of Sales or Count of Orders. Numeric value filters can then limit the displayed row or column items based on those calculations.
For example, if a report compares salespeople, put Salesperson in Rows and Revenue in Values. To show only salespeople with at least $10,000 in revenue, use a value filter on the row labels. A report filter on Salesperson would instead let someone select particular names manually; it would not automatically identify everyone above the revenue threshold.
Filter row and column labels
Row and column fields commonly have drop-down arrows beside their labels. Open one to select or clear individual items. Depending on the field and layout, the menu can also include label filters, such as Begins With, Contains, or Equals, and date-specific options for date fields.
To filter a label by text, open the field’s drop-down, point to Label Filters, choose a condition, and enter the comparison text. For instance, a product field can be limited to labels that contain the word Cable. The filter applies to the labels in that field, not to every text value in the source workbook.
To filter by a calculated measure, open the row or column field’s drop-down, point to Value Filters, choose a condition such as Greater Than, and select the value field to compare. If a PivotTable has multiple value fields, choose the appropriate measure. A top-items filter can also display the highest or lowest items by a selected value, which is useful for finding leading products or customers.
Use multiple selections, slicers, and timelines
A report filter can include one item or several. Open its drop-down and select the items to retain. In many Excel menus, the Select Multiple Items option must be turned on before you can check several values. The exact control can vary with the field and Excel build; use the menu’s search box, when available, to find a value in a long list.
For a more visible control, select a cell in the PivotTable and choose PivotTable Analyze > Insert Slicer. Check the fields you want and select OK. A slicer appears as a panel of buttons; click a button to filter the PivotTable, and use its clear-filter icon to remove that slicer’s selection. Slicers are particularly helpful in dashboards because readers can see which choices are available and which are selected.
For a PivotTable built from date data, choose PivotTable Analyze > Insert Timeline when that command is available. Select a date field and use the timeline to narrow the displayed period. Timelines are designed for date fields and offer controls for changing the time scale, such as months or quarters. If the source dates are stored as text or contain invalid entries, Excel may not offer the date field as a usable timeline.
Slicers and timelines can control more than one PivotTable when those reports share a compatible PivotCache or data source. Select the slicer, open the Slicer tab, and choose Report Connections or PivotTable Connections, depending on the ribbon label in your Excel build. Check the PivotTables it should control. If a report is missing from the list, it may not share the required cache or connection.
Clear, change, or remove a filter
To change a report filter, open its drop-down and choose a different value or selection. To clear a filter, open the same menu and choose Clear Filter From followed by the field name, where that command is shown. For row and column filters, use the field’s drop-down and choose the clear-filter command. A funnel icon on a field button is a useful clue that a filter is active.
To remove a report filter entirely, click inside the PivotTable, open PivotTable Analyze > Field List, and drag the field out of the Filters area, or move it to another area. This removes the control from the report; it does not delete the field from the source data. To remove a slicer, select its border and press Delete. Removing a slicer does not delete source records or the PivotTable itself.
If the report has several filters and you are unsure why a category is missing, clear them one at a time and check the result. Also inspect filters on both row and column fields: a report can look incomplete even when its report-filter drop-down says All, because a separate label or value filter is still active.
Refresh the PivotTable and keep its source current
A filter can only offer values known to the PivotTable’s current source or cache. If you append new records to a normal cell range, the PivotTable’s source range may not include them. A useful approach is to convert the source data into an Excel Table before building the PivotTable: select a cell in the data and choose Insert > Table, making sure the header setting is correct. Tables generally expand as records are added beneath them.
After changing or adding source records, click inside the PivotTable and choose PivotTable Analyze > Refresh. To update all PivotTables in the workbook, use Data > Refresh All. Refreshing updates the PivotTable’s cached results; it is not the same as changing a filter. A refresh can bring new items into a filter list if the source includes them.
When records have been removed from the source, old item names can sometimes remain in a PivotTable filter list because Excel retains items in the PivotCache. The setting for retained items is available in the PivotTable Options dialog, on its Data tab, under the number of items to retain per field. Setting that option to None and refreshing can remove stale entries; the precise dialog layout may vary across Excel versions.
Common filtering problems and fixes
- The Filters area is missing: Click inside the PivotTable and choose PivotTable Analyze > Field List. The field list may be hidden, or its areas may be collapsed at the bottom of the pane.
- A new category is not in the drop-down: Confirm that the source range includes the new rows, then refresh. If the source is a fixed range, update the source through PivotTable Analyze > Change Data Source, or use an Excel Table as the source.
- A field cannot be used as a date field: Check the source column for text-formatted dates, blanks, or invalid values. Correct the source data and refresh. Dates represented as text may be treated as ordinary labels.
- Some expected rows disappear: Check report filters, label filters, value filters, and slicers. Also consider whether the source itself excludes those records or whether a refresh is needed.
- The filter affects one report but not another: The PivotTables may not share a compatible cache or connection. Check the slicer’s Report Connections or PivotTable Connections list.
Excel for Mac and Excel for the web
The basic method—show the field list and place a field in Filters—also applies in Excel for Mac. The contextual ribbon tabs and some dialog labels can differ by platform or update, so look for the PivotTable Analyze tab after selecting the report and use Field List to display the areas. On Mac, the ribbon may use a different arrangement, but the field-list areas serve the same purpose.
Excel for the web supports working with PivotTables, but available commands can differ from the Windows desktop app and may depend on the workbook’s features. If a particular filter, connection, or PivotTable option is unavailable in the browser, open the workbook in the desktop version of Excel. In all versions, the report-filter concept is the same: a field placed in the Filters area controls which source items contribute to the summary.
Frequently Asked Questions
Can I add more than one filter to an Excel PivotTable?
Yes. Drag multiple fields into the Filters area to create several report filters. You can also filter row and column labels and apply value filters. Each control can narrow the report further, so check all active filters when results seem unexpectedly limited.
How do I add a filter drop-down to a PivotTable?
Click the PivotTable, open PivotTable Analyze > Field List, and drag a field into Filters. Excel adds the field’s drop-down above the report. If you mean the arrows beside row or column labels, those are controlled by the field layout and field-header display.
Can I filter a PivotTable by a number or sales total?
Yes. Use a Value Filter on a row or column field and select the numeric measure, such as Sum of Sales, for the comparison. A report filter selects field items directly; it does not by itself apply a threshold to a calculated total.
Why does my PivotTable filter not show new data?
The PivotTable may need a refresh, or its source range may not include the new records. Choose PivotTable Analyze > Refresh, then check the source range. An Excel Table used as the source is often easier to maintain as records are added.
Does filtering a PivotTable change the original data?
No. A PivotTable filter changes which records are included in the displayed summary; it does not delete or edit the source rows. To permanently change the data, edit the source itself rather than relying on a PivotTable filter.
Related
- Excel Decimal Comma: Change Number Separators
- Excel Number Format to Hide Zeros Without Changing Values
- Excel Number Custom Format Codes: Examples and Tips
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Gantt Chart Project Plan: Build and Manage ItTo make an Excel Gantt chart project plan, list each task with a start date and duration, put…
- Excel KPI Dashboard Templates: How to Choose and UseExcel KPI dashboard templates are prebuilt workbooks that turn performance data into a compact view of key measures,…
- How to Import a Chart Template in Excel and Apply ItTo import a chart template in Excel for Microsoft 365 on Windows, copy its .crtx file into your…
- Can You Build an Org Chart in Excel? Steps & OptionsYes, you can build an organizational chart in Excel using SmartArt or by arranging shapes and connector lines.…
- Combo Waterfall Charts in Excel: How to Build OneYes, you can make a chart that combines a waterfall-style breakdown with another measure, such as a target…
- Excel Copy Chart Formatting to Another Chart QuicklyTo copy a chart’s formatting in Excel for Microsoft 365 on Windows, select the source chart and press…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.