How to Refresh a Pivot Table in Excel (2026 Guide)
Home›Pivot Tables›Refresh a Pivot Table
ExcelHow-To⏱ 2 min read

How to Refresh a Pivot Table in Excel (2026)

Pivot tables cache their source data. When the source changes, the pivot doesn't update until you refresh. This guide covers the fastest ways to refresh — one pivot, all pivots, or automatically on file open.

⚡ Quick Answer

Right-click the pivot → Refresh. Or press Alt + F5. To refresh all pivots in the workbook: Data → Refresh All, or Ctrl + Alt + F5.

Method 1: Refresh a single pivot

  1. Right-click inside the pivot → Refresh

    Fastest single-pivot refresh.

  2. Or press Alt + F5

    With cursor inside the pivot. Same result.

  3. Or use the ribbon

    PivotTable Analyze → Refresh (top of the ribbon). Dropdown offers Refresh (current) and Refresh All.

Method 2: Refresh every pivot in the workbook

When multiple pivots share source data and everything needs to update together.

  1. Data tab → Refresh All

    Refreshes every pivot, PivotChart, Power Query, and external connection in the file.

  2. Or press Ctrl + Alt + F5

    Same result, faster.

Method 3: Auto-refresh on file open

The closest thing to automatic refresh Excel offers.

  1. Click inside a pivot table

    Any pivot in the workbook.

  2. PivotTable Analyze → Options → Data tab

    Options button on the left of the ribbon.

  3. Tick "Refresh data when opening the file"

    Every time the workbook opens, this pivot refreshes.

  4. Repeat for other pivots or

    Since data source is shared, refreshing one usually refreshes all connected pivots.

Auto-refresh on interval

Excel doesn't natively support timed auto-refresh for standard pivots. For Power Query connections: Data → Queries & Connections → Properties → tick "Refresh every N minutes". Standard pivots on Excel tables need manual or on-open refresh.

Common refresh problems

Problem: Refresh completes but new rows don't appear

Cause: Source range doesn't include the new rows.

Fix: Two options.

  • Best fix: Convert source to Excel Table (Ctrl + T). Table auto-expands, pivot picks up new rows on refresh.
  • Quick fix: PivotTable Analyze → Change Data Source → expand the range to include new rows.

Problem: "PivotTable field name is not valid"

Cause: A source column has a blank header, or a column was renamed/deleted.

Fix: Ensure every source column has a unique, non-blank header. Restore any missing columns.

Problem: Formatting resets on refresh

Cause: Default pivot settings wipe formatting on refresh.

Fix: PivotTable Analyze → Options → Layout & Format tab. Tick "Preserve cell formatting on update". Untick "Autofit column widths on update".

Problem: Refresh is very slow

Cause: Large source, many pivots, or Power Query refreshes running in sequence.

Fix: Use Excel Tables (indexed). Consolidate multiple pivots to one shared cache. For heavy loads, move to Power Pivot's data model (in-memory columnar engine — significantly faster).

Problem: Number format doesn't stick

Cause: Used Format Cells instead of Number Format.

Fix: Right-click value cell → Number Format. Not Format Cells. Number Format is field-level and persists through refresh.

Refreshing with Power Query

If your pivot's source is a Power Query result, refreshing the pivot triggers the query to re-run first, then the pivot updates from the fresh data. One Refresh All handles the whole chain.

Set query refresh options

Data → Queries & Connections → right-click a query → Properties. Options:

  • Refresh every N minutes (background refresh)
  • Refresh data when opening the file
  • Enable background refresh (query runs while you work)

Refresh in VBA

For scheduled or event-driven refresh:

' Refresh one specific pivot
ActiveSheet.PivotTables("PivotTable1").RefreshTable

' Refresh all pivots in workbook
ActiveWorkbook.RefreshAll

' Refresh on workbook open (put in ThisWorkbook module)
Private Sub Workbook_Open()
    ActiveWorkbook.RefreshAll
End Sub
Excel Wizard

Refresh chains, managed

Refreshing pivots in workbooks with Power Query, multiple data sources, and dependent pivots takes attention. Excel Wizard can inspect refresh chains and refresh in the right order — or diagnose why a refresh isn't picking up expected changes.

Install Excel Wizard →

Frequently asked questions

How do I refresh a pivot table in Excel?

Right-click the pivot → Refresh. Or press Alt + F5. To refresh every pivot: Data → Refresh All, or Ctrl + Alt + F5.

How do I set a pivot table to refresh automatically?

PivotTable Analyze → Options → Data tab → tick 'Refresh data when opening the file'. Pivots refresh every time the workbook opens.

Why doesn't my pivot show new rows after refreshing?

Source range doesn't include them. Convert source to Excel Table (Ctrl + T) — auto-expands. Or PivotTable Analyze → Change Data Source → expand manually.

Does refreshing preserve my formatting?

Only if you tick 'Preserve cell formatting on update' and untick 'Autofit column widths on update' in PivotTable Options → Layout & Format tab.