Pivot Table: “Data Source Reference Is Not Valid”

You click Refresh on a pivot table and Excel answers with a single line: “Data source reference is not valid.” The table may still show yesterday’s numbers, so it is tempting to assume nothing is wrong. Something is wrong: that pivot is now a snapshot and will not update again until the pointer is fixed.

The message names a broken pointer, not broken data. Finding out what the pivot was pointed at is most of the repair.

Why the reference breaks

A pivot table does not own data. It owns a cache — a compressed copy of the rows it last read — plus a source reference. That reference is one of four things: a range such as Sheet1!$A$1:$F$900, an Excel Table name, a defined name, or a connection to an external file.

On refresh, Excel resolves the reference again and re-reads the source. If the reference no longer resolves, refresh stops before the cache is touched, which is why your old numbers stay visible on screen.

The usual causes, roughly in order of frequency:

• The source sheet was deleted or renamed, so the range now points at #REF!.
• The source was converted to an Excel Table and the table was renamed later, for example Table1 to tblSales.
• A defined name in Formulas → Name Manager was deleted, or its scope changed from workbook to a single sheet.
• The source sits in another workbook that has been moved, renamed, or is not open.
• The workbook is still in legacy Shared Workbook mode, which never supported refresh.

Before changing anything, find out which one applies. PivotTable Analyze → Change Data Source opens a dialog showing the current reference in its Table/Range box. Read it. If it says #REF!, skip to Fix 3. If it names a table, go to Fix 2.

Fix 1 — Re-point the pivot at its range

1. Click a cell inside the pivot, then PivotTable Analyze → Change Data Source.
2. Clear the Table/Range box and select the source range again, header row included. The whole range has to sit on one sheet.
3. Click OK, then Refresh.

Excel keeps calculated fields, value filters, and layout as long as the field names are unchanged. A renamed header is the exception: that field drops out and has to be dragged back in. Expect it — it is the usual surprise after an otherwise successful repair.

Fix 2 — Repoint a renamed table or name

If the source is an Excel Table, the reference is the table name alone, and renaming the table breaks it.

1. Click the source table, open Table Design, and read Table Name.
2. Return to the pivot and open Change Data Source again.
3. Type the exact table name, for example tblSales, into the Table/Range box and press OK.

For a defined name, open Formulas → Name Manager (Ctrl+F3), confirm the name still exists, and check the Scope column. A name scoped to a single sheet is invisible to a pivot built on another sheet, which produces the same error with no obvious cause.

Fix 3 — Rebuild the source sheet

A deleted sheet cannot be undeleted in the same session; Ctrl+Z covers only the deletion itself. There are two ways back:

• If the reference reads #REF!, insert a new sheet, paste the data in, and give it the original sheet name. Then run Fix 1 and reselect the range.
• If the sheet was merely renamed, renaming it back and refreshing is enough — no reselection needed.

Keep the header row in row 1 of the same columns; a cache matches fields by position as well as by name, and a shifted header row produces blank column labels after refresh.

Fix 4 — Check external links and connections

When the source is another file, the pivot depends on a link Excel stores as a full path.

Open Data → Queries & Connections, right-click the connection, and choose Properties to see the file it expects. Then use Data → Edit Links to point the link at the file’s current location. Network shares deserve suspicion here: a mapped drive that changes letter breaks the reference even though the file never moved.

Fix 5 — Leave Shared Workbook mode

Legacy shared workbooks cannot refresh pivot tables at all. On the Review tab, open Share Workbook and clear Allow changes by more than one user. Excel asks you to save and reopen the file; do that, then refresh.

The cost is real: turning sharing off ends simultaneous editing for everyone in the file. Microsoft 365 replaced this feature with co-authoring on OneDrive or SharePoint, so on a current build the dialog may be missing entirely.

Fix 6 — Rebuild the pivot when the cache is corrupt

If the reference reads correctly and refresh still fails, the cache itself is damaged. Repairing a cache is not supported, so rebuild it:

1. Copy the pivot’s current values with Ctrl+C → Paste Special → Values onto a spare sheet, for reference.
2. Insert a new pivot from the same source range.
3. Rebuild the row, column, and value layout, then delete the old table.

That is quicker than it sounds for a small pivot, and clears every stale pointer at once. If the workbook also shows a repair prompt on open, the damage is in the package rather than in the pivot — see “We found a problem with some content”.

When it is not worth fixing

If the source arrived as a Google Sheets export, check the export before rebuilding: a date column that lands as text refreshes fine but groups wrongly, as covered in Google Sheets export breaks Excel formulas.

Reconsider repair when the pivot carries custom calculated items, manual grouping, or a layout someone spent a day on. Rebuilding drops all of it, and the person who built the table usually knows the field logic better than any recovery route. Where the file will not open at all, start with Excel file won’t open instead — pivot errors assume the workbook loads.

FAQ

Does Refresh All make it worse?

No, but it hides the cause. Data → Refresh All stops at the first broken pivot, so the workbook’s other connections are never tested. Refresh the failing table on its own while you diagnose.

The pivot works on another computer. Why?

The source is most likely an external file referenced by a local path, or a mapped drive that exists on one machine and not the other. The reference lives in the workbook, but resolves at refresh time on whichever machine refreshes.

Can I keep the old numbers while I fix the source?

Yes. Copy the table as values first. A cache is only rebuilt on refresh, so an unrefreshed pivot keeps displaying what it last read.

If the source range is gone and the pivot is the only place those numbers still exist, send the workbook before refreshing anything.