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.

How to Clear Pivot Cache in Excel: 3 Effective Ways

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 the 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.

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.

• 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.

• 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.

• 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.

• 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.

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

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

Thus, we can clear Pivot Cache 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:

• To begin with, insert the PivotTable like the previous method.

• 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.

• 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.

• 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

• Next, press F5 to run the code.

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

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 first 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.
• However, 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.

Download Practice Workbook

You can download the practice workbook here..

Conclusion

In this article, we have talked about 3 ways to clear Pivot Cache in Excel. These methods will allow the 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. Keep learning new Excel features.