Table of Contents 

Excel -- Pivot Tables -- Clear Old Items

  1. Manually Clear Old Items
  2. Change the Retain Items Setting in Excel 2007
  3. Programmatically Clear Old Items -- Excel 2002 and later
  4. Programmatically Clear Old Items -- Excel 97/2000
Download the zipped sample file

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, some sales reps may leave the company, and the names of their replacements appear in the source table.

Even after you refresh the pivot table, the names of the old sales reps will appear, along with the new names. In the list at right, Cartier has replace Gill, but Gill still appears in the list.

Manually Clear Old Items

To manually clear the old items from the list:

  1. If you manually created any groups that include the old items, ungroup those items.
  2. Drag the pivot field out of the pivot table.
  3. On the Pivot toolbar, click the Refresh button
  4. Drag the pivot field back to the pivot table

Change the Retain Items Setting in Excel 2007

To prevent old items from being retained in an Excel 2007 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.

To view the steps in a short video, click here  

 

NumberItems

Programmatically Clear Old Items -- Excel 2002 or later

In Excel 2002, and later versions, you can programmatically change the pivot table properties, to prevent missing items from appearing, or clear items that have appeared. 

Sub DeleteMissingItems2002All()
'prevents unused items in non-OLAP PivotTables

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
  pc.Refresh
Next pc

End Sub

 

 

Programmatically Clear Old Items -- Excel 97/Excel 2000

In previous versions of Excel, run the following code to clear the old items from the dropdown list. 

Sub DeleteOldItemsWB()
'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.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
  Next pt
Next ws

End Sub

 

 
1. Pivot Tables -- Dynamic Data Source
2. Pivot Tables -- Data Field Layout
3. Pivot Tables -- Show and Hide Items
4. Pivot Tables -- Clear Old Items
5. Pivot Tables -- Field Settings
6. Pivot Tables -- GetPivotData
7. Pivot Tables -- Grouping Data
8. Pivot Tables -- Multiple Consolidation Ranges
9. Pivot Tables -- Printing   
10. Pivot Tables -- Custom Calculations 
11. Pivot Tables -- Pivot Cache     
12. Pivot Tables -- Protection      
13. Pivot Tables -- Grand Totals

       Home     Excel Tips     Excel Files      Blog    Contact

 

RSS Feed

 

 

 

The Excel Store

Last updated: July 18, 2008 11:45 PM