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.Pivot Table with source data to Pivot Table clear cache in Excel

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.
    Deleted the selected data from source data to Pivot Table clear cache
  3. Now, choose Shift cells up in the Delete dialog box and click OK.
    Shift cells up after deleting cells in Excel
  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.
    Clicked on Refresh to Pivot Table clear cache in Excel
    See, you have cleared the Pivot Table cache in Excel by using the Refresh button.
    Cleared Pivot Table Cache by using Refresh option in Pivot Table in Excel

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.
    Accessed PivotTable Options to Pivot Table clear cache in Excel
  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.
    Chose None in Number of items to retain per field to clear cache
  5. Next, select any cell on the Pivot Table and go to PivotTable Analyze tab > Refresh command.
    Or, use the shortcut key ALT+F5.
    Selected Refresh to Pivot Table clear cache with PivotTable Options
    You will see the cleared cache after clicking on the Refresh button.
    Cleared Pivot Table cache in Excel using PivotTable Options

Clear Pivot Table Cache in Excel by Using VBA Code

  1. Right-click on the sheet’s name and choose View Code command.
    Choose View Code to insert VBA code to Pivot Table clear cache
  2. Now, navigate to Insert tab > Module command.
    Insert Module to paste the VBA code to Pivot Table clear cache
  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
    ' Optionally, refresh the pivot table to update it with the latest data
    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.
    Run the VBA code from Macro to Pivot Table clear cache
    It will update the Pivot Table after clicking on Run.
    Cleared Pivot Table cache using VBA code in Excel


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.

5/5 - (2 votes)

Leave a Reply

Your email address will not be published. Required fields are marked *