To clear the Pivot Table cache in Excel, go through the process below:
- Right-click on a cell of the Pivot Table and go to PivotTable Options.
- Now, go to the Data and in the Number of items to retain per field: select None, then click OK.
- Next, go to the PivotTable Options > Refresh.
If you remove any data from the source dataset, you have to update the Pivot Table as well. By following the above procedure, you will clear the cache memory in a Pivot Table in Excel.
What is Pivot Table Cache?
When you create a Pivot Table in Excel, it doesn’t directly interact with the source data. Instead, Excel creates a separate data structure called the PivotTable Cache. This cache contains a copy of the source data, and it is optimized for quick analysis and manipulation.
Here are some key points about the PivotTable Cache:
- Data Storage: The PivotTable Cache stores a copy of the source data, but it does not duplicate the entire dataset. It stores a summarized version of the data based on the fields and calculations used in the PivotTable.
- Performance: The use of the PivotTable Cache improves the performance of PivotTables. Instead of repeatedly querying the source data, Excel can quickly retrieve information from the cache, making the PivotTable more responsive, especially when dealing with large datasets.
- Offline Analysis: The PivotTable Cache allows you to perform analysis even when you’re offline or disconnected from the original data source. This is because the cache contains a snapshot of the data at the time the PivotTable was created or refreshed.
- Refresh: You can refresh the PivotTable to update the data in the cache with the latest information from the source. This is useful when the source data changes, and you want your PivotTable to reflect those changes.
- Multiple PivotTables: If you have multiple PivotTables based on the same data, they can share the same PivotTable Cache. This helps in saving memory and resources.
Understanding the PivotTable Cache is essential for efficiently working with PivotTables in Excel. It’s the mechanism that enables Excel to provide dynamic and flexible data analysis capabilities.
Clear Pivot Table Cache Using Refresh Button
To keep my sales report up-to-date, I need to modify the source data. However, before doing so, I must clear the cache memory of the Pivot Table. As a result, I have generated a Pivot Table based on the dataset.
Now, I will update the source dataset. Then, I will clear the Pivot Table cache in Excel.
First, I will delete the dataset. Then I will clear the Pivot Table cache in Excel by refreshing it. Here’s how:
- Select the range that you want to delete.
- Right-click on that and select the Delete option.
- Now, choose Shift cells up the Delete dialog box and click OK.
- Then, go to the Pivot Table and select a cell on that.
- Next, right-click on that cell and choose the Refresh button.
See, you have cleared the Pivot Table cache in Excel by using the Refresh button.
Clear Pivot Table Cache Using Pivot Table Options
You can use PivotTable Options to clear the Pivot Table cache in Excel. After deleting data from the source data, clear the cache in the Pivot Table in Excel using the steps below:
- Select a cell on the Pivot Table.
- Then, right-click on that and select PivotTable Options.
- Go to Data in PivotTable Options.
- Then, choose None in the Number of items to retain per field: and click OK.
- Next, select any cell on the Pivot Table and go to PivotTable Analyze > Refresh.
Or, use the shortcut key ALT+F5.
You will see the cleared cache after clicking on the Refresh button.
Clear Pivot Table Cache in Excel by Using VBA Code
- Right-click on the sheet’s name and choose View Code.
- Now, navigate to Insert > Module.
- Copy and paste the VBA code below.
Sub ClearPivotTableCache() Dim ws As Worksheet Dim pt As PivotTable ' Set the worksheet containing the pivot table Set ws = ThisWorkbook.Worksheets("ClearCache with VBA Code") ' Change "ClearCache with VBA Code" to the name of your worksheet ' Set the pivot table Set pt = ws.PivotTables("PivotTable5") ' Change "PivotTable5" to the name of your pivot table ' Clear the pivot table cache pt.PivotCache.MissingItemsLimit = xlMissingItemsNone ' Optional: Adjust cache settings pt.PivotCache.Refresh ' Optionally, refresh the pivot table to update it with the latest data pt.RefreshTable End Sub
- Now, go back to the worksheet.
- Then, press ALT+F8 to open the Macro dialog box.
- Next, click Run.
It will update the Pivot Table after clicking on Run.
To create a Pivot Table, there is source data. When you delete some data in the source data, you have to update the Pivot Table. So, you have to clear the Pivot Table cache in Excel. In this article, I have added 3 methods for Pivot Table cache clear in Excel. Here, I have included 2 ways of refreshing data and a VBA code for the Pivot Table cache clear. This will increase your Pivot Table performance as well. Hopefully, you can do it without any issues.
Frequently Asked Questions
Should I clear Excel cache?
Clearing the Excel cache can be beneficial in some situations. You should consider clearing the cache if you encounter issues like slow performance, data inconsistencies, or unexpected behavior in Excel. It can help resolve problems related to cached data. However, clearing the cache is not a routine maintenance task and should only be done when you’re facing specific issues. It won’t affect your Excel documents or settings, but it may improve Excel’s performance and reliability under certain circumstances.
Can I delete pivot cache in Excel?
No, you cannot directly delete PivotCache in Excel. PivotCache is automatically created and managed by Excel when you create a PivotTable. If you want to remove a PivotCache, you would need to delete the associated PivotTable(s) or the entire workbook. PivotCache is a background data storage mechanism used to improve PivotTable performance, and it’s not typically something users need to manage or delete directly.
How do I clear Excel cache?
To clear the cache in Excel:
- Close Excel and any other Office applications.
- Navigate to the Start menu and search for Run or, press Windows + R.
- Type %temp% and press ENTER.
- This opens the Temp folder, then delete all files and folders in this location.
This process clears the temporary files created by Excel and can help resolve various issues related to cached data and performance.