Excel Pivot Table: Remove Old Filter Values After Refresh

To remove deleted items from an Excel PivotTable filter, open PivotTable Options, set Number of items to retain per field to None, and refresh the PivotTable. This clears obsolete labels stored in its cache; simply clearing the filter or refreshing without changing this setting may leave old values in the dropdown.

Why old values remain in a PivotTable

A PivotTable does not read its filter list directly from the visible cells every time you open a dropdown. For a standard PivotTable, Excel keeps a PivotCache containing data and field items used by the report. The cache can retain labels that were present in earlier source data, even after those labels are removed from the worksheet or replaced with new ones.

For example, suppose a sales table once included a region called West, but the source data has since been corrected and no longer contains that region. A PivotTable filter may still list West because the cache remembers it. Choosing a different filter or using Clear Filter changes the current selection; it does not necessarily discard remembered items.

This is usually a display and cache issue, not evidence that Excel has put the deleted records back into the source. However, if the old label still appears in the source range or an Excel table, the PivotTable will continue to show it after a refresh. Check the underlying data before changing cache settings.

Remove old items in Excel for Windows

Use the desktop version of Excel for Microsoft 365 on Windows for the following steps. They apply to a conventional PivotTable based on a worksheet range or Excel table:

Click a cell inside the PivotTable.
Right-click inside it and choose PivotTable Options.
Open the Data tab.
Under Number of items to retain per field, choose None.
Click OK.
Refresh the PivotTable by right-clicking it and choosing Refresh, or use PivotTable Analyze > Refresh.

After refresh, open the relevant field’s filter dropdown and check the item list. The deleted label should no longer be available as a retained item. If you have several old labels, this same setting removes retained items across fields in that PivotCache; it is not a one-item delete command.

If you do not see the option, confirm that the selection is inside the PivotTable and that you are using desktop Excel. Dialog labels and access can differ in Excel for Mac and Excel for the web. On Mac, look for PivotTable options in the PivotTable ribbon or the contextual menu; if the retention control is unavailable in your version, open the workbook in Excel for Windows to change it.

What the retention setting changes

Number of items to retain per field controls whether Excel keeps field items that are no longer present in the current source data. The default behavior can preserve items in the cache. Setting it to None tells Excel not to retain those obsolete items when the PivotTable is refreshed.

The timing matters: changing the setting alone may not update the visible list until the PivotTable is refreshed. A refresh alone may also leave old values in place if the cache is still configured to retain them. Set the option first, then refresh. If several reports use the same PivotCache, they can be affected by the cache’s retention behavior as well. Check related reports after making the change, especially if they rely on old categories for comparison.

Removing an item from the cache does not delete or edit source records. Nor does it change the fields, layout, or calculations in the PivotTable. It only affects which field items remain available from the cached data after refresh. If a label is still present in the source, it remains a legitimate current item and will continue to appear.

Refresh the right source and report

A refresh can only reflect the data range or connection that the PivotTable actually uses. If new rows were added below a fixed worksheet range, those rows may not be included. Converting the source range to an Excel table is often helpful because tables expand as rows are added. To check the source, select the PivotTable and use PivotTable Analyze > Change Data Source. Confirm that the range or table includes the current records, then refresh.

For a PivotTable connected to an external file, database, or other connection, refresh the connection or PivotTable as appropriate and confirm that the refreshed source no longer returns the old item. A cache cleanup cannot remove a label that the connected source still supplies. If the workbook uses several PivotTables, refresh the report or reports that display the stale value; refreshing one report is not a guarantee that every separate cache or connection in the workbook has been refreshed.

If the source data is correct but the item remains, try these checks in order:

Confirm the old label is absent from the source range or table, including rows that may be hidden or below the expected range.
Set retention to None in the PivotTable Options dialog.
Refresh the PivotTable and, for connected data, refresh the relevant connection.
Check whether another PivotTable uses a separate cache or source connection.
Reopen the filter dropdown after the refresh; a currently selected filter and the available-item list are different things.

Filter dropdowns, slicers, and selected items

People often mean one of two things by an old filter value: an obsolete label still listed among the choices, or an old filter selection still applied to the report. The retention setting addresses the first. To remove a current filter selection, use the field dropdown and choose Clear Filter for that field, or use the clear-filter command available for the selected report field. Clearing a selection does not purge cached labels.

Slicers can also display items that have no current data. First set the cache retention option to None and refresh. If you only need to hide unavailable entries in a slicer, right-click the slicer, choose Slicer Settings, and select Hide items with no data if that option is offered. This changes what the slicer displays; it is not a substitute for removing obsolete items from the cache. Slicers connected to multiple PivotTables may reflect their shared connections, so verify the reports they control.

A related distinction applies to row and column fields. A field can be configured to show items with no data, depending on the report and data model. That display behavior is not the same as cache retention. If a value has no current records but is intentionally kept for a reporting layout, hiding items with no data may be more suitable than removing retained items.

When the usual setting is unavailable

The instructions above are for a standard PivotTable using a worksheet range or table. PivotTables built from the Data Model, Power Pivot, or certain OLAP sources behave differently. Their item lists can be controlled by the model or external connection, and the standard Number of items to retain per field control may be absent or may not govern the list in the same way.

For those reports, refresh the underlying model or connection and check that the source dimension or query no longer contains the old member. If it is supplied by a database, the correction may need to be made in that source or in the query feeding the workbook. Avoid assuming that deleting a visible item from a worksheet will change a model-based PivotTable; the visible sheet may not be the source of the report.

Excel for the web can open and work with many PivotTables, but some creation and configuration options are more limited than in desktop Excel. If you cannot find the cache-retention control in the web interface, use the Windows desktop application to change it. Excel for Mac has its own ribbon and dialog presentation; menu names or availability can vary by release, so use the Windows steps when you need the exact control described here.

Common mistakes and practical cautions

Using Clear Filter as cache cleanup: this clears a selection, not the list of retained items.
Refreshing before changing retention: refresh the report after setting retention to None so Excel applies the cleanup.
Forgetting the source range: an old value that remains in the source is not obsolete to the PivotTable.
Expecting every PivotTable to update: reports can use separate caches, connections, or models.
Removing historical categories unintentionally: retaining an empty category can be useful for consistent period-to-period layouts. Consider whether the old item is truly unwanted before removing it.

For a recurring report, document the source and refresh procedure, and check the filter list after the source changes. If a report intentionally needs to show categories with no current transactions, retain or display them deliberately rather than treating every empty label as an error.

Frequently Asked Questions

Why do deleted items still appear in my PivotTable filter?
Excel may retain old field items in the PivotCache. Set Number of items to retain per field to None in PivotTable Options, then refresh. Also verify that the item is actually absent from the PivotTable’s source.

Does Clear Filter remove old values from the dropdown?
No. Clear Filter removes the active filter selection so the report can show the unfiltered data. It does not clear historical field items stored in the cache.

Will setting retention to None delete data from my worksheet?
No. It changes which obsolete items the PivotTable cache keeps after a refresh. It does not delete source rows or edit the original table.

Can I remove just one old item?
The retention setting applies to cached items by field rather than acting as a delete button for one chosen label. If the unwanted label is still in the source, correct the source; if it is only retained in the cache, set retention to None and refresh.

Why is Number of items to retain per field missing?
The control is intended for standard PivotTables and may not be available for a Data Model, Power Pivot, or OLAP-based report, or in some Excel interfaces. Refresh and correct the underlying model or connection, or use desktop Excel for the standard cache setting when applicable.

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.