It’s a question that every PivotTable user has had to ask at some point: How to clear old items from a PivotTable filter list?
Look at the screenshot below, we can see Delta in the PivotTable filter box and the slicer, but there is no Delta in the source data table. How can that happen?
Download the example file
I recommend you download the example file for this post. Then you’ll be able to work along with examples and see the solution in action, plus the file will be useful for future reference.
Download the file: 0065 Clear old items in PivotTable.zip
Watch the video
Contents
Why does this happen?
Every time we refresh a PivotTable, it updates the Pivot Cache. The Pivot Cache is an unseen part of Excel which stores a copy of the source data.
Having a copy of the data ensures PivotTable calculations are fast. So, when we refresh a PivotTable, we are actually refreshing the data held within the Pivot Cache.
Even after removing records from the source data, old items can still appear in the dropdowns and slicers of the PivotTable. The Pivot Cache contains a copy of the data and has its own settings governing how that data is used.
One of the default behaviors of the Pivot Cache is that it retains a list of previous unique values.
But, that’s enough of the why…. let’s look at how to fix it.
Clear old items from a PivotTable
To clear the old items from a PivotTable dropdown list or slicer, we must change one of the PivotTable options:
- Right-click on a cell in the PivotTable
- From the right-click menu, select PivotTable Options…
- From the PivotTable Options dialog, select the Data tab
- Change the Number of items to retain per field option to None.
- Click OK to close the PivotTable Options dialog box
- Finally, Refresh the PivotTable.
Ta-Dah! All those redundant historic items have now disappeared.
If using the Data Model as the source for a PivotTable, the Pivot Cache is not used; and it does not suffer with this problem.
Change default PivotTable settings
If you have Excel 365, or Excel 2019 or later, it is possible to change the default PivotTable settings. This ensures every PivotTable we create going forwards will have the setting above applied automatically.
- From the Ribbon at the top, click File > Options
- In the Excel Options dialog box, select the Data section
- Click the Edit Default Layout… button
- In the Edit Default Layout dialog box, click the PivotTable Options… button
- The PivotTable Options dialog appears, select the Data tab
- Change the Number of items to retain per field option to None.
- Click OK three times to close each of the windows.
The next PivotTables you create going forward will no longer retain missing items.
Change PivotTable settings with VBA
The setting changed above can also be applied with VBA. These code snippets can be run from a standard code module.
Clear old items from the selected PivotTable
The following macro applies the same steps as above to the selected PivotTable.
Sub ClearOldPtItemsActive() 'Create the variable for the PivotTable Dim pt As PivotTable 'Find the active PivotTable For Each pt In ActiveSheet.PivotTables If Not Intersect(Selection, pt.TableRange2) Is Nothing Then 'Change the setting pt.PivotCache.MissingItemsLimit = xlMissingItemsNone End If Next pt End Sub
There are three options available for the Missing Items Limit property. We have only applied the xlMissingItemsNone option in this post; listed below are all the available options for this property.
'The default number of unique items per Pivot Field allowed pt.PivotCache.MissingItemsLimit = xlMissingItemsDefault 'Pre-Excel 2007 PivotTables which have 32,500 as the maximum of unique items pt.PivotCache.MissingItemsLimit = xlMissingItemsMax 'Excel 2007+ PivotTables have 1,048,576 as the maximum of unique items pt.PivotCache.MissingItemsLimit = xlMissingItemsMax2 'No unique items per Pivot Field allowed pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
Clear old items from all PivotTables in the workbook
The following macro applies the same settings as above but to all the PivotTables in the active workbook.
Sub ClearOldPtItemsWb() 'Create the variables Dim ws As Worksheet Dim pt As PivotTable 'Loop through all worksheets For Each ws In ActiveWorkbook.Worksheets 'Loop through all PivotTables For Each pt In ActiveSheet.PivotTables 'Change the setting pt.PivotCache.MissingItemsLimit = xlMissingItemsNone Next pt Next ws End Sub
Conclusion
So, it turns out that it’s just one pesky setting that causes all those redundant items to appear. But thankfully, you now know how to clear old items from a PivotTable, so it’s no longer an issue for you 😀
About the author
Hey, I’m Mark, and I run Excel Off The Grid.
My parents tell me that at the age of 7 I declared I was going to become a qualified accountant. I was either psychic or had no imagination, as that is exactly what happened. However, it wasn't until I was 35 that my journey really began.
In 2015, I started a new job, for which I was regularly working after 10pm. As a result, I rarely saw my children during the week. So, I started searching for the secrets to automating Excel. I discovered that by building a small number of simple tools, I could combine them together in different ways to automate nearly all my regular tasks. This meant I could work less hours (and I got pay raises!). Today, I teach these techniques to other professionals in our training program so they too can spend less time at work (and more time with their children and doing the things they love).
Do you need help adapting this post to your needs?
I'm guessing the examples in this post don't exactly match your situation. We all use Excel differently, so it's impossible to write a post that will meet everybody's needs. By taking the time to understand the techniques and principles in this post (and elsewhere on this site), you should be able to adapt it to your needs.
But, if you're still struggling you should:
- Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
- Ask the 'Excel Ninja' in your office. It's amazing what things other people know.
- Ask a question in a forum like Mr Excel, or the Microsoft Answers Community. Remember, the people on these forums are generally giving their time for free. So take care to craft your question, make sure it's clear and concise. List all the things you've tried, and provide screenshots, code segments and example workbooks.
- Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.
What next?
Don't go yet, there is plenty more to learn on Excel Off The Grid. Check out the latest posts:
