Formatting and refresh Updated November 2026

Refresh a pivot table

Right-click anywhere in the pivot and choose Refresh, or press Alt+F5. Data, Refresh All (Ctrl+Alt+F5) updates every pivot and query in the workbook. To refresh automatically when the file opens, tick Refresh data when opening the file under PivotTable Options, Data.

Syntax

Right-click pivot > Refresh | Alt+F5 | Data > Refresh All (Ctrl+Alt+F5)

Part	What it is
`Refresh`	Re-reads the current source range for this pivot and any pivots sharing its cache
`Refresh All`	Every pivot, query and external connection in the workbook
`Refresh data when opening the file`	PivotTable Options, Data tab
`Preserve cell formatting on update`	PivotTable Options, Layout & Format; keeps manual formats after refresh

Examples

Add 20 rows inside the Table that feeds the pivot > Alt+F5

Result: The totals include the new rows; no change to the data source needed because the Table expanded.

Data > Refresh All

Result: All pivots on all sheets update, including those built on Power Query.

PivotTable Options > Data > tick Refresh data when opening the file

Result: The pivot refreshes each time the workbook is opened, so readers see current numbers.

Common mistakes

Refresh does nothing: the new rows sit below a fixed range, so use Change Data Source or convert the source to a Table.
Column widths jump every refresh: untick Autofit column widths on update in PivotTable Options.
Old items linger in filter dropdowns: set Number of items to retain per field to None under PivotTable Options, Data, then refresh.
Refresh is slow: the source range includes a million empty rows; limit it to the data.

Practice Refresh a pivot table

These drills check your answer on live data, free, no account needed.

Revenue by regionBuild a pivot that shows total Revenue for each Region.

Questions

Can a pivot table refresh automatically?

On file open, yes (PivotTable Options, Data). For refresh on every change you need a short VBA Worksheet_Change macro. Google Sheets pivots refresh on their own.

Why do I lose number formatting after refresh?

Formatting applied to cells is not tied to the field. Set the format with Value Field Settings, Number Format, and keep Preserve cell formatting on update ticked.

Change data sourceUpdate the range a pivot table reads from with PivotTable Analyze, Change Data Source, or convert the source to an Excel Table so it grows by itself.
Pivot table not updatingA pivot table does not update on its own. The usual causes: no refresh, new rows outside the source range, retained old items, text numbers. Checks and fixes.
Pivot table shortcutsThe keyboard shortcuts that matter for pivot tables: insert, refresh, refresh all, group and ungroup, open the field list, select the whole pivot.
Number format in a pivot tableFormat pivot table values so the format survives a refresh: use Value Field Settings, Number Format rather than the Home ribbon. Currency and percent.