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.
- Right-click a cell in the pivot table
- Click on PivotTable options
- Click on the Data tab
- In the Retain Items section, select None from the drop down list.
- Click OK, then refresh the pivot table.
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.
- At the top of Excel, click the File tab
- Click Options
- In the Category list, click Data
- In the Data Options section, click Edit Default Layout button
- Click on PivotTable options
- Click on the Data tab
- In the Retain Items section, select None from the drop down list.
- 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
_____________________
Thank you, very clear explanation. I appreciate the written steps as an alternative to watching a video
You’re welcome, Helen, and thanks for letting me know that the written steps helped you!