Contextures Blog

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.

NOTE: There is also a Clear Old Items feature in my PivotPower Premium add-in, along with many other time-saving pivot table tools.

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
5. • 
6. Click on PivotTable options
7. • 
8. Click on the Data tab
9. In the Retain Items section, select None from the drop down list.
10. • 
11. 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:

• change the Retain Item settings for all pivot tables in the workbook
• change Excel’s default settings for pivot tables (Office 365 or Excel 2019 and later)

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

_____________________