Clear & Delete Old Pivot Table Items

Unable to Clear Pivot Table Cache Memory ??

Have you ever cleared, deleted, or replaced your Pivot Table data/items but they still show inside your Pivot Table filters?

What gives??

Well, you can easily clear your Pivot Table’s old items from your Pivot Table’s memory or cache

In this article, you will be provided a guide on:

• How to Clear Pivot Table Cache Memory
• Change Default setting of Retain Items
• Conclusion

How to Clear Pivot Table Cache Memory

Watch it on YouTube and give it a thumbs-up!

You might have faced this issue before. You have deleted or replaced old data from the data table but even after refreshing the pivot table, the data is still visible.

This is because the old data is stored in the cache memory and displayed in filter selections even if there is no data for it at all. This can be confusing as well as annoying.

Let’s look at an example and understand How to Clear Pivot Table Cache Memory!

So, you have created a Pivot Table using the original data source containing the years 2012, 2013, and 2014.

The Pivot Table will look like this :

Now, you change the year 2012 in your data source to 2013 and the same is reflected in the Pivot Table as well.  But, the year 2012 is still visible in the Pivot Table’s filter selection.

This is because the old item is still saved in the Pivot Table cache memory. Let’s learn how to fix it!

Example 1:

Follow the step-by-step tutorial on How to Clear Pivot Table cache memory and make sure to download the exercise workbook to follow along:

STEP 1: Below is our data source and we want to replace the year 2012 with 2013, effectively only showing the years 2014 & 2013.

Go to Home > Find & Select > Replace

Let us replace the year 2012 with the year 2013.  Click Replace All.

 

STEP 2: Go back to your Pivot Table.  Right click and select Refresh.

 

We have technically deleted the year 2012 records, so they should be gone from our Pivot Table, right?

Hmm.. Looking good, the year 2012 is now gone from our Pivot Table!

 

BUT WAIT! 

 

Clicking on the Column Labels drop-down list, the Year 2012 is still there!  Bloody hell!

 

STEP 3: Let us fix this!  Go back to your Pivot Table > Right click and select PivotTable Options.

 

STEP 4: Go to Data > Number of items to retain per field.

Select None then OK.  This will stop Excel from retaining deleted data!

This box lets you set the number of items per field to temporarily save, or cache with the workbook.

STEP 5: Go back to your Pivot Table.  Right-click and select Refresh.

 

Click the Column Labels drop-down list, and the Year 2012 is now gone!  Problem fixed!

Example 2:

In this table, months are displayed as abbreviations i.e. 3-letter month name, and using this data a Pivot Table is created.

But after creating Pivot, you realize it is better to present the full-length month name and hence you change the name in the data source.

Now, when you look at the filter selection of Pivot Table, you will find that both versions of the month names are visible.

Let’s fix that using the 3 simple steps!

STEP 1: Right-click on the Pivot Table and select PivotTable Options.

STEP 2: In the dialog box, go to Data Tab.

STEP 3: In the dropdown for the Number of items to retain per field, select None.

Click OK and don’t forget to refresh the Pivot Table.

This will remove the abbreviated version of the month name and only show the full-length month name.

To keep the data in the Pivot Table’s filter updated, make sure to change the number of items to retain per field to None.

Once that is done and you refresh your table, the old data will no longer appear. This will clear Pivot Table Cache Memory!

Change Default setting of Retain Items

By default, the number of items to retain per field is set to “Automatic”.

If you want you even change the default setting to None so this setting is reflected in all the Pivot Tables you create. To do that, follow the steps below (this is applicable for Office 365 and Excel 2019 only):

STEP 1: Click on the File Tab at the top-left corner of Excel

STEP 2: From the left panel, select Options.

STEP 3: In the Excel Options dialog box, click on the Data.

STEP 4: Under Data Options, select Edit Default Layout button

STEP 5: In the Edit Default Layout dialog box, click on PivotTable options button.

 

STEP 6: In the PivotTable Options dialog box, click on the Data tab,

STEP 7: Under the Data tab, select None from the drop-down list in the Retain Items section

Click Ok three times and Voila it’s done! The old deleted items from the data source are not shown in the Pivot Table’s filter selection anymore.

How To Clear Pivot Table Cache Memory

Conclusion

In this tutorial, you have learned how to delete pivot table cache memory and change the default setting of the retain items deleted from the data source.

This will help you to force the Pivot Table to only show data or items that are currently in the data source.

Helpful Resource:

Make sure to download our FREE PDF on the 333 Excel keyboard Shortcuts here:

You can learn more about how to use Excel by viewing our FREE Excel webinar training on Formulas, Pivot Tables, and Macros & VBA!