Old Items Appear in Pivot Table Drop Downs

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.

OldItems01

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

  1. Right-click a cell in the pivot table
  2. In the pop-up menu, click PivotTable options
  3. Click the Data tab
  4. In the Retain Items section, select None from the drop down list.
  5. Click OK, then refresh the pivot table.

OldItems02 

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:

  1. If you manually created groups that include the old items, ungroup those items.
  2. 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.
  3. Refresh the pivot table.
  4. 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.

______________

You may also like...

2 Responses

  1. pd tiwari says:

    Very nice solved problem.

  2. Garry says:

    Wow, that was bugging me all day, in Excel 2016 for Mac, but this solves it. Nice – and thanks a lot for this.

Leave a Reply to pd tiwari Cancel reply

Your email address will not be published. Required fields are marked *