Contextures

Excel Pivot Table - Clear Old Items

After you change the source data for a pivot table, the old items might still appear in the drop downs. The instructions below will help you clear those items.

Old Items Remain in Pivot Field Dropdowns

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 resturctured, 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 list below, the Central region still appears in the list.

This pivot table tutorial shows how you can clear the old items either manually or programmatically.

clear old items from drop down list

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.

Change Retain Items Setting

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

  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: This setting is available in PivotTable Options in Excel 2007 and later. For Excel 2003, the setting can be changed with a macro.

NumberItems

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.

NOTE: There are macros below, that change the default setting for you.

  1. At the top of Excel, click the File tab
  2. In the Category list, click Data
  3. In the Data Options section, click Edit Default Layout button
    • Edit Default Layout button
  4. Click on PivotTable options
    • PivotTable options button
  5. Click on the Data tab
  6. In the Retain Items section, select None from the drop down list.
    • NumberItems
  7. 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     

go to top

Macro to Clear Old Items -- Excel 2000

In Excel 2000 and Excel 97, run the following code to clear the old items from the dropdown list. go to top

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.

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.

go to top

 

About Debra

 

Last updated: July 11, 2021 11:59 AM