Home > Pivot > Layout > Old Items Excel Pivot Table Clear Old Items 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.

Old Items Remain in Pivot Field Drop Downs

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.

Video: Prevent Old Items in Pivot Table

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.

Video Timeline

• 0:00   Old Items - Intro
• 1:37    Change Retain Items Setting
• 2:57    Change Default Retain Items Setting

Change Retain Items Setting

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

1. Right click any cell in the pivot table
2. Click on PivotTable options
3. In the PivotTable Options dialog box, 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: This setting is available in PivotTable Options in Excel 2007 and later. For Excel 2003, the setting can be changed with a macro.

Change Retain Items Default Setting

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.

1. At the top of Excel, click the File tab
2. In the Excel Options window, in the Category list at the left, click Data
3. In the Data Options section, click Edit Default Layout button
• 
4. In the Edit Default Layout dialog box, at the bottom right, click the PivotTable Options button

• 
1. In the PivotTable Options dialog box, click on the Data tab
2. In the Retain Items section, select None from the drop down list.
• 
3. Click OK, three times, to close all the windows.

Manually Clear Old Items

Instead of changing an option setting, you can manually clear the old items from the drop down lists:

1. If you manually created any groups that include the old items, ungroup those items.
2. Remove the pivot field from of the pivot table.
3. Right-click on the pivot table, and click the Refresh command
4. Add the pivot field back to the pivot table

Macro to Clear Old Items

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     

Macro to Clear Old Items -- Excel 2000

In Excel 2000 and Excel 97, run the following code to clear the old items from the drop down list.

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

Macros for Default Setting

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.

• Change default to None
• Change default to Automatic

Default Setting - None

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

Default Setting - Automatic

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

Get the Sample File

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.

More Tutorials

Show and Hide Pivot Items

Refresh Pivot Table

Grouping Data

Pivot Filters, Multiple

Pivot Filter Macros