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”.

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.

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.

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

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.

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.

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.

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.

__________________________