Clear old items from a PivotTable filter list

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?

Table of Contents

• Why does this happen?
• Clear old items from a PivotTable
• Change default PivotTable settings
• Change PivotTable settings with VBA
• Clear old items from the selected PivotTable
• Clear old items from all PivotTables in the workbook
• Conclusion

Watch the video

Watch the video on YouTube.

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:

1. Right-click on a cell in the PivotTable
2. From the right-click menu,  select PivotTable Options…
   
3. From the PivotTable Options dialog, select the Data tab
4. Change the Number of items to retain per field option to None.
   
5. Click OK to close the PivotTable Options dialog box
6. Finally, Refresh the PivotTable.

Ta-Dah! All those redundant historic items have now disappeared.

Notes:
This setting applies to the Pivot Cache, so the change will be applied to every PivotTable built upon that cache.
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.

1. From the Ribbon at the top, click File > Options
2. In the Excel Options dialog box, select the Data section
3. Click the Edit Default Layout… button
   
4. In the Edit Default Layout dialog box, click the PivotTable Options… button
   
5. The PivotTable Options dialog appears, select the Data tab
6. Change the Number of items to retain per field option to None.
   
7. 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

Notes: At the time of writing, there are no equivalent codes available within Office Scripts.

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 😀

---

What next?

Discover how you can automate your work with our Excel courses and tools.

The Excel Academy