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.

NOTE: There is also a Clear Old Items feature in my PivotPower Premium add-in, along with many other time-saving pivot table tools.

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.

Note: This setting is available in Excel 2007 and later.

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 Excel 2007 and later.


Manually Clear Old Items

Instead of changing a 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 DeleteMissingItems2002All()
'for Excel 2002 and later
'prevents unused items in non-OLAP PivotTables
'pivot table tutorial by
Dim pt As PivotTable
Dim ws As Worksheet
Dim pc As PivotCache

'change the settings
For Each ws In ActiveWorkbook.Worksheets
  For Each pt In ws.PivotTables
    pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
  Next pt
Next ws

'refresh all the pivot caches
For Each pc In ActiveWorkbook.PivotCaches
  On Error Resume Next
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()
'pivot table tutorial by
'gets rid of unused items in PivotTable
' 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.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
          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      

Pivot Table Tools

To save time when building, formatting and modifying your pivot tables, use the tools in my Pivot Power Premium add-in. With just a few clicks, you can:

  • copy the formatting from one pivot table, and apply it to another pivot table.
  • change all the values from Count to Sum
  • remove the "Sum of" from all the headings

and much more!

Download the Sample File

Download the zipped sample file for this tutorial. go to top

Search Contextures Sites


Get weekly Excel tips from Debra



pivot power free




pivot power free


Last updated: October 26, 2018 6:53 PM