'%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 Fix Excel PivotTable Dates and Categories Not Refreshing
Question details
The user is unable to clear obsolete dates and categories from an Excel PivotTable using the 'Refresh All' function after copying a workbook.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Attempting to update PivotTable data after copying a workbook using the Save As feature.
- Observed behavior
- The PivotTable retains old dates and categories in its pivot cache, and clicking 'Refresh All' fails to remove these obsolete items.
Before modifying your PivotTable cache settings, ensure that your data source range is correctly defined and does not contain any unintended blank columns or rows.
Clear Retained Items from PivotTable Options
Adjusting the PivotTable options to prevent Excel from saving deleted items in the cache is the most effective way to clear old dates and categories.
By default, Excel retains items deleted from the data source in the pivot cache. This is done so that custom formatting and calculated fields aren't lost when data changes. To permanently remove obsolete categories, you must change this behavior in the settings.
Right-click anywhere inside the affected PivotTable 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 'Retain items deleted from the data source' section. Click the drop-down menu next to 'Number of items to retain per field' and change it from 'Automatic' to 'None'.
Click 'OK' to save the settings. Finally, right-click the PivotTable again and select 'Refresh' to clear the old dates and categories.
Verify and Update the PivotTable Source Range
Sometimes a copied workbook retains the absolute file path to the original workbook's data range, meaning your PivotTable is looking at the wrong data entirely.
Create and Refresh Pivot Tables Easily in WPS Spreadsheet
WPS Spreadsheet provides a seamless and user-friendly experience for analyzing data with Pivot Tables. You can easily refresh data, manage source ranges, and avoid frustrating cache issues, all while maintaining perfect compatibility with your existing Excel files.
- 1. Open Your Workbook: Launch WPS Spreadsheet and open your existing .xlsx workbook.
- 2. Access PivotTable Tools: Click on your PivotTable to activate the dedicated 'PivotTable Tools' tab on the top ribbon.
- 3. Adjust Cache Settings: Click on 'Options', navigate to the Data tab, and ensure item retention is set to 'None'.
- 4. Refresh Instantly: Click the 'Refresh' button on the ribbon to instantly update all dates and categories.
Frequently Asked Questions
Why does my Excel PivotTable still show deleted data?
By default, Excel retains items deleted from the data source in the pivot cache. This prevents the loss of custom formatting and calculated fields associated with those items. To remove them, you must change the 'Number of items to retain per field' setting to 'None' in the PivotTable Options.
Does 'Refresh All' update the pivot cache?
'Refresh All' updates the values from the source data, but it does not automatically purge old categories from the background cache if the retention setting is set to 'Automatic'. You must manually clear the cache settings first for the refresh to drop obsolete items.
How do I clear the PivotTable cache on Excel for Mac?
The process on Mac is identical to Windows. Right-click the PivotTable, select 'PivotTable Options', navigate to the 'Data' tab, change the 'Retain items deleted from the data source' drop-down to 'None', and then refresh your PivotTable.
