Search Contextures Sites
Last updated: December 22, 2012 12:22 AM
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.
- Video: Prevent Old Items in Excel 2010 / 2007
- Change the Retain Items Setting in Excel 2007
- Manually Clear Old Items
- Programmatically Clear Old Items -- Excel 2002 and later
- Programmatically Clear Old Items -- Excel 97/2000
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.
This pivot table tutorial shows how you can clear the old items either manually or programmatically.
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 an Excel 2010 or 2007 pivot table, you can change an option setting:
- Right-click a cell in the pivot table
- Click on PivotTable options
- Click on the Data tab
- In the Retain Items section, select None from the drop down list.
- Click OK, then refresh the pivot table.
To manually clear the old items from the drop down lists:
- If you manually created any groups that include the old items, ungroup those items.
- Remove the pivot field from of the pivot table.
- Right-click on the pivot table, and click the Refresh command
- Add the pivot field back to the pivot table
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 'pivot table tutorial by contextures.com 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
Sub DeleteOldItemsWB() 'pivot table tutorial by contextures.com '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 'optional - might hang Excel 'if 2 or more pivot tables on one sheet Next pt Next ws End Sub
Download the zipped sample file for this tutorial.
Pivot Table Introduction
Clear Old Items in Pivot Table
Create a Pivot Table in Excel 2007
Data Field Layout
Dynamic Data Source
FAQs - Pivot Tables
Filter Source Data
Filters, Top 10
Filters, Report Filters
Layout, Excel 2007
Multiple Consolidation Ranges
Printing a Pivot Table
Show and Hide Items
Unique Item Count
Pivot Table Books
Pivot Table Add-Ins
Pivot Table Videos
Clear Old Items
Copy a Custom PivotTable Style
Create Pivot Table in Excel 2007
Create Pivot Table from Multiple Sheets
Data Field Layout
Date Filters, Add
Layout, Excel 2007
Report Filters, Add
Subtotals, Create Multiple
Top 10 Filters
Contextures Inc., Copyright ©2012
All rights reserved.