It’s super irritating when you’ve deleted data on excel and it continues to show on the slicer or filter of a Pivot Table. This happens because Excel retains a cache of the PivotTable data to improve performance. I think making this a default setting was a mistake by Microsoft.

Fortunately, there’s a simple solution: clearing the Pivot Table cache.

Example

Consider the following sales data:

If I create a Pivot of this data and then delete the row for ‘Free State,’ the data is removed from the Pivot Table but still appears in the slicer as a grayed out option. It also still shows on the filter for that field:

Step-by-Step Guide to Clearing the PivotTable Cache

1. Right-click anywhere inside the PivotTable.
2. Select PivotTable Options.
3. Click on the Data tab.
4. Set Number of items to retain per field to None.
5. Click OK.
6. Right-click the PivotTable again and choose Refresh.

The PivotTable will now be updated, and any deleted data will no longer appear in the filters or on the slicer.

Conclusion

Clearing the PivotTable cache is a quick and easy way to keep your Pivot Tables accurate and up-to-date. Use this method whenever you find old and irrelevant data lingering in your PivotTable.