Pivot Table Remove Deleted Items: Full Guide

To remove deleted items from a pivot table, you need to clear or refresh the pivot table cache, because the cache stores a snapshot of all data that was ever included, even after you delete rows from the source. This article explains why deleted items linger, how to remove them in Excel and Google Sheets, and how to prevent them from reappearing.

Why Deleted Items Still Appear

When you create a pivot table, Excel (and Google Sheets) takes a snapshot of the source data and stores it in a hidden structure called the pivot cache. This cache is what the pivot table actually reads from. If you delete rows from your source data, the cache still holds the old values until you refresh the pivot table. Even after a refresh, items that were once in the data but are now absent can remain in the filter dropdowns because the cache retains them as “orphaned� items. This behavior is by design—it preserves selections and formatting, but it often confuses users who expect the pivot to mirror the source exactly.

How to Remove Deleted Items in Excel

In Excel, there are several methods to clear deleted items from a pivot table. The quickest is to refresh the pivot table, but that alone may not remove all orphaned items. Here’s a step-by-step approach:

1. Select any cell inside the pivot table.
2. Go to the PivotTable Analyze tab (or Options in older versions).
3. Click Refresh – this updates the cache with current source data. Deleted rows will disappear, but items that were once in the data may still appear in filters.
4. If items still show, right-click the pivot table and choose PivotTable Options.
5. On the Data tab, set Number of items to retain per field to None. This tells Excel not to keep old items.
6. Click OK and refresh again. The old items should now be gone from the filter dropdowns.

If the above doesn’t work, you may need to clear the entire pivot cache. This is done by copying the pivot table and pasting it as values, then deleting the original pivot table and creating a new one. This is a more drastic step but guarantees a clean cache.

How to Remove Deleted Items in Google Sheets

Google Sheets pivot tables behave slightly differently. They don’t have a persistent cache in the same way; they reference the source range directly. However, they can still show stale items if the source range is not updated or if the pivot table is not refreshed. To remove deleted items:

1. Click anywhere on the pivot table.
2. Click the Refresh button in the toolbar (or press Ctrl+R / Cmd+R).
3. If items persist, check the source range. If you deleted rows, the named range or range reference may still include empty cells. Adjust the range to exclude them.
4. If the pivot table still shows old items, try deleting the pivot table and recreating it. This is often the simplest fix in Google Sheets.

Unlike Excel, Google Sheets does not have a “retain items� setting, so refreshing usually clears everything. The main issue is that the source range might be manually set to a fixed range that includes extra rows.

How to Prevent Deleted Items from Reappearing

Prevention is better than cure. Here are steps to avoid the problem altogether:

• Use Excel Tables (ListObjects) for your source data. When you convert a range to a table, the pivot table automatically picks up new rows and excludes deleted ones on refresh. This is the most reliable method.
• Set “Number of items to retain per fieldâ€� to None in Excel’s PivotTable Options. This stops Excel from storing old items.
• Refresh your pivot table regularly after any data changes. This keeps the cache in sync.
• Use dynamic named ranges in older Excel versions. These automatically expand and contract with data changes, but they require manual setup.
• In Google Sheets, always use a named range that covers only the data area, and avoid leaving blank rows within the range.

Common Problems and Misconceptions

One common misconception is that refreshing the pivot table will always remove deleted items. In Excel, this is not true if the cache retains old items. Another issue is that the “Number of items to retain per field� setting is often set to “Automatic� by default, which can keep up to 256 items per field. This can cause memory bloat and stale items. Some users also think that deleting the source data file will clear the cache, but the cache is stored within the workbook, so the items remain until you clear them. Another problem is that if you have multiple pivot tables based on the same data, they share the same cache. Clearing items from one pivot table may not affect the others unless you clear the cache for all.

How to Clear the Pivot Cache Completely

If you want to start fresh, you can clear the pivot cache entirely. In Excel, there is no direct “clear cache� button, but you can do it by:

1. Select the pivot table and copy it (Ctrl+C).
2. Paste it as values (right-click > Paste Special > Values). This removes the pivot table but keeps the data.
3. Delete the original pivot table.
4. Create a new pivot table from the source data. This creates a fresh cache with only current data.

In Google Sheets, you can simply delete the pivot table and create a new one. There is no separate cache to clear.

Alternatives to Manual Removal

If you frequently deal with deleted items, consider using VBA macros to automate the cleanup. A simple macro can refresh all pivot tables and set the “retain items� option to None. However, VBA is not available in Google Sheets. In Google Sheets, you can use Apps Script to refresh pivot tables, but it’s less common. Another alternative is to use Power Pivot (Excel’s data modeling tool) which handles data differently and doesn’t have the same stale item issue. However, Power Pivot requires the data to be loaded into the data model, which may be overkill for simple tasks.

Frequently Asked Questions

Why do deleted items still show in the filter dropdown? Because the pivot cache retains a snapshot of all items that were ever in the source data. Even after refresh, the cache keeps old items unless you change the “retain items� setting.

How do I refresh a pivot table without losing my formatting? Right-click the pivot table and choose Refresh. Formatting is preserved. If you want to keep the layout, just refresh normally.

Can I remove deleted items from a pivot table in Excel Online? Yes, the same methods apply. Excel Online supports refreshing and changing the retain items setting, though the interface may be slightly different.

What if I have multiple pivot tables from the same data? They share the same cache. Changing the retain items setting for one pivot table will affect all. To clear items, you may need to refresh all, or clear the cache for all.

Does deleting the source sheet remove the items? No, the cache is stored in the workbook, not in the source sheet. Even if you delete the source data, the pivot table will still show the old items until you refresh or clear the cache.

Is there a way to automatically remove deleted items on every refresh? Yes, set “Number of items to retain per field� to None in Excel. In Google Sheets, refreshing automatically removes deleted items as long as the source range is correct.

Related

• Mass Effect Crossover: Platform & Edition Guide
• Samsung Galaxy S21 Press Renders Leak?
• Affordable Sports Car: Porsche Handling, Lexus Reliability

in t | Finish time | min | 50 | 0
in d | Distance | km | 10 | 0
out pace | Average pace | min/km | t / d | 2
out speed | Average speed | km/h | d / (t / 60) | 2