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.
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.
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.
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
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.
Now, after a Refresh, you will only see the items that currently exist in the data source as below