If an Excel PivotTable filter still lists values that have been removed from the source, refresh the PivotTable and set its cache to retain no deleted items: right-click the PivotTable, choose PivotTable Options, open Data, set Number of items to retain per field to None, click OK, then refresh. If the unwanted values remain, check that the source range includes the current data and that the values are not still present in the source.
What “old values” in a PivotTable filter means
A PivotTable filter can show a category that no longer appears in the records you expect it to summarize. For example, a sales report may still offer “North” in a Region filter after the North region’s rows were deleted. Selecting that category may produce a blank result or no visible records. This is often a saved-item issue, but it is not the only explanation.
Excel builds a PivotTable from a source and stores information about its fields and items in a PivotCache. Depending on the PivotTable’s settings, the cache can preserve labels that were seen in earlier data, even after those labels disappear from the current source. That can make a filter list look out of date while the worksheet data itself is correct.
First distinguish a stale filter choice from stale numbers in the report. If the filter menu contains an old label but the totals are current, investigate retained items. If the totals themselves are wrong, refresh the data connection and verify the source range, calculations, and filters. A refresh cannot fix a PivotTable that is still pointed at the wrong records.
Clear deleted items from the PivotCache
In Excel for Microsoft 365 on Windows, use these steps for a standard PivotTable:
- Right-click a cell inside the affected PivotTable and select PivotTable Options.
- In the PivotTable Options dialog, select the Data tab.
- Under Retain items deleted from the data source, change Number of items to retain per field to None.
- Select OK, then right-click the PivotTable and choose Refresh. You can also use PivotTable Analyze > Refresh while the PivotTable is selected.
The setting tells Excel not to keep deleted field items in that PivotTable’s cache after an update. In some workbooks, the old item list disappears only after you refresh following the setting change. If the field is still in the source data, Excel will continue to offer it; this setting does not delete source values or conceal valid categories.
On Excel for Mac, select the PivotTable and use the refresh command on the PivotTable Analyze tab or the context menu. Dialog labels and their location can vary by release. If the Mac version offers the same retain-items option in PivotTable Options, set it to None and refresh. Excel for the web has fewer PivotTable configuration controls; if the retention setting is unavailable, open the workbook in the desktop app to change it.
Refresh the PivotTable and its data source
A PivotTable does not necessarily update the instant you edit its source cells. Refreshing makes Excel reread the source for that PivotTable; it does not automatically guarantee that Excel is reading every row you intended. Select the PivotTable and choose PivotTable Analyze > Refresh. To refresh workbook connections and multiple PivotTables, choose Data > Refresh All.
For a PivotTable based on an external connection, Power Query output, or another workbook, the source may need to refresh before the PivotTable can show the latest records. Use Data > Refresh All, and check any connection prompts or errors. If the source query has not returned updated data, refreshing only the PivotTable will not bring in records that are not yet available to it.
To check the source, click in the PivotTable and choose PivotTable Analyze > Change Data Source. Review the displayed range or table name. A fixed range such as Sheet1!$A$1:$D$500 will not include new records entered in row 501 or later. A formatted Excel Table is usually more dependable for a growing list: add records directly below it, confirm they become part of the table, and then refresh the PivotTable.
Check whether the source still contains the value
Before changing cache settings repeatedly, search the source. A category can look deleted from the visible report but still exist in a row outside the range you reviewed, on a hidden worksheet, or in a different part of the source table. Filters applied to the source can also hide records without removing them. If even one source row still contains “North,” that value is not a deleted item from Excel’s perspective.
Look for near-duplicates as well. “North,” “North ” with a trailing space, and a value containing a nonbreaking space can display almost identically while being separate text items. Likewise, a number stored as text may appear similar to a numeric value but behave as a different category. Correct the source data, refresh, and inspect the filter list again. If you clean values with formulas, make sure the PivotTable is based on the cleaned field rather than the original one.
For formula-driven source columns, confirm that the formulas have recalculated before refreshing the PivotTable. In a workbook using manual calculation, a formula can still display an earlier result until calculation occurs. Recalculate the workbook, then refresh. This is different from cache retention: the cache may be accurately reflecting an old formula result because that is what the source cell currently contains.
Other reasons a filter can show unexpected items
Not every extra label is a retained deleted item. Check these causes before assuming the PivotCache is at fault:
- Items with no data are being displayed. A field may be configured to show items even when the current report has no matching records. Right-click a field label and inspect Field Settings > Layout & Print for Show items with no data, when available. Turn it off if empty categories should not appear.
- The PivotTable has not been refreshed. The report can reflect an earlier source state. Refresh after editing or importing records.
- The source range is too small or points elsewhere. The PivotTable may include old records in a fixed range, or exclude new records added outside it. Check Change Data Source.
- A filter or slicer is confusing the view. A report filter, label filter, value filter, or slicer can restrict what appears in the report without changing which items are available in another filter menu. Clear relevant filters temporarily to diagnose the result.
- Date grouping or source formatting is involved. Grouped dates can show years, quarters, or months derived from dates in the source. A label that looks like an old date category may be part of a grouping rather than a leftover text value.
These checks matter because clearing retained items will not repair an incorrectly defined source, remove a category that still exists, or correct a filter selection. Diagnose the data and the report separately.
What the retention setting changes—and what it does not
Number of items to retain per field controls how Excel handles items that were present in a field but have since disappeared from the source. Choosing None is useful for operational reports where filter lists should match current categories. It is not a command to delete anything from the worksheet, and it does not remove records from the source or alter the meaning of valid items.
Changing the setting can affect other PivotTables that share the same PivotCache. If several reports use the same source and cache, refreshes or cache-related changes may influence what those reports retain. Review the workbook’s other PivotTables after changing the option, especially if one report intentionally needs to preserve historical filter choices. Consider saving a copy before making broad changes to a complex workbook.
Some PivotTables connected to the Data Model or an OLAP source do not expose the same cache-retention controls as a conventional range-based PivotTable. In that case, refresh the underlying model or connection and verify the source dimension members. The old member may be coming from the model or external system, so changing a standard PivotCache option may not be possible or relevant.
Prevent stale filter lists in future reports
For regularly updated reports, make the source and refresh routine predictable. Store source records in an Excel Table, keep one consistent field for each category, and avoid including large blocks of unused rows in a fixed source range. When a report is refreshed from a connection, confirm that the connection completes before checking its PivotTables.
You can also review refresh behavior in the PivotTable Options dialog’s Data tab. If the workbook should update when opened, use the available Refresh data when opening the file option for that PivotTable, where applicable. This can help keep a report current, but it does not fix a broken connection or expand a fixed source range. Periodically check that the source table, query, or model still contains the intended records.
When a category is intentionally retired but must remain available for historical reporting, do not remove it from the underlying data without considering the report design. A separate status field, archive table, or date-based filter may be a clearer way to distinguish current categories from historical ones. The right approach depends on whether the report is meant to show only current choices or to preserve an audit trail.
Frequently Asked Questions
Why does my PivotTable filter show items that are no longer in the source?
Excel may retain previously used field items in the PivotCache. Set Number of items to retain per field to None in PivotTable Options, then refresh. Also confirm that the value does not remain elsewhere in the actual source.
Will refreshing a PivotTable remove old filter values?
It can update the report, but retained items may remain unless the retention setting is changed. Set the option to None and refresh. If the value still exists in the source or an external model, refresh will continue to show it.
Why does the old value still appear after I set retention to None?
Check that you refreshed after changing the option, that the source contains no matching value or near-duplicate, and that the PivotTable uses the expected source. A Data Model or OLAP PivotTable may use different controls, and an item-with-no-data setting can also keep categories visible.
Will setting retained items to None change my source data?
No. It changes what the PivotTable cache keeps for deleted field items; it does not delete or rewrite worksheet records. Still, review other PivotTables that may share the cache, particularly if they rely on historical item lists.
Why are new source rows missing from my PivotTable?
The rows may fall outside a fixed source range. Check PivotTable Analyze > Change Data Source. Using an Excel Table as the source helps it expand as records are added, but the PivotTable still needs to be refreshed.
Related
- Excel Keyboard Shortcuts Mouse Pad: Guide to Choosing
- Excel Keyboard Shortcut for Next Sheet: Ctrl+Page Down
- Excel Keyboard Shortcuts to Find the End of Your Data
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Pivot Table Error: Causes and FixesIf Excel shows an error while creating a PivotTable, first check that the source data is one continuous…
- Excel Line Chart Tutorial: Create and Format OneTo make a line chart in Excel for Microsoft 365 on Windows, arrange your categories in one column…
- Excel Gantt Chart Template: Create and CustomizeAn Excel Gantt chart template lays out project tasks against dates, using horizontal bars to show when each…
- Excel Pivot Table: How to Hide Rows With Zero ValuesTo hide rows whose PivotTable result is zero in Excel for Microsoft 365 on Windows, open the drop-down…
- Excel Bar Chart Reverse Order: Steps, Options, and FixesYes. Select the bar chart’s category axis, open Format Axis, and under Axis Options turn on Categories in…
- 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.…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.