Have you ever cleared, deleted or replaced your Pivot Table data/items but they still shows 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 our example below we have our Pivot Table with the Years showing in the Column area (2014, 2012, 2013):

Clear & Delete Old Pivot Table Items cache

DOWNLOAD EXCEL WORKBOOK

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

Clear & Delete Old Pivot Table Items cache

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

Clear & Delete Old Pivot Table Items cache

 

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

Clear & Delete Old Pivot Table Items cache

 

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!

Clear & Delete Old Pivot Table Items cache

 

BUT WAIT! 

 

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

Clear & Delete Old Pivot Table Items cache

 

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

Clear & Delete Old Pivot Table Items cache

 

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

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

Clear & Delete Old Pivot Table Items cache

 

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

Clear & Delete Old Pivot Table Items cache

 

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

Clear & Delete Old Pivot Table Items cache

 

How To Clear & Delete Old Pivot Table Items

Clear & Delete Old Pivot Table Items cache

Helpful Resource:

Excel Pivot Table

 

 

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin

Share on Google+

Google+

Related Posts

Move an Excel Pivot Table If you have your Excel Pivot Table ready, but it's not in the best location, how do you move this onto another section of your Excel worksheet? Well, you can simply select Move PivotTable and you can move your Excel Pivot Table very quickly! For our example, let's move this...
Show The Percent of Row Total With Excel Pivot Tab... Excel Pivot Tables have a lot of useful calculations under the SHOW VALUES AS option and one that can help you a lot is the PERCENT OF ROW TOTAL calculation. This option will immediately calculate the percentages for you from a table filled with numbers such as sales data, exp...
Slicer Connection Option Greyed Out For Excel Pivo... Sometimes when you create a Pivot Table and want to insert a Slicer you are unable to do this as the Slicer button is greyed. You try to click on the Slicer button but nothing happens. What gives?? There are two things that can cause your Slicer connection to be grey...
Count VS Sum in Pivot Tables The #1 complaint that I get from Pivot Tables is "Why do my values show as a Count of rather than a Sumof ?" Well there are three reasons why this is the case: 1. There are blank cells in your values column within your data set; or 2. There are "text" cells in your value...