Clear Old Items in Pivot Table Drop Downs

There is a page on the Contextures website that describes how to clear old items in pivot table drop downs.

PivotOldItems

This week, Laurence emailed me about a problem he was having with those instructions, and you can keep reading, to see how we solved the problem.

Old Items in the Pivot Table

Those old items can appear if you change the pivot table source data – for example, you might remove a few obsolete products, or change a sales rep’s name.

When you refresh the pivot table, the new data can appear, but the old names still show up in the drop down lists, that you use for filtering.

Manually Clear the Old Items

One way to clear the old items is to do the steps manually:

  1. Remove pivot field(s) from the pivot table layout
  2. Refresh the pivot table
  3. Put the pivot field(s) back in the pivot table layout

Change a Pivot Table Setting

In Excel 2007 or Excel 2010, you can change a pivot table setting, to prevent old items from appearing.

  1. Right-click any cell in the pivot table, and click PivotTable options
  2. In the PivotTable Options dialog box, click the Data tab
  3. In the Retain Items section, select None from the drop down list.
  4. Click OK, then refresh the pivot table.

Use a Macro to Clear Old Items

If you’re using Excel 2002 or Excel 2003, there isn’t a Retain Items setting in the Options dialog box, but you can prevent old pivot items, with a bit of programming.

Things weren’t so easy, in earlier versions of Excel, so you’ll need a longer piece of code to remove the old items from the pivot table. Laurence, who emailed me about the problem, is still using Excel 2000. I’m sure he’s not the only one, so here’s the issue that he encountered, and the simple solution.

Problems With the Code

The code loops through all the worksheets, and all the pivot tables on each sheet. Laurence has 3 pivot tables on one worksheet, and was running the code to clear old pivot items in Excel 2000 and Excel 97. However, the code was hanging, and stopping somewhere in the second pivot table update.

He uploaded his sample file, in case I wanted to take a look at it, so I stepped through the code to see what was happening. The pivot data source wasn’t too big, and the pivot tables only had a few fields, but Excel seemed to go into overload in the second pivot table.

At the end of each pivot table loop, there were 2 lines:

    pt.ManualUpdate = False
    pt.RefreshTable

That’s a belt and suspenders approach, because the pivot table should refresh when ManualUpdate is set to False.

In one of my tests, I removed the pt.RefreshTable line, and the code ran smoothly. It removed all the old items, and the pivot tables all refreshed, without the additional line of code.

I was using Excel 2010, and Laurence confirmed that the same solution worked in his file, using Excel 2000. I hope that helps you, if you ever encounter a similar pivot table update problem. I’ve updated the code sample on the Contextures website, to alert future readers.

_________________

You may also like...

35 Responses

  1. André Piëst says:

    Thank you for this page.
    It was just what I needed.
    It did just that what was expected of it.
    Once again thanks very much.

  2. Eric Knigge says:

    Thanks – super helpful…using a template and did not want to distribute the older names to different groups…

  3. Damo says:

    Perfect, exactly what I needed to clear out the old and keep it tidy. Thanks.

  4. Techie says:

    Thank you Very much!!!!! Just what I needed after doing some major changes in to the original datasource!!!!!!!!!!! I am very glad!!!!!! Thank you again.

  5. Ron says:

    Thank you. This has been bugging me for months!!

  6. Marv Schmidt says:

    Thanks Debra! Once again, you provide a cool solution to a nagging problem.

  7. ARC says:

    THANK YOU!!

  8. Wim says:

    Hi Debra,

    Thanks for sharing this.

    The pivot table filters were stuffed with old data and they kept appearing in the slicers and the pivot filter drop down.
    This was bugging me for some time now.

    Now this is HISTORY! ;))

  9. Anita says:

    THANK YOU!! Exactly what I needed!

  10. David Stubbs says:

    Brilliant!

    It’s interesting how things become more intuitive the more you use them.

    Cheers!

  11. Chris says:

    no more hair pulling. thanks.

  12. Doug says:

    Simple solution to a nagging problem. Wish it was more obvious in Excel, but “hey”…..

    Thanks for the fix!

  13. brian says:

    Thanks a whole bunch.

  14. kh says:

    Thank you … it is helpful

  15. TJ says:

    Another way is:

    PivotTable Options > Data >
    Number of Items to retain per Field > None >
    Refresh pivot
    Voila!

  16. firdy says:

    it works…thanks a lot guys..

  17. walker says:

    I happened to this problems before and well solved, but when it comes again, I forget how to fix. this is really helpful for me.

  18. Adrian Langworthy says:

    Thank you very much for publishing this page.
    I used the “Change a Pivot Table Setting” part and it did exactly what I was looking for!
    Thanks again.

  19. Adrian G. says:

    Hello. I would like to ask some help on how to retain Value Field Settings > Show Values As when refreshing a Pivot Table. Thank you very much!

  20. Julian McIlroy says:

    Perfect – thank you

  21. Tavolengo says:

    Debra, you’re a Excel Master, just what I needed

  22. vicsar says:

    Hello Debra,

    It is 2015 and this is still a life saver. Even in Office 2016.

    Your code here: http://www.contextures.com/xlPivot04.html#Clear2002 is also very useful.

    Thanks.

  23. Christina says:

    Thank you!

  24. Cody says:

    Thanks – I was having a problem with having multiple rows in a spreadsheet, where rows seemed to contain inaccurate data if they were supposed to have no data. As soon as I selected ‘None’, all those problems were resolved and now the spreadsheet looks like it should. Good stuff. I’m using Excel 2016.

  25. Lauren says:

    Thank you – very useful pages on Pivot Tables

  26. Will says:

    Thanks for this tip. Solved a very annoying issue for me!

  27. Manish Kumar says:

    Guys best option to do so is below: as per MS Office 2010

    Pivot Table Options – Data – Number of Item retained per field “None”

  28. Rafael says:

    Perfect! Thanks a million for this help! It worked perfectly!

  1. October 20, 2012

    […] […]

  2. July 30, 2013

    […] fancy or particularly complicated, but it gets the job done. Or it used to. You know that old problem with pivot items hanging around?. Yeah, that got […]

Leave a Reply to Christina Cancel reply

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