We’ve just started a new financial year and all our source data has been cleared out ready to start afresh. I was expecting to refresh my standard pivot table reports to see refreshed and tidy tables that had clear pivot table filters, relating to the new year only.
What actually happened was that my pivot table looked fairly similar to last year. My drop down lists contained all the options that appeared last year and felt littered.
Luckily you can clear the cache and start afresh with clear pivot table filters.
Instructions for Clearing out Old Items in Pivot Table filters
- Right click within pivot table and select pivot table options
- Go to the data tab
- Flip the number of items to retain per field: to None
- Refresh pivot table
You don’t have to wait for a new financial year to do this.
If you ever make a mistake in your source data, that you later correct, you may have noticed that the pivot table filter is totally unforgiving and retains those errors in the drop down. This trick enables you to remove these errors and forces the pivot table to only show records that are currently in the source data.