To clear old values from an Excel PivotTable, open PivotTable Options, choose the Data tab, set Number of items to retain per field to None, and refresh the PivotTable. This removes obsolete field labels from its retained cache; it does not delete records from your source data.
What “old values” means in a PivotTable
A PivotTable can keep a list of labels it has encountered before, even after those labels disappear from the current source range. For example, a sales report may once have included a product called “Desk Lamp.” If the source data is later changed so that no rows contain Desk Lamp, the old label may still appear in a filter dropdown or field list. This is usually a retained item in the PivotTable cache, not evidence that the deleted sale is still included in the report.
Excel keeps cached information so it can build and update a PivotTable efficiently. One part of that information is the set of items previously seen in each field. Depending on the setting and the type of data source, Excel may continue showing an item that is no longer present in the latest source data. Refreshing alone does not always discard those remembered labels.
“Clear old values” can also mean something else: a person may want to remove displayed numbers, clear a filter, or reset the whole PivotTable. Those are different actions. The retained-item setting is the right fix when the problem is an obsolete label that remains available in a field’s dropdown.
Remove stale items in Excel for Microsoft 365 on Windows
Use the desktop version of Excel for this setting. The steps below apply to a regular PivotTable in Excel for Microsoft 365 on Windows:
- Click any cell inside the PivotTable.
- Open PivotTable Analyze on the ribbon, then choose Options. You can also right-click a PivotTable cell and choose PivotTable Options.
- In the PivotTable Options dialog, open the Data tab.
- Under Retain items deleted from the data source, set Number of items to retain per field to None.
- Select OK, then refresh the PivotTable. Use PivotTable Analyze > Refresh, or right-click inside the PivotTable and select Refresh.
After the refresh, open the relevant field’s filter dropdown again. The obsolete label should no longer be listed if it is truly absent from the refreshed source. If it remains, check the source data and the troubleshooting section below before concluding that Excel has ignored the setting.
On a Mac, select the PivotTable and open its PivotTable Options dialog, then look on the Data tab for the retained-items setting; the ribbon placement and wording can vary between Excel releases. Excel for the web has fewer PivotTable configuration controls. If you cannot find this setting in the browser, open the workbook in the desktop app, change the option, and refresh there.
What the “None” setting changes
The setting controls whether Excel retains field items that have been removed from the source data. Choosing None tells Excel not to keep those deleted items in the PivotTable’s retained-item list. A refresh then rebuilds the PivotTable using the current source and updates the available items. It is a cache-cleanup setting, not a command to erase the PivotTable or its underlying records.
The option is called “Number of items to retain per field” because it governs retained items for fields, rather than giving you a separate cleanup button for one label. Setting it to None is useful when old categories, names, or dates are cluttering dropdowns. It can also prevent a workbook from carrying forward unnecessary historical labels after data has been replaced.
It does not remove an item that still occurs anywhere in the source. If even one source row contains “Desk Lamp,” the label is valid and will remain after a refresh. Nor does the option change how the PivotTable groups, sums, or filters current records. Its purpose is to stop Excel from preserving items that no longer exist in the source.
Check the source range before refreshing
A refresh can only update a PivotTable from the data it is connected to. If new rows or changed records sit outside a fixed source range, the PivotTable may not be reading the data you expect. Likewise, if a supposedly deleted label remains in a hidden row, a second worksheet, or an included source column, Excel will correctly keep it.
To check the connection, click the PivotTable and choose PivotTable Analyze > Change Data Source. Review the range or table name shown in the dialog. If the source is an Excel Table, added rows are generally incorporated into the table as it expands, making it a convenient source for regularly updated reports. With a fixed cell range, update the range if the source has grown or moved, then refresh.
For a query or external connection, update the connection’s data first when needed, then refresh the PivotTable. A PivotTable refresh cannot remove an item that the query still returns. Also distinguish a blank from a deleted record: a source row with a blank category may produce a blank item, while a row containing the old category keeps that category available.
Clear cached items versus clearing the PivotTable
Excel has several commands with similar-sounding effects, but they solve different problems:
- Refresh updates the PivotTable from its source. It may not discard old labels unless the retained-item setting is set to None.
- Clear Filter removes a filter selection or field filter. It does not erase cached items.
- Remove a field takes that field out of the report layout. It does not remove the field from the source data or necessarily purge its cached items.
- PivotTable Analyze > Clear > Clear All resets the PivotTable’s layout and applied formatting. It is not the usual way to clean stale labels, and it does not delete the source data.
If the unwanted value is actually displayed in the report because a source row still exists, clearing the cache is not the right remedy. Correct or filter the source data, or apply a suitable PivotTable filter. If the issue is merely that a particular filter selection is active, clear that filter instead of changing the retained-items option.
Shared PivotTables and different data sources
PivotTables built from the same source may share a PivotCache. That means cache-related changes can affect more than one report, and a refresh of one PivotTable may have implications for other PivotTables using that cache. If several reports share the same source, inspect their field dropdowns after changing the setting and refreshing. Confirm that each report still has the layout and filters its users need.
Some PivotTables use the Excel Data Model, OLAP connections, or other external sources rather than a standard worksheet-range cache. Their available options can differ, and the retained-items control may be unavailable or behave differently. If the Data tab does not show the setting, do not assume that clearing the worksheet or rebuilding the report is necessary. Check the source type and its refresh controls; the data model or external system may manage the member list.
When a workbook contains many PivotTables, use Data > Refresh All only when you intend to refresh the workbook’s connections and reports, not just one table. Refresh All can take longer and may update other connected data. For a targeted cleanup, refresh the affected PivotTable first and verify the result.
Common reasons old values remain
If an obsolete label is still visible after setting the option to None and refreshing, work through these checks:
- Search the source: Find the label in the source column, including rows that are hidden or below the area you normally inspect.
- Confirm the source range: Make sure the PivotTable includes the current data and does not point to an older range or worksheet.
- Refresh the right object: Refresh the affected PivotTable after changing the option. If the source is a query, refresh that data connection as appropriate first.
- Check the source type: A Data Model or OLAP PivotTable may not expose the same retained-items control as a standard range-based PivotTable.
- Look for another field or report: A similar-looking label may be present in a different field, or another PivotTable may be using a separate cache.
Also check whether the label is part of a manually created group. Grouped dates or categories can make the displayed item look like source data even when it is a generated group label. Ungrouping or revising the grouping may be necessary; changing retained items alone does not redefine a group.
Good practices for keeping PivotTables tidy
Use an Excel Table as the source when rows are added regularly, and give the table and its columns clear names. This reduces the chance of refreshing a PivotTable against an outdated fixed range. Keep a known-good copy of important workbooks before changing shared reports, especially when several PivotTables depend on one cache or data model.
For routine reporting, decide whether users need old labels to remain available. Retaining items can be useful when reports are temporarily refreshed against partial data and historical filter choices matter. Setting the option to None is more suitable when the source is routinely replaced and old categories should disappear. In either case, validate the source first: the cache setting cannot correct inaccurate or incomplete source data.
Frequently Asked Questions
Why does my Excel PivotTable show items that were deleted?
A PivotTable can retain labels from earlier source data in its cache. Set Number of items to retain per field to None in PivotTable Options on the Data tab, then refresh. Also confirm the label is absent from the current source range.
Will clearing old PivotTable items delete my source data?
No. Setting retained items to None and refreshing changes the PivotTable’s cached item list. It does not delete worksheet rows, table records, or data in an external source.
Does Refresh All remove old PivotTable values?
Not necessarily. Refresh updates the report from its source, but Excel may retain deleted items according to the retained-items setting. Set that option to None, then refresh the affected PivotTable.
Can I remove just one old item from a PivotTable cache?
The standard retained-items setting is applied to fields, rather than acting as a one-label deletion tool. If an item still appears, verify that it is absent from all source rows. If it is absent, setting retention to None and refreshing is the usual cleanup method.
Why can’t I see “Number of items to retain per field”?
The workbook may be open in Excel for the web, or the PivotTable may use the Data Model, OLAP, or another source with different options. Try the desktop Excel app and check the PivotTable’s source type if the setting is still unavailable.
Related
- Excel Data Validation Guidelines for Reliable Spreadsheets
- Excel Data Validation From Another Workbook: Best Methods
- Data Validation From a Table in Excel: Setup and Fixes
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Pivot Chart: How to Remove ButtonsTo remove the buttons from an Excel PivotChart in Microsoft 365 for Windows, select the chart, open the…
- Excel Line Chart With X and Y Values: Setup and TipsTo make an Excel line chart, put your x values in one column and the matching y values…
- Excel Line Chart Not Plotting Correctly? Fix the DataIf an Excel line chart is missing points, plotting the wrong values, or showing an unexpected axis, first…
- Excel Average for Every 5-Unit X-Axis RangeTo find the average Y value in each 5-unit X-axis range in Excel, put the X values in…
- Excel Cumulative Line Graph: How to Create OneTo make a cumulative line graph in Excel, add a running-total column beside your values, then chart the…
- Excel Pivot Table Repeat Row Labels: How to Do It EasilyTo repeat row labels in an Excel PivotTable, select the PivotTable, choose PivotTable Design > Report Layout >…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.