Formatting and refresh Updated November 2026

Change data source

Click in the pivot, open PivotTable Analyze, Change Data Source, select the new range (or type the Table name) and click OK, then refresh. Converting the source to a Table with Ctrl+T and pointing the pivot at the Table name means you never have to do this again.

Syntax

PivotTable Analyze > Change Data Source > Table/Range: Table1 or Sheet1!$A$1:$F$500 > OK

Part	What it is
`Table/Range`	A range reference or a Table name such as Sales
`Table name`	Expands automatically when rows are added under it
`Whole columns`	Sheet1!$A:$F works but adds a (blank) item and slows refresh

Examples

Change Data Source > Sheet1!$A$1:$F$800 (was $A$1:$F$500) > OK > Refresh

Result: The pivot now includes rows 501 to 800.

Click in the source > Ctrl+T > OK > name it Sales
Change Data Source > Sales

Result: The pivot reads the Table; new rows are included on the next refresh with no further changes.

Change Data Source > Use an external data source > Choose Connection

Result: The pivot can point at a query or another workbook instead of a sheet range.

Common mistakes

The reference is not valid error: the sheet name has spaces and needs quotes, as in 'Sales Data'!$A$1:$F$500, or the sheet was renamed.
A (blank) item appears after changing to whole columns: the empty rows are included; use a Table or an exact range.
Other pivots did not change: pivots that share a cache update together, but ones built separately need their own change.

Practice Change data source

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

How do I see what range a pivot table uses?

PivotTable Analyze, Change Data Source shows the current range in the dialog without changing it.

Can I change the data source to a different sheet?

Yes. Type or select the new range on the other sheet; the field names must match or the layout will be lost for fields that no longer exist.

Refresh a pivot tableRefresh a pivot table after the source changes with right-click Refresh or Alt+F5, refresh every pivot with Ctrl+Alt+F5, and set the file to refresh on open.
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.
Create a pivot tableCreate a pivot table in Excel from a range or table, pick a new worksheet, then drag fields into Rows and Values. Steps, shortcut, data rules and fixes.