How to Remove Obsolete Items from an Excel PivotTable Cache
Question details
The user wants to permanently remove deleted or obsolete data items that still appear in an Excel PivotTable's drop-down filters after the original source data has been updated.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Updating an existing PivotTable after deleting specific rows or items from the original source data range.
- Observed behavior
- Deleted items remain visible in the PivotTable fields and filters because they are retained in the underlying PivotTable cache.
Ensure you have completely removed the unwanted data from your original source range or table before attempting to clear the PivotTable cache.
Change Data Options to Retain No Items
Modify the PivotTable settings so it no longer retains deleted items in its data cache, then refresh the table.
By default, Excel retains deleted items in the PivotTable cache. This prevents custom formatting and calculated fields from breaking when data temporarily changes. Changing the retention settings will force Excel to drop any items that no longer exist in the source data.
Click anywhere inside your existing PivotTable to activate the PivotTable Tools on the ribbon.
Go to the 'PivotTable Analyze' tab (or simply 'Options' in older versions) on the ribbon and click on the 'Options' button.
Navigate to the 'Data' tab within the PivotTable Options dialog box. Look for the 'Number of items to retain per field' setting and select 'None' from the drop-down menu.
Click 'OK' to save the settings. Right-click anywhere in the PivotTable and select 'Refresh' to apply the changes and permanently clear the obsolete items.
Easily Manage PivotTables in WPS Office
WPS Spreadsheet provides a highly compatible and intuitive interface for managing complex data. You can easily create, refresh, and configure PivotTables to clear obsolete data cache just like in Microsoft Excel.
- 1. Select the Table: Open your spreadsheet in WPS Office and click anywhere inside the PivotTable.
- 2. Access Options: Go to the 'PivotTable' tab on the top ribbon and click on 'Options'.
- 3. Modify Retention: Under the 'Data' tab, change 'Number of items to retain per field' to 'None' and click OK.
- 4. Refresh Data: Click 'Refresh' on the ribbon or right-click to refresh the table and clear the removed items from the cache.
Frequently Asked Questions
Why does Excel keep deleted items in the PivotTable filters?
Excel caches PivotTable data to optimize performance and preserve custom grouping or formatting. This ensures your layout doesn't break if an item temporarily disappears from the source data during an update.
Can I apply the 'Retain None' setting to all PivotTables at once?
The 'Number of items to retain per field' setting is specific to each PivotTable cache. You will need to change this setting for each independent PivotTable individually, unless you use a VBA macro to automate the process globally.
What if the deleted items still appear after changing the setting to None?
Ensure you have clicked 'Refresh' after changing the setting. If the PivotTable shares a cache with another table, you may need to click 'Refresh All' on the Data tab to ensure the entire workbook's cache is updated.
