Clear Old Items from Pivot Table Drop Downs

Clear Old Items from Pivot Table Drop Downs

Old items might still appear in pivot table drop down lists, even after you remove the items from the source data. Here’s how to stop fix that problem – watch the short video, or see the written steps below.

Video: Fix Old Items in Pivot Table Lists

This video shows how to remove those old items from a pivot table, with a change to the pivot table settings. For newer versions of Excel, you’ll also see how to change your default settings, to prevent old items in all new pivot tables.

Video Timeline

  • 00:00    Intro
  • 01:37    Change a Setting
  • 02:57    Default Setting
Stop Old Items from Showing

To stop old items from showing in an existing pivot table, you can change one of the pivot options.

NOTE: This setting will affect all pivot tables that use the same pivot cache.

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

change items to retain option

Change Pivot Table Default Setting

To keep old items from appearing the new pivot tables that you build, you can also change that pivot option in your default settings.

NOTE: This feature is only available in Excel for Office 365, or Excel 2019 or later

Follow these steps to change the default pivot table settings.

  1. At the top of Excel, click the File tab
  2. Click Options
  3. In the Category list, click Data
  4. In the Data Options section, click Edit Default Layout button
    • Edit Default Layout button
  5. Click on PivotTable options
    • PivotTable options
  6. Click on the Data tab
  7. In the Retain Items section, select None from the drop down list.
    • Retain Items section
  8. Click OK, three times, to close all the windows.
More Info on Old Items

If your pivot table already contains old items, there are steps on my Contextures site, that show how to remove the old items.

There are also macros that you can use, to:

To get this information, go to the Clear Old Items page on my Contextures site.

Get the Workbook

To get the free workbook, go to the Clear Old Items page on my Contextures site.

  • The download file has sample data and pivot tables. The zipped file is in xlsm format, and contains the macros from that page.
  • To test the macros, be sure to enable macros, if prompted, when you unzip and open the workbook.

_____________________

Fix Old Items in Excel Pivot Table

Clear Old Items from Pivot Table Drop Downs

Clear Old Items from Pivot Table Drop Downs

_____________________

2 thoughts on “Clear Old Items from Pivot Table Drop Downs”

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.