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.

Claim your free eBook

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

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.

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.


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 😀

Headshot Round

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:

  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 *