When we edit the source data of a Pivot Table, it will not automatically update in our Pivot Table.
When we create a Pivot Table, Excel creates a replica of the source data. We call this the Pivot Cache. Even though you can’t see it, it’s a component of the Pivot Table.
When we make updates to the Pivot Table, it will use the Pivot cache instead of the original data source. Because of this, pivot tables operate very quickly. As a result, it can quickly summarize all of the data, even if it has thousands of rows.
When we need to update the Pivot Table based on changed source data, we have to clear the Pivot Table cache.
Clearing the Pivot Table cache is not the same as refreshing a Pivot Table. When you refresh a Pivot Table, Excel will update the Pivot Table based on updated data in your source data. But, when you clear the cache of the Pivot Table, Excel will rebuild your Pivot Table from scratch.
In this article, I will show you different methods you can use to clear Pivot Table cache, as well as useful tips to refresh Pivot Tables after clearing the Pivot Table cache.
Method 1 – Use the Pivot Table Options to Clear Pivot Table Cache
We can use the Excel Pivot Table Options to clear the Pivot Table cache.
From the source data on the left side of the below image, I constructed the pivot table on the right.
Later on, I removed Ben’s name from the list. Since he was the only representative of Australia, the Pivot Table should, in theory, update Australia’s count to zero, or Australia should be deleted.
But you can see that my pivot table still lists Australia and that its count remains at 1.
The reason for this is that Pivot Table refers to the Pivot Cache.
Now, in order to update the pivot table, we need to clear the pivot cache.
To clear the cache in your Pivot Table, use the following method:
- Select any cell within your Pivot Table. In this case, I have selected cell D3. But you can select any cell in the D1 to E5 range.
- Right-click and select “PivotTable Options.”
- In the “Pivot Table Options” Dialog box, go to the Data tab.
- In the dropdown for “Number of items to retain per field,” select “None.”
- Click “OK” and don’t forget to refresh the Pivot Table.
This will clear the cache and prevent old items from being displayed in your Pivot Table fields.
To refresh your Data set you can use any of the below methods.
Method 1 – Use the Pivot Table Options to Refresh Pivot Table
- Select any cell on our Pivot Table to activate the “PivotTable Analyze” tab.
- Click the “Refresh” Button on the “Data” group of the “PivotTable Analyze” tab.
As soon as we click on the “Refresh” button, Pivot Cache will clear. Then, Excel will update the Pivot Table based on our source data.
Now, in this example, after we click the “Refresh” button, Excel will remove Australia from the Pivot Table.
We have another method by which we can refresh and clear the pivot cache of our pivot table.
First, right-click the pivot table and choose “Refresh” from the context menu.
Also read: How to Change Data Source in Pivot Table
Method 2 – Use the Excel “Refresh” Shortcut to Refresh the Pivot Table
We sometimes like to use Excel shortcuts instead of going to tabs and clicking on icons. We can refresh the Pivot Table using an Excel shortcut too.
I created the Pivot Table on the right side of the image below using the source data on the left side.
Later, I removed Ben’s name from the list.
The Pivot Table needs to, in theory, update Australia’s count to zero or remove Australia because Ben was the only person from Australia.
But as you can see, Australia is still shown in my pivot table with a count of 1, and it remains to be shown.
The reason for this is that Pivot Table is referring to Pivot Cache. To update the pivot table, we have to refresh the pivot table.
To quickly refresh the Pivot Table in Excel, use the shortcut given below.
ALT + F5
You have to use the above keyboard shortcut as follows.
- Select any cell of the Pivot Table.
- Hold down the ALT key and then press the F5.
Sometimes ALT + F5 fails to work on your computer. It happens when you lock the function keys. In that case, in addition to pressing F5, you have to press the Fn key on your keyboard. After that, the keyboard shortcut will work for you.
Also read: Cannot Group That Selection Error in Pivot Tables
Method 2 – Use a VBA Code to Clear Pivot Table Cache
Consider a scenario in which you produced an Excel report or an Excel template with several pivot tables for someone else.
Then you have to give a user-friendly method for clearing the Pivot Table cache and updating Pivot Tables.
From the source data on the left side of the figure below, I constructed the pivot table on the right.
Now I want to send the above worksheet to someone else. I need to come up with a simple way to update and clear the pivot table for him because he is not familiar with Excel.
So, in this case, the best option is to write VBA code and link it to a button.
We can do that by using the VBA code below.
'Code developed by Steve Scott from https://spreadsheetplanet.com
Sub ClearCache()
'Loop through each PivotCache object in the PivotCaches collection of the active workbook
For Each PivotCache In ActiveWorkbook.PivotCaches
'Call the Refresh method on the current PivotCache object to refresh the associated PivotTable data
PivotCache.Refresh
'Move on to the next PivotCache object in the collection
Next PivotCache
End SubYou can follow the below steps for that.
- First, press ALT + F11 to open the VBA Editor.
- Expand the “Insert” menu and select “Module”.
- Then, copy and paste the above VBA code.
- Click the “Save” button.
- Click “No” in the pop-up box and save the workbook as an Excel Macro-Enabled Workbook (*.xlsm).
Now you have saved the VBA code to your Excel workbook.
Let me now also show you how to add a button in the worksheet so that you can click that button and clear the Pivot Cache right away.
- Insert a shape in the worksheet by following the below steps in the given sequence.
Insert tab >> Illustrations >> Shapes>> Rectangle
- Next, type “REFRESH” inside the shape.
- Right-click on the shape and click “Assign Macro”.
- Finally, select “ClearCache” from the macro name box and click the “OK” button.
Now, we can just click the “REFRESH” button to clear the “Pivot Table Cache”.
Also read: How to Group by Months in Excel Pivot Table?
Benefits of Clearing Cache of Pivot Tables
Clearing the cache of a Pivot Table has several benefits, such as:
- Improved performance: Clearing the cache can free up memory resources, leading to faster calculations and overall improved performance of the workbook.
- Reduced file size: Clearing the cache can decrease the overall size of the Excel file, making it easier to share and store.
- Accurate data: Clearing the cache ensures that your Pivot Table displays the most up-to-date data, avoiding potential discrepancies or outdated information.
- Elimination of redundant data: Removing the cache helps to get rid of any unnecessary or redundant data that may have been stored in the Pivot Table.
- Efficient memory usage: Clearing cache enables more efficient usage of system memory, allowing Excel to allocate resources to other tasks or processes.
- Enhanced stability: By freeing up memory resources, clearing cache can also reduce the chances of Excel crashing or becoming unresponsive due to heavy data processing.
- Easier troubleshooting: If you encounter any issues with your Pivot Table, clearing cache can be a helpful step in diagnosing and resolving the problem.
In this article, you learned two different methods to clear the Pivot Table cache.
You can choose the way that works best for you out of the three that all perform the same task.
Other Excel articles you may also like:
- Pivot Table Field Name is Not Valid – How to Fix?
- How to Group by Quarters in Excel Pivot Table?
- How to Connect Slicer to Multiple Pivot Tables
- How to Lock a Pivot Table in Excel
- How to Move Pivot Table in Excel?
- Remove Grand Total From Pivot Table in Excel
- Copy a Pivot Table in Excel to Another Sheet
