How to Clear Pivot Table Cache in Excel [3 Methods]

To clear the Pivot Table cache in Excel, go through the process below:

1. Right-click on a cell of the Pivot Table and go to the PivotTable Options command.
2. Now, go to the Data tab, and in the “Number of items to retain per field:“, select None, then click OK.
3. Next, right-click on the cell in the Pivot Table and select the Refresh command.

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:

1. Select the range that you want to delete.
2. Right-click on that and select the Delete command.
   
3. Now, choose Shift cells up in the Delete dialog box and click OK.
   
4. Then, go to the Pivot Table and select a cell on that.
5. 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 tool 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:

1. Select a cell on the Pivot Table.
2. Then, right-click on that and select PivotTable Options command.
   
3. Go to Data tab in the PivotTable Options dialog box.
4. Then, choose None in the “Number of items to retain per field:” and click OK.
   
5. Next, select any cell on the Pivot Table and go to PivotTable Analyze tab > Refresh command.
   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

1. Right-click on the sheet’s name and choose View Code command.
   
2. Now, navigate to Insert tab > Module command.
   
3.  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

4. Now, go back to the worksheet.
5. Then, press ALT+F8 to open the Macro dialog box.
6. Next, click Run command.
   
   It will update the Pivot Table after clicking on Run.
   
   

Conclusion

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:

1. Close Excel and any other Office applications.
2. Navigate to the Start menu and search for Run or, press Windows + R.
3. Type %temp% and press ENTER.
4. 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.