Excel Pivot Table Filter Showing Old Data? Fix It

Charts & pivot tables · Updated November 19, 2026

Yes. An Excel PivotTable filter can keep showing old item names after the source data has changed because the PivotTable cache may retain items that are no longer in the source. In Excel for Microsoft 365 on Windows, set the field’s retained items to None, then refresh the PivotTable; if that does not solve it, check the source range and refresh status too.

Why a PivotTable filter shows old items

A PivotTable uses a cache of source data to organize and summarize records. To avoid rebuilding every item list from scratch, Excel can retain item names that were previously present in a field. If a source row is deleted or a category is renamed, the old label can remain in the filter’s checklist even though it no longer appears in the current source data. For example, a Region filter might still list West after every West record has been removed.

Seeing an old name in the dropdown does not necessarily mean that Excel is still counting old records in the report. The filter list and the values currently summarized are related, but they are not the same thing. Check the PivotTable’s displayed rows and totals as well as the list of selectable items. If old totals or records remain in the report, investigate whether the PivotTable has been refreshed and whether its source includes the right records.

This behavior is different from a filter that intentionally hides a current item. It is also different from a source range that accidentally still contains the old record. Clearing retained items addresses remembered labels; it cannot correct inaccurate data that remains in the source.

Clear retained items in Excel for Windows

For a standard PivotTable based on a worksheet range or Excel table, use this sequence in Excel for Microsoft 365 on Windows:

  1. Click a cell inside the PivotTable so the PivotTable tabs appear on the ribbon.
  2. Choose PivotTable Analyze > Options > Options to open PivotTable Options. Depending on the ribbon layout, Options is a dropdown in the PivotTable group.
  3. Open the Data tab in the dialog.
  4. Find Number of items to retain per field and select None.
  5. Choose OK, then select PivotTable Analyze > Refresh. If you have several reports to update, use Data > Refresh All.

The key setting is None. It tells Excel not to keep obsolete field items in the cache. In most cases, the old labels disappear from the filter after the refresh. If they remain, close and reopen the filter menu and confirm that you changed the setting for the PivotTable you are inspecting.

You can also open PivotTable Options by right-clicking inside the PivotTable and choosing PivotTable Options. The dialog’s tabs and labels are the same place to look for the retention setting. On some ribbon layouts, the menu arrangement differs, but the setting is in the PivotTable Options dialog’s Data tab.

Refresh the report and check what changed

Changing the retention setting does not itself recalculate the PivotTable from a newly edited source. Refreshing is a separate step. With the PivotTable selected, choose PivotTable Analyze > Refresh, or use Data > Refresh All to refresh multiple workbook connections and reports. After refresh, open the filter dropdown again and look for the obsolete label.

It helps to distinguish two symptoms:

  • Old label appears only in the dropdown: retained cache items are a likely cause. Set retention to None and refresh.
  • Old rows or amounts appear in the PivotTable: refresh the report, then inspect the source records and source range. A real record may still be included.
  • New records are missing: check whether the source range includes them. A refresh cannot bring in rows that are outside a fixed source range.

For an external connection, refresh can depend on connection availability, credentials, or permissions. If Excel reports a refresh error, the PivotTable may continue to show its last successfully loaded data. Resolve the connection issue and refresh again; clearing retained field items is not a substitute for a successful data refresh.

Check the PivotTable source range

A PivotTable created from a fixed cell range does not always expand to include rows added below that range. For instance, if the source was A1:D100 and new records were entered on row 101, refreshing that PivotTable may still omit the new record. Choose PivotTable Analyze > Change Data Source to inspect the source reference. If necessary, update it to cover the new rows and refresh.

For a regularly growing worksheet list, converting the source to an Excel table can make expansion easier. Select a cell in the data and choose Insert > Table, or press Ctrl+T on Windows, then confirm the range and headers. A PivotTable based on that table can include rows added to the table when refreshed. On a Mac, the equivalent shortcut is Control+T in many Excel configurations; menu choices are also available from Insert. Verify that the new records are actually part of the table before refreshing.

Be careful not to expand the source to include unrelated notes, totals, or blank-formatted rows. The source should have one header row, consistent column headings, and records arranged in columns. A malformed range can introduce blank or unexpected items in the filter, which may look like a cache problem.

Other reasons a filter can look out of date

