Fixes / Excel · Checked September 30, 2026

Excel "Reference isn't valid": what it means and how to fix it

Short answer

Excel shows "Reference isn't valid" when something in the workbook, usually a PivotTable source, a named range or a typed reference, points to a sheet, table or range that no longer exists or can't be read. The most common fix is to open Formulas > Name Manager, find any name that shows #REF! in its Refers To column, and correct or delete it.

What this message means

Excel stores many references outside of normal cells. PivotTables remember their source range or table, named ranges store an address, hyperlinks and Go To use an address, and form controls and Goal Seek point at specific cells. When one of those addresses breaks (because a sheet was deleted or renamed, a table was removed during file repair, or the file name contains characters Excel can't use in a reference), Excel can't resolve it and shows "Reference isn't valid." The data itself is usually fine. You only need to find the reference that broke and point it at the right place again.

Common causes

Cause	How to tell
A named range points to a deleted sheet or range	In Formulas > Name Manager, one or more names show #REF! (for example =#REF!$A$1:$D$200) in the Refers To column. The error often appears when you refresh a PivotTable, use a drop-down list or print.
The PivotTable's source table or range no longer exists	The error appears when you click Refresh or open Change Data Source. The Table/Range box shows a table name or sheet that you can't find in the workbook, often after Excel repaired the file on open.
Square brackets or other invalid characters in the file name	The file name looks like Report[1].xlsx or Sales (2)[1].xlsx, typical of files opened straight from an email or browser. Creating or refreshing a PivotTable fails with "Reference isn't valid" or "Data source reference is not valid."
A hyperlink, Go To entry or chart points at a sheet that was renamed or deleted	The message appears right after you click a hyperlink or press OK in the Go To (F5) box. Hovering over the hyperlink shows a sheet name that isn't among your sheet tabs.
You typed into a text box or control while Design Mode was on	The Developer tab's Design Mode button is highlighted, and what you typed went into the formula bar instead of the text box. The error repeats every time you press Enter.
Goal Seek was given the wrong kind of cell	The error appears after clicking OK in Data > What-If Analysis > Goal Seek. The Set cell contains a plain value instead of a formula, or the By changing cell contains a formula instead of a value.

How to fix it

1. Get out of a repeating error loop first

Click OK on the message, then press Esc or click the X (Cancel) button to the left of the formula bar so Excel stops trying to accept the entry.
If the message keeps coming back and you can't click anything, save isn't possible: close Excel. On Windows press Ctrl+Shift+Esc, select Microsoft Excel on the Processes tab and click End task. On a Mac press Cmd+Option+Esc, choose Microsoft Excel and click Force Quit.
Reopen the file. If AutoRecover offers a newer copy, open it and save it under a new name.
If the loop started while you were typing into a text box, go to Developer > Design Mode and click it so it's no longer highlighted, then try again.

2. Fix or delete broken named ranges

Windows: Formulas > Name Manager (Ctrl+F3). Mac: Formulas > Defined Names > Name Manager.

Open Name Manager from the Formulas tab.
On Windows, click Filter > Names with Errors to show only broken names. On a Mac, scan the Refers To / Value column for #REF!.
Select a broken name. If you still need it, click in the Refers to box, delete the #REF! part, select the correct range on the sheet, and click the check mark to confirm.
If the name is no longer needed (common with Print_Area or names copied in from other workbooks), click Delete.
Close Name Manager and retry the action that caused the error.

3. Point the PivotTable at its data again

Excel for Windows and Mac desktop. The tab is called PivotTable Analyze in Microsoft 365 (Analyze in older versions). Excel for the web can't change a PivotTable's source.

Click any cell inside the PivotTable.
Go to PivotTable Analyze > Change Data Source > Change Data Source.
Note what is in the Table/Range box. If it names a table that no longer exists, go to your data, select it, press Ctrl+T (Cmd+T on Mac) to make it a table, and give it that name in Table Design > Table Name.
Or simply replace the Table/Range box with the correct range or table name (for example SalesData or Sheet1!$A$1:$F$500) and click OK.
Click Refresh to confirm the error is gone.

4. Rename the file and save it locally

Close the workbook.
In File Explorer (Windows) or Finder (Mac), rename the file to remove square brackets and other unusual characters, for example change Report[1].xlsx to Report.xlsx.
If the file was opened from an email attachment, a browser download or a read-only location, use File > Save As and save a copy to a normal local folder such as Documents.
Open the renamed copy and retry the PivotTable or refresh.

5. Repair broken hyperlinks and cell references

Right-click the hyperlink that triggers the error and choose Edit Hyperlink.
Under Place in This Document, pick the sheet that exists now and type a valid cell such as A1, then click OK. Or right-click and choose Remove Hyperlink.
If the error came from Go To (F5 or Ctrl+G), type the reference with the exact sheet name, using single quotes when the name contains spaces, for example 'Q3 Sales'!B2.
For Goal Seek, make sure the Set cell contains a formula and the By changing cell contains a typed number, not a formula.

6. Repair Office if the error appears on every workbook

Windows 10 and 11: Settings > Apps > Installed apps (Windows 11) or Apps & features (Windows 10). On Mac, reinstall Office from your Microsoft account instead.

Close all Office apps.
Open the installed apps list, find Microsoft 365 or Microsoft Office, and choose Modify.
Pick Quick Repair and click Repair.
If the error still appears in new, blank workbooks, run Online Repair from the same screen.

Next time, skip the search

Crade is an AI assistant for Mac and Windows that sees your screen. When a message like this pops up, ask “what does this mean?” and it explains the exact error in front of you, step by step.

Download Crade

Still not fixed?

Start Excel in Safe Mode (hold Ctrl while launching it on Windows, or run excel /safe) to rule out add-ins, then open the file again. If the error only affects one workbook, open it with File > Open, select the file, click the arrow next to Open and choose Open and Repair, or copy the sheets you need into a new workbook. For files on SharePoint or OneDrive, download a local copy first. If it is a company file with macros or data connections, send the workbook to whoever built it or your IT team, since a VBA procedure or external connection may be referring to a range that no longer exists.

Questions people ask

Why does Excel keep saying "Reference isn't valid" and won't let me close the message?

Excel is repeatedly trying to accept an entry that points to an invalid reference, often from a text box in Design Mode or a broken control. Press Esc or the X next to the formula bar, and if that fails, end Excel from Task Manager (Windows) or Force Quit (Mac) and reopen the file.

Why do I get "Reference isn't valid" when refreshing a PivotTable?

The PivotTable's source table, range or named range no longer exists or contains #REF!. Use PivotTable Analyze > Change Data Source to point it at the current data, or fix the named range in Name Manager.

Can a file name cause "Reference isn't valid"?

Yes. Microsoft confirms that square brackets in a workbook name (for example foo[1].xlsx) break PivotTable references. Rename the file without brackets and reopen it.

Is "Reference isn't valid" the same as the #REF! error?

They are related but different. #REF! shows inside a cell when a formula points to deleted cells, while "Reference isn't valid" is a pop-up that appears when a feature such as a PivotTable, name, hyperlink or Goal Seek can't find its target.

How do I find which reference is broken?

Open Name Manager and filter for Names with Errors (Windows) or look for #REF! in the Refers To column. Also note exactly what you clicked when the message appeared, since that feature (refresh, hyperlink, print, Go To) holds the broken reference.

Sources

Related errors