How to Remove Old Filter Items from an Excel Pivot Table
Question details
The user wants to clear outdated or deleted data items that still appear in the filter dropdowns of an Excel Pivot Table.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Replacing or updating a Pivot Table's source data where previous data items are no longer present.
- Observed behavior
- Dates and other items from the previous data set remain visible in the Pivot Table filter list because the Pivot Table cache retains missing items by default.
Verify that your Pivot Table's source data has been successfully updated and contains only the current values you wish to display.
Change Missing-Item Retention Setting to None
Modify the Pivot Table options to stop retaining deleted items in the cache and then refresh the data.
Excel uses a background cache to optimize Pivot Table performance. By default, this cache retains items deleted from the data source, which causes old items to persist in your filter lists. Changing the data retention settings forces the Pivot Table to drop these outdated items upon the next refresh.
Right-click anywhere inside your Pivot Table and select 'PivotTable Options' from the context menu.
In the PivotTable Options dialog box, click on the 'Data' tab located at the top.
Locate the section labeled 'Retain items deleted from the data source'. Change the 'Number of items to retain per field' dropdown from 'Automatic' to 'None'.
Click 'OK' to save your changes. Finally, right-click the Pivot Table again and select 'Refresh' to apply the new cache settings and clear the old filter items.
Manage Pivot Tables Flawlessly in WPS Spreadsheet
WPS Spreadsheet provides powerful Pivot Table features, allowing you to easily analyze data, clear old filter caches, and refresh your data sources with seamless compatibility.
- 1. Open your file in WPS Spreadsheet: Launch WPS Office and open your workbook containing the Pivot Table.
- 2. Access PivotTable Options: Right-click the Pivot Table and choose 'PivotTable Options'.
- 3. Change Data Settings: Navigate to the Data tab, set 'Number of items to retain per field' to 'None', and click OK.
- 4. Refresh your Data: Right-click the Pivot Table and select 'Refresh' to update your filters instantly.
Frequently Asked Questions
Why do deleted items still show up in my Pivot Table filter?
Pivot Tables store data in a hidden area called the Pivot Cache to optimize performance. By default, this cache remembers old items even if they are deleted from the source data, resulting in them still appearing in your filters.
Does changing the retention setting to 'None' affect my current data?
No, changing this setting only prevents the Pivot Table from remembering items that no longer exist in your source data. Your current data and calculations will remain completely unaffected.
Can I apply this setting to all Pivot Tables in my workbook at once?
The setting must be applied to each Pivot Cache individually. If multiple Pivot Tables share the exact same cache, changing it for one and refreshing will update the others sharing that cache. Otherwise, you must adjust the setting for each independent Pivot Table.
How do I force a completely new Pivot Cache to clear all old data?
You can force a new cache generation by temporarily changing the data source range slightly (such as adding a blank row or column), refreshing the Pivot Table, and then changing the data source back to the original range.
