Fix Old Items in Excel Pivot Table Lists

If you remove items from a pivot table’s source data, those items might still appear in the pivot table drop down lists, even after you refresh the pivot table. Here’s how you can stop those old items from appearing – watch the short video, or read the written steps below.

Fix Old Items in a Pivot Table

To prevent old items from being retained in a pivot table, you can change an option setting in that pivot table.

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

If you’re using Excel for Office 365, or Excel 2019 or later, you can change the pivot table default settings. Then, when you create new pivot tables, they’ll automatically have the settings that you prefer.

Follow these steps to change the default 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. Click on PivotTable options
   • 
6. Click on the Data tab
7. In the Retain Items section, select None from the drop down list.
   • 
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.

My Contextures site also has 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.

Video: Fix Old Items in Pivot Table Lists

In this video, you’ll see 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, for all new pivot tables.

Video Timeline

• 00:00    Intro
• 1:37    Change a Setting
• 2:57    Default Setting

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