'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Remove Old Items From Excel PivotTable Filters
Question details
The user needs to remove old names or blank entries that continue to appear in Excel PivotTable filter lists even after the source data has been deleted or updated.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Filtering data in an existing PivotTable after items have been removed from the original data source.
- Observed behavior
- Excel retains deleted source items in the cache, causing obsolete and deleted entries to still show up in the PivotTable filter drop-down menus.
Make sure you have completely updated and saved your original data source before attempting to refresh the PivotTable.
Change PivotTable Data Retention Settings
Adjust the PivotTable options to stop retaining deleted items from the data source, then refresh the table to clear the filter list.
By default, Excel retains deleted source items in the PivotTable cache so that custom formatting and calculated fields remain intact even if data is temporarily removed. Changing this setting to 'None' forces Excel to drop these old items from the cache completely.
Right-click anywhere inside your PivotTable and select 'PivotTable Options' from the context menu.
In the PivotTable Options dialog box, click on the 'Data' tab located at the top.
Find the section labeled 'Retain items deleted from the data source'. Click the drop-down menu next to 'Number of items to retain per field', select 'None', and then click 'OK'.
Right-click the PivotTable again and select 'Refresh' (or go to the PivotTable Analyze tab and click Refresh). This applies the new setting and clears the old filter items.
Easily Manage PivotTable Filters with WPS Office Spreadsheet
WPS Office Spreadsheet provides intuitive and powerful tools for data analysis, including full support for PivotTable creation, customization, and data cache management. You can effortlessly clear old items and refresh your data.
- 1. Open your file in WPS: Launch WPS Office Spreadsheet and open your existing .xlsx workbook.
- 2. Access PivotTable Options: Right-click the PivotTable, select 'Options', and go to the 'Data' tab.
- 3. Clear Old Items: Set the 'Number of items to retain per field' to 'None' and click OK.
- 4. Refresh Data: Right-click the table and click 'Refresh' to update your filters instantly.
Frequently Asked Questions
Why do deleted items still show up in my Excel PivotTable?
By default, Excel saves deleted items in the PivotTable cache to prevent issues if the data is temporarily removed or changed. This memory function causes old entries to appear in your filter drop-downs until the retention settings are modified.
Can I set the retention setting to 'None' for all future PivotTables automatically?
No, there isn't a built-in global setting to change the default behavior for all new PivotTables in Excel. You must change the 'Retain items deleted from the data source' setting to 'None' individually for each PivotTable, or use a VBA macro to apply it across multiple tables.
Does refreshing the PivotTable automatically remove old items?
No. Unless you first change the data retention setting in the PivotTable Options to 'None', merely refreshing the PivotTable will not clear the deleted or blank items from the cache.
Will changing data retention settings affect my original data?
No. The retention setting only affects the internal cache of the PivotTable itself, dictating whether it remembers items that no longer exist in the source. Your original data remains completely untouched.
