Excel Pivot Table Not Refreshing: Causes and Fixes

Pivot tables never update automatically when the source data changes — you have to trigger a refresh yourself — and the second most common cause of stale numbers is a source range that didn't grow when new rows were added underneath it. Both are fixable in under a minute once you know which one you're hitting.

Why doesn't a pivot table update automatically?

Excel pivot tables run on a separate internal cache of the source data, built at the moment you create or last refresh the pivot — editing the source sheet doesn't touch that cache, so the pivot keeps showing whatever it had cached until you explicitly refresh it.

Right-click anywhere in the pivot > Refresh
PivotTable Analyze > Refresh
Keyboard: Alt, then A, then R, then A (Refresh All)

How do I make a pivot table refresh automatically when I open the file?

Turn on "Refresh data when opening the file" in PivotTable Options — this refreshes the cache on open, but it still won't catch changes made while the workbook is open without a manual refresh or a VBA/Office Script trigger.

Right-click pivot > PivotTable Options > Data tab
> check "Refresh data when opening the file"

There is no native "live" auto-refresh while the file is open; if you need that, the pivot's source needs to be a Power Query connection with a scheduled refresh (Power BI/Excel Online with a data source that supports it), or a small VBA Worksheet_Change macro that calls ActiveSheet.PivotTables("Name").RefreshTable.

Why doesn't the pivot table include my new rows?

If the source was selected as a fixed cell range (e.g. A1:D500) rather than a Table, adding row 501 puts it outside that range entirely — refreshing the pivot re-reads A1:D500 and simply never sees row 501. Converting the source to a Table fixes this permanently because a Table's named range grows with the data.

1. Click anywhere in the source data
2. Ctrl+T > confirm "My table has headers"
3. Right-click the pivot > Change Data Source
4. Confirm the source now reads the Table's name (e.g. Table1[#All])
   instead of a fixed range like Sheet1!$A$1:$D$500

See why Excel Tables beat plain ranges for the mechanics of that auto-expansion.

Why does refreshing throw an error or reset my formatting?

A refresh error usually means the source range or connection no longer exists (a renamed sheet, a moved file, a deleted named range) — check PivotTable Analyze > Change Data Source to confirm it still points somewhere valid. If formatting resets on refresh, tick "Preserve cell formatting on update" and "Autofit column widths on update" (uncheck the latter if it's the one resetting your widths) in PivotTable Options > Layout & Format.

What is GETPIVOTDATA and why does it break after I refresh or rearrange?

GETPIVOTDATA is the formula Excel auto-generates when you type = and then click a cell inside a pivot table, and it pulls a value by field/item name rather than by cell address — so it keeps working even if the pivot's layout shifts, but it returns #REF! if the field or item name you referenced no longer exists after a refresh (e.g. a filtered-out region, or a renamed field).

=GETPIVOTDATA("Revenue", $A$3, "Region", "West")

Breaks with #REF! if:
- "West" is filtered out of the pivot after refresh
- The "Region" field is removed from Rows/Columns
- The pivot table itself is deleted or moved

If you'd rather reference pivot cells by plain address (like a normal formula) instead of by name, turn it off before clicking into the pivot: File > Options > Formulas > uncheck "Use GetPivotData functions for PivotTable references," or toggle it per-workbook via PivotTable Analyze > Options dropdown > Generate GetPivotData.

Pivot table stale data: quick diagnosis

Match the symptom you're seeing to the cause below — the fix for "nothing updated" is different from the fix for "new rows are missing."

Symptom	Likely cause	Fix
Numbers unchanged after editing source	Pivot not refreshed	Right-click > Refresh, or Ctrl+Alt+F5
New rows missing even after refresh	Fixed source range didn't grow	Convert source to a Table (Ctrl+T)
Refresh throws an error	Source range/connection no longer valid	PivotTable Analyze > Change Data Source
GETPIVOTDATA formula shows #REF!	Referenced field/item removed by the last refresh	Update the formula's field/item names to match the current layout