Excel PivotTable Shows Deleted Items in Filter List: Fix

When you delete data from your source table and refresh a PivotTable, the filter drop-down list may still show old items that no longer exist. This happens because Excel caches the unique values from the original data range inside the PivotTable cache. The stale items can clutter your filter list, making it harder to find current entries. This article explains why the cache retains deleted items and provides two reliable methods to clear them out completely.

Why Old Items Stay in the PivotTable Filter After Deleting Source Data

Excel stores a snapshot of all unique values from the source data inside the PivotTable cache when you create or refresh the report. This cache is separate from the source table. When you delete rows or change values in the source, the cache does not automatically update its list of stored items. The next refresh only updates the aggregated numbers, not the list of items shown in the filter drop-down.

The root cause is the way Excel manages the PivotTable cache. The cache retains every distinct value it has ever seen for a field unless you force a full cache rebuild. Excel does this to improve performance on large datasets by avoiding a complete re-scan of the source every time you filter. However, this design causes deleted items to remain visible in the filter list indefinitely.

Another contributing factor is the use of named ranges or table references that shrink after data deletion. When you delete rows from a named range, the range definition does not automatically contract. The PivotTable still references the original range, which may now include empty cells. Excel caches empty strings as valid items, adding more clutter to the filter list.

Steps to Remove Deleted Items from PivotTable Filter List

Two methods can clear stale filter items. The first method uses a built-in Excel option that forces a deferred layout update. The second method uses a simple VBA macro for complete cache reset. Choose the method that matches your workflow and technical comfort level.

Method 1: Force a Cache Rebuild Using Deferred Layout Update

1. Select any cell inside the PivotTable
   Click a cell in the PivotTable to activate the PivotTable Analyze and Design tabs on the ribbon.
2. Open the PivotTable Options dialog
   Right-click the PivotTable and choose PivotTable Options. Alternatively, go to PivotTable Analyze > Options on the far left of the ribbon.
3. Enable Defer Layout Update
   In the PivotTable Options dialog, go to the Data tab. Under PivotTable Data, check the box labeled Defer Layout Update. This tells Excel to wait before applying any layout changes.
4. Change the data source slightly
   In the same dialog, switch to the Source Data section. Click the button for Change Data Source. In the Change PivotTable Data Source dialog, do not actually change the range. Instead, click inside the Table/Range box and press Enter. This action forces Excel to re-evaluate the source range and rebuild the cache.
5. Uncheck Defer Layout Update
   Go back to the PivotTable Analyze tab. In the PivotTable group, uncheck the Defer Layout Update checkbox. Excel now refreshes the PivotTable with a clean cache that contains only the current items from the source data.

Method 2: Clear the PivotTable Cache with a VBA Macro

1. Open the Visual Basic Editor
   Press Alt + F11 to open the VBA editor. In the menu, go to Insert > Module to create a new code module.
2. Paste the macro code
   Copy and paste the following code into the module window:

   Sub ClearPivotCache()
       Dim pt As PivotTable
       For Each pt In ActiveSheet.PivotTables
           pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
           pt.RefreshTable
       Next pt
   End Sub

3. Run the macro
   Press F5 to run the macro while the cursor is inside the code. The macro sets the MissingItemsLimit property to xlMissingItemsNone, which tells Excel to discard all cached items that no longer exist in the source. Then it refreshes each PivotTable on the active sheet.
4. Save the workbook as a macro-enabled file
   Press Ctrl + S. In the Save As dialog, set the file type to Excel Macro-Enabled Workbook (.xlsm). Click Save.

If the Filter Still Shows Old Items After the Main Fix

PivotTable Is Connected to an External Data Source

When your PivotTable uses an external connection such as SQL Server or Power Pivot, the cache behavior differs. The external source may retain historical items in its own query cache. Open the connection properties in Data > Queries & Connections. Right-click the connection and select Properties. On the Usage tab, check the box for Refresh data when opening the file. Then go to the Definition tab and click Edit Query. Add a WHERE clause to exclude deleted rows at the source level.

OLAP-Based PivotTable from a Cube

PivotTables built on an OLAP cube do not cache items locally. The filter list reflects the cube’s current dimension members. If deleted items still appear, the cube itself has not been processed. Contact your database administrator to process the cube and remove the obsolete members. You cannot fix this from Excel alone.

Multiple PivotTables Share the Same Cache

If you created multiple PivotTables from the same source range, they share one PivotCache. Deleting items in one PivotTable does not clear the cache for the others. Use the VBA macro from Method 2 and apply it to all sheets. Alternatively, right-click each PivotTable and choose PivotTable Options > Data tab. Set Number of items to retain per field to None. This setting forces each PivotTable to discard orphaned items when refreshed.

Quick Comparison: Deferred Layout vs VBA Cache Clear

Item	Deferred Layout Update	VBA Macro (MissingItemsLimit)
Skill level required	Beginner	Intermediate
Time to execute	30 seconds	10 seconds after setup
Affects all PivotTables on sheet	One at a time	All at once
Requires macro-enabled file	No	Yes (.xlsm)
Permanently prevents stale items	No, must repeat after each data change	No, must run macro after each data change

Preventing Stale Filter Items in Future PivotTables

To stop old items from reappearing, you can adjust the source data structure. Convert your source range into an Excel table by selecting the range and pressing Ctrl + T. Tables automatically expand and contract as you add or delete rows. When you refresh the PivotTable, Excel reads the table’s current row set and caches only the items that exist. This eliminates the need to manually clear the cache after every data change.

Another preventive measure is to set the PivotTable option Number of items to retain per field to None. Right-click the PivotTable, choose PivotTable Options, go to the Data tab, and set the drop-down to None. This tells Excel not to cache historical items at all. Combined with a table-based source, this setting keeps the filter list clean automatically.

Finally, always refresh the PivotTable using the Refresh All button on the Data tab instead of the Ctrl + Alt + F5 shortcut. Refresh All forces Excel to rebuild the cache for all PivotTables in the workbook, not just the active one. This reduces the chance of stale items lingering in shared caches.