Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Clear Pivot Cache in Excel (3 Effective Ways)

In this article, we will discuss how to clear Pivot Cache in Excel. Pivot Cache is a replica of the source data that Excel creates as soon as the PivotTable is inserted. When the user makes any changes to the data source the Pivot Cache does not change automatically. However, the PivotTable inserts data from the Pivot Cache. So, the PivotTable gets updated once the Pivot Cache gets updated. That is why it is very important to clear old data from Pivot Cache in order to get the same data as the source in the PivotTable.


Download Practice Workbook

You can download the practice workbook here..


3 Ways to Clear Pivot Cache in Excel

In this article, we will discuss 3 methods to clear Pivot Cache in Excel. Firstly, we will use the PivotTable Options. Secondly, we will resort to a VBA Code to clear Pivot Cache in Excel. Finally, we will use the Refresh option to get the job done. Here, is the sample dataset that we will use to demonstrate the methods.

excel clear pivot cache


1. Using PivotTable Options

In this method, we will apply the PivotTable Options to clear the Pivot Cache in Excel. We will delete some of the data from the source data table after we insert the PivotTable. Then, we will clear the cache using the PivotTable Options.

Steps:

  • Firstly, select the B5:D12 cell range.
  • Secondly, go to the Insert tab.
  • Thirdly, choose the PivotTable option.
  • Finally, from the drop-down options, choose From Table/Range.
  • As a result, a prompt will be on the screen.

inserting pivottable to clear pivot cache in excel

  • In the prompt, first, choose the B4:D12 cell range as the Table/Range.
  • Secondly, check the Existing Worksheet oval.
  • Thirdly, choose the F4 cell as the Location for the PivotTable.
  • Finally, click OK.
  • As a result, the PivotTable Fields will appear to the right.

  • Now, drag the Department, Employee, and Employee ID fields under the Columns, Rows, and Values options respectively.

setting up pivottable fields to clear pivot cache in excel

  • As a result, we will get the PivotTable.

 

  • Now, choose the B11:D12 cell range and right-click.
  • Then, from the available options, choose Delete.
  • As a result, a prompt will be on the screen.

deleting cells to clear pivot cache in excel

  • Next, choose the Shift cells up option and click OK.

  • Consequently, the cells will be deleted from the data source.
  • However, they are still in the PivotTable because of the PivotTable Cache.

  • Thereafter, choose any of the cells on the PivotTable and right-click.
  • From the available options, choose PivotTable Options.
  • As a result, we will find a prompt on the screen.

going to pivot options to clear pivot cache in excel

  • In the prompt, first, go to the Data tab.
  • Then, from the Number of items to retain per field option, choose None.
  • Next, click OK.

setting up pivot options to clear pivot cache in excel

  • Now, again right-click on one of the data in the PivotTable.
  • From the prevailing options, select Refresh.

clicking refresh to clear pivot cache in excel

  • As a result, the PivotTable will be updated according to the data source.

using pivot options to clear pivot cache in excel

Thus, we can clear Pivot Cache in Excel.

Read More: How to Clear Pivot Table Cache Using VBA in Excel


2. Applying Excel VBA to Clear Pivot Cache

In this instance, we will use VBA to clear the Pivot Cache. The code will update the PivotTable as we delete some of the data from the dataset.

Steps:

  • After that, select the B11:D12 cell range and right-click.
  • Next, from the prevailing options, choose Delete.
  • Consequently, a prompt will be on the screen.

deleting set of cells to clear pivot cache in excel

  • Thereafter, select the Shift cells up option and click OK.

  • As a result, the cells will be deleted from the data source.
  • However, the PivotTable data will remain the same.

  • Now, go to the Developer tab.
  • From there, choose Visual Basic.
  • As a result, a new window will be opened.

opening developer tab to clear pivot cache in excel

  • In the Visual Basic window, choose ThisWorkbook under the VBAProject option.
  • As a result, a coding module will appear.

  • In the module, write the following code and save it by typing Ctrl+S.
Private Sub PivotTableCache()
Dim am_pt As PivotTable
Dim am_ws As Worksheet
Dim am_pc As PivotCache
Application.ScreenUpdating = False
For Each am_ws In ActiveWorkbook.Worksheets
For Each am_pt In am_ws.PivotTables
am_pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
Next am_pt
Next am_ws
For Each am_pc In ActiveWorkbook.PivotCaches
On Error Resume Next
am_pc.Refresh
Next am_pc
Application.ScreenUpdating = True
End Sub

writing codes to clear pivot cache in excel

  • Next, press F5 to run the code.

running codes to clear pivot cache in excel

  • Consequently, the command will clear all the Pivot Cache, and also it will update PivotTable accordingly.

applying vba code to clear pivot cache in excel

Read More: How to Clear Excel Cache Using VBA (4 Easy Methods)


3. Using Refresh Command

In this final method, we will explore the Refresh command to clear Pivot Cache in Excel. We will insert a PivotTable and then make some changes to it. Then, we will use the Refresh option to update the table.

Steps:

  • To start with, insert a PivotTable by using the data in the B5:D12 range like the previous method.

  • Thereafter, choose the B11:D12 cell range and right-click.
  • Now, from the prevailing options, choose Delete.
  • As a result, a prompt will be on the screen.

  • Next, select the Shift cells up option and click OK.

  • Consequently, the cells will be deleted from the data source.
  • But the data in the PivotTable will remain the same.

  • After that, click on any of the data cells of the PivotTable and right-click.
  • Then, choose Refresh from the available options.

  • As a result, the command will terminate all the deleted data from the PivotTable.

using refresh option to clear pivot cache in excel


Conclusion

In this article, we have talked about 3 ways to clear Pivot Cache in Excel. These methods will allow user to update their PivotTable effectively and keep the table relevant. If you have any questions regarding this essay, feel free to let us know in the comments. Also, if you want to see more Excel content like this, please visit our website Exceldemy.Com and unlock a great resource for Excel-related content.


Related Articles

Adnan Masruf

Adnan Masruf

I am an engineering graduate. I graduated from Bangladesh University of Engineering and Technology(BUET), one of the top universities in Bangladesh from department of Naval Architecture & Marine Engineering with a major in structure. I am an avid reader of fiction especially fantasy. I also keep myself abreast of the recent developments in science and technology. I believe diligence will eventually pay off and luck tends to favor those who work hard.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo