After you update the source data for a pivot table, and refresh the table, some of the old data might still appear in the pivot table drop downs. For example, you changed a product name from Whole Wheat to Whole Grain, and now both names show up in the pivot table’s Product drop down.
Prevent Old Items in Excel 2007
You can prevent old items from being retained in an Excel 2007 pivot table, by changing on of the pivot table options
- Right-click a cell in the pivot table
- In the pop-up menu, click PivotTable options
- Click the Data tab
- In the Retain Items section, select None from the drop down list.
- Click OK, then refresh the pivot table.
The old items will disappear from the pivot table drop downs, and won’t appear again.
Clear Old Items in Excel 2003
To prevent old items in Excel 2003 pivot tables, you can use programming to change the MissingItemsLimit setting.
Or, you can manually clear the old items, by following these steps:
- If you manually created groups that include the old items, ungroup those items.
- Drag the pivot field that contains old items out of the pivot table. Also remove it from any other pivot tables that use the same pivot cache.
- Refresh the pivot table.
- Drag the pivot field back to the pivot table.
This will clear the existing old items, but won’t prevent more from appearing later.
Watch the Video
To see the steps to change the retain items setting in Excel 2007, watch this short video.
For more information on Pivot Tables, see the Pivot Table Tutorials on the Contextures Website.