Not every unexpected filter entry is an obsolete cache item. Check these common possibilities before changing workbook structure:

  • A renamed category: The source may now use a new spelling while an old spelling still exists in some records, a second source table, or the PivotTable cache.
  • A blank or whitespace value: A cell that looks empty may contain a space or formula result. The PivotTable can treat it as a distinct item.
  • A manual filter selection: A saved selection can exclude a newly added item. Open the field dropdown and review its checked items, or clear the field filter and choose again.
  • Show items with no data: This display option can show items that have no matching values in the current report. It is not the same as retaining old items in the cache. Turning it off may affect what appears in the PivotTable, but it does not repair an incorrect source range.
  • A date grouping: Grouped dates can display months, quarters, or years based on the PivotTable’s date field. Check the underlying dates and grouping if the displayed period seems surprising.

Also inspect slicers and report filters if they are connected to the PivotTable. A slicer can leave the report restricted even when the field dropdown looks correct. Use the slicer’s clear-filter button, if appropriate, and remember that a slicer may control more than one PivotTable.

When several PivotTables share a cache

Workbooks often contain multiple PivotTables built from the same source. Excel can let those reports share a PivotCache, so changing a cache-related setting or refreshing the data can affect more than the selected report. After setting retained items to None, check other PivotTables that use the same source. Their filter lists may change too, and their summaries may update when refreshed.

If only one report appears wrong, first confirm that you selected the intended PivotTable before opening its options. Two PivotTables may look alike but use different sources or caches. You can inspect the source through PivotTable Analyze > Change Data Source. Avoid deleting and rebuilding every PivotTable as a first step: that can remove layout choices, formatting, calculated fields, or report connections that the workbook depends on.

For a workbook with many reports, refresh behavior is worth checking systematically. Use Data > Refresh All, then review any connection or refresh messages. If one report still differs, compare its source, filters, and field arrangement with a report that is correct rather than assuming all PivotTables share the same cache.

Data Model, Mac, and Excel for the web differences

The steps above apply to a conventional PivotTable with a worksheet-based PivotCache. A PivotTable created from the Excel Data Model or an OLAP source can have different cache behavior, and the Number of items to retain per field option may be unavailable or may not control the member list in the same way. In that case, refresh the model or connection, check the underlying query or source, and consult the options available for that PivotTable rather than expecting the standard cache setting to remove every old member.

Excel for Mac has PivotTable options and refresh commands, though the ribbon layout and dialog presentation can differ from Windows. Look for PivotTable Options and its Data tab; if the retention control is present, set it to None and refresh. Excel for the web supports working with many PivotTables, but desktop Excel provides the fuller set of PivotTable configuration controls. If the web interface does not expose the retention setting, open the workbook in Excel for Windows or Mac to make the change.

When the source is an external query or a shared data model, an old label may be coming from the data provider rather than from the ordinary worksheet PivotCache. In that situation, examine the query output or model dimension. The dropdown can only reflect the items supplied to the report and the behaviors supported by its source.

Prevent the problem from returning

Once the filter is clean, a few habits make stale lists less likely and make diagnosis easier:

  • Use an Excel table for a changing worksheet source, then refresh after adding or removing records.
  • Set retained items to None for standard PivotTables where obsolete labels should not remain available.
  • Use Data > Refresh All when a workbook contains multiple related reports or connections, and check for refresh errors.
  • Keep source columns consistent: one header per field, no duplicate headings, and no accidental spaces or leftover categories.
  • Document which source or connection each important PivotTable uses, especially in workbooks with many reports.

Some workbooks intentionally keep old categories available for historical comparison or for a report layout that expects them. In that case, retaining items may be useful rather than an error. Decide based on whether the filter should offer only current source values or also previously used labels; the None setting is appropriate when the goal is to remove obsolete entries.

Frequently Asked Questions

Why does my PivotTable filter show deleted items?
The PivotTable cache may retain field items that were present before you deleted the source records. Set Number of items to retain per field to None in PivotTable Options, then refresh the report.

Will refreshing a PivotTable remove old filter items?
Sometimes, but not always. Refresh updates the report from its source, while the cache can still retain old items. Set the retention option to None and refresh if deleted labels remain.

Why are new items missing from my PivotTable filter?
First refresh the PivotTable. If the item is still missing, check that the source range includes the new row, or that the row belongs to the Excel table used as the source. A refresh cannot include data outside the selected source.

Does Show items with no data remove old filter values?
No. It controls whether items with no matching data are displayed in the PivotTable layout. It is separate from the cache setting that retains old field items.

Can I clear old PivotTable items without rebuilding the report?
Usually. For a standard worksheet-based PivotTable, set retained items to None and refresh. Rebuilding should not be the first fix; Data Model and OLAP reports may need their source or model refreshed instead.

Related

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.