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

Share on Facebook

Tweet about this on Twitter

Share on LinkedIn

Share on Google+

Related Posts

Show & Hide Field List in Excel Pivot Table Have you ever had the scenario where you are working on your Pivot Table and the Field List disappears?Lots of Excel users get annoyed with this but luckily I'm here to help you out 🙂If your Pivot Table Field List disappears, you can easily bring it back as I show you be...
Pivot Table Styles The default Pivot Table style in Excel is very bland and boring, stemming from a lack of creativity from the nerds over at Microsoft!You can give some life to your Excel Pivot Table by simply changing the Pivot Table Style!For our example, this is our Pivot Table:...
Group By Half Years With Excel Pivot Tables  In our previous post, we were able to Group by Month using Pivot Tables.  Let us take it up another notch, and group it by Half Years!In the example below I show you how to get the Sales Grouped by Half Years: January to June and July to December DOWNL...
Show The Percent of Difference From Previous Years...  I am sure that your boss has asked you to come up with a Year on Year variance report at some stage.  There are a couple of ways to get him/her an answer.One is using Formulas, but that will take time to set up and you are exposed to errors!The other method is t...