Manually Clear Old Items in Pivot Drop Down

Manually Clear Old Items in Pivot Drop Down

If you remove an item from your pivot table’s source data, it might still show up in the drop downs, even after you refresh the pivot table.

In this example, a product name was originally “Whole Wheat”. In the source data, all the records for that product were changed to the new name, “Whole Grain”.

clearolditemsmanually02

In the source table, the drop downs in the heading row only show Whole Grain. The Whole Wheat item has disappeared, because no records have that product.

clearolditemsmanually01

Refresh the Pivot Table

To update the pivot table, to show the new product name, you can right-click on a pivot table cell, and click Refresh.

clearolditemsmanually03

The pivot table updates, and the Whole Wheat item changes to Whole Grain.

clearolditemsmanually04

Check the Drop Down List

However, if you check the drop down list in the pivot field’s heading row, the old item is still listed.

clearolditemsmanually05

You can change a pivot table setting, to prevent old items from being retained. You can see the steps for this in the video below.

Manually Remove the Old Item

If you don’t want to change the setting, you can manually remove the old items:

  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

The old item should have disappeared from the drop down list, after you follow these steps.

clearolditemsmanually06

Occasionally, an old item will stay in the list, even after you follow the steps to manually remove it. In that case, change the pivot table setting, for retaining old items..

Pivot Power Premium

If you have bought a copy of my Pivot Power Premium add-in, you can quickly remove old items by clicking the command on the Ribbon tab.

clearolditemsmanually07

Watch the Video

To see the steps for preventing old items from appearing in the pivot table, please watch this short video tutorial. These instructions apply to both Excel 2010 and Excel 2007.

__________________________

2 thoughts on “Manually Clear Old Items in Pivot Drop Down”

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.