Pivot Table showing deleted data?

When you use Pivot Tables, you may come across situations where it appears that the pivot table is showing deleted data. Depending on where it is being shown, you may need to try a few options.

Have you refreshed the Pivot Table?

So below, note that Paris is shown in the Pivot Table but the data doesn’t have any Paris related data.

Pivot Table showing deleted data

This is probably because the Pivot Table is still remembering the old database and hasn’t been refreshed.

Right click on the Pivot Tables and click Refresh as shown below. You will see that the Paris data disappears.

Pivot Table showing deleted data

Whenever you rely on a Pivot Tables result, please make sure it is refreshed.

Deleted Data still visible in the dropdown field list in a Pivot Table

Even when you refresh, you may be surprised to still see the item (in this case Paris) in the dropdown list.

Pivot Table showing deleted data

This is due to a setting in Excel Pivot Tables which allows it to remember items that were in the data at one time, but have now been removed.

If you right click on the Pivot Table and choose Pivot Tables Options as below

Pivot Table showing deleted data

On the Data tab, you will see an option:

  • Retain items deleted from the data source: Number of items to retain per field

By default it is set to Automatic, but you can change it to None as shown below.

Pivot Table showing deleted data

Now, after a Refresh, you will only see the items that currently exist in the data source as below

Pivot Table showing deleted data