Get our FREE VBA eBook of the 30 most useful Excel VBA macros.
Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.
Email Address *
First Name *

By entering your email address you agree to receive emails from Excel Off The Grid. We'll respect your privacy and you can unsubscribe at any time.

Clear old items from a PivotTable filter list

Clear old PivotTable Items

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?

Old items in a PivotTable

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 Icon
Download the file: 0065 Clear old items in PivotTable.zip

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…
    PivotTable options from PT right click menu
  3. From the PivotTable Options dialog, select the Data tab
  4. Change the Number of items to retain per field option to None.
    PivotTable Options dialog box
  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.

Old PivotTable items cleared

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.

Formula Magic with Dynamic Arrays

Of all the features available in Excel dynamic arrays provide the most power for the smallest time investment.  Yet most Excel users do not even know what they are.

Have you ever faced these spreadsheet scenarios?

  • How can I use VLOOKUP to return all the matching items, not just the first?
  • How can I sort my information using a formula, so I don’t have to keep clicking the sort button?
  • How can I quickly create unique lists of items to use with my SUMIFS calculation?
  • How can I stop copying down formulas every time my source data changes.
  • How can I build a PivotTable-like report, but using formulas so I don’t have to click refresh ever again.

Well, I’m here to give you some good news. with dynamic arrays, all these can be achieved easily 🙂

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
    Excel Options dialog box
  4. In the Edit Default Layout dialog box, click the PivotTable Options… button
    Edit Default Layout dialog box
  5. The PivotTable Options dialog appears, select the Data tab
  6. Change the Number of items to retain per field option to None.
    PivotTable Options dialog box
  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 😀



Get our FREE VBA eBook of the 30 most useful Excel VBA macros.
Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.

Email Address *
First Name *

By entering your email address you agree to receive emails from Excel Off The Grid. We’ll respect your privacy and you can unsubscribe at any time.


Don’t forget:

If you’ve found this post useful, or if you have a better approach, then please leave a comment below.

Do you need help adapting this to your needs?

I’m guessing the examples in this post didn’t exactly meet 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:

  1. Read other blogs, or watch YouTube videos on the same topic.  You will benefit much more by discovering your own solutions.
  2. Ask the ‘Excel Ninja’ in your office.  It’s amazing what things other people know.
  3. 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.
  4. 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:

Leave a Reply

Your email address will not be published. Required fields are marked *