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):
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!
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!
How To Clear & Delete Old Pivot Table Items
Helpful Resource:
