After you change the source data for a pivot table, a few old items might still appear in the pivot table item drop downs. The instructions below will help you clear old items, with manual steps or macro.
The data in the pivot table source may change, and items that were previously in the source are no longer included. For example, a company restructured, and went down from 3 regions -- East, Central and West -- to only 2 regions -- East and West.
Even after you refresh the pivot table, the name of the old region might continue to appear in the drop down lists, along with the new names. In the screen shot below, the Central region still appears in the list of regions.
This pivot table tutorial shows how you can clear the old items either manually or with a macro.
This short video shows how to change a setting in a pivot table, so old items do not appear in the drop down lists. The written instructions are below the video.
To prevent old items from being retained in a pivot table, you can change an option setting:
Note: This setting is available in PivotTable Options in Excel 2007 and later. For Excel 2003, the setting can be changed with a macro.
To prevent old items from being retained in new pivot tables, you can change a pivot table default setting, in Excel 2019, or Excel for Office 365. Then, when you create new pivot tables, they will automatically have the settings that you prefer.
NOTE: There are macros below, that change the default setting for you.
Follow these steps to change the default settings.
Instead of changing an option setting, you can manually clear the old items from the drop down lists:
The following macro will change the pivot table properties, to prevent missing items from appearing, or clear items that have appeared.
This macro changes the MissingItemsLimit setting for all pivot tables in the active workbook.
Sub DeleteMissingItemsAllPTs() 'For NEW versions of Excel 'developed by contextures.com 'to prevent unused items in ' non-OLAP PivotTables ' changes MissingItemsLimit setting ' for all PTs in active workbook Dim pt As PivotTable Dim wb As Workbook Dim ws As Worksheet Dim pc As PivotCache Set wb = ActiveWorkbook 'change the settings For Each ws In wb.Worksheets For Each pt In ws.PivotTables pt.PivotCache.MissingItemsLimit _ = xlMissingItemsNone Next pt Next ws 'refresh all the pivot caches For Each pc In wb.PivotCaches On Error Resume Next pc.Refresh Next pc End Sub
Sub DeleteOldItemsWB() 'For OLD versions of Excel ' XL97 and XL2000 'to prevent unused items in ' non-OLAP PivotTables ' changes MissingItemsLimit setting ' for all PTs in active workbook ' based on MSKB (202232) Dim ws As Worksheet Dim pt As PivotTable Dim pf As PivotField Dim pi As PivotItem On Error Resume Next For Each ws In ActiveWorkbook.Worksheets For Each pt In ws.PivotTables pt.RefreshTable pt.ManualUpdate = True For Each pf In pt.VisibleFields If pf.Name <> "Data" Then For Each pi In pf.PivotItems If pi.RecordCount = 0 And _ Not pi.IsCalculated Then pi.Delete End If Next pi End If Next pf pt.ManualUpdate = False 'pt.RefreshTable 'optional - might hang Excel 'if 2 or more pivot tables on one sheet Next pt Next ws End Sub
To prevent old items from being retained in new pivot tables, you can use these macros to change a pivot table default setting, in Excel 2019, or Excel for Office 365.
This macro changes the setting to None, so old items will not appear in new pivot tables.
Sub DefaultMissingItemsNone() 'For Excel 365 or Excel 2019 'Default setting =None 'developed by contextures.com 'change default setting for ' MissingItemsLimit setting ' for all new PTs Application _ .DefaultPivotTableLayoutOptions _ .xlMissingItemsNone = 0 End Sub
This macro changes the setting to Automatic, so old items will appear in new pivot tables
Sub DefaultMissingItemsAuto() 'For Excel 365 or Excel 2019 'Default setting =Automatic 'developed by contextures.com 'change default setting for ' MissingItemsLimit setting ' for all new PTs Application _ .DefaultPivotTableLayoutOptions _ .xlMissingItemsNone = -1 End Sub
To see the sample data and pivot table used in these examples, download the Pivot Table Old Items workbook. The zipped file is in xlsm format, and contains the macros from this page. To test the macros, be sure to enable macros, if prompted, when you unzip and open the workbook.
Last updated: January 15, 2022 12:26 PM