Excel raises a reference error when a formula, defined name, table formula, chart source, or linked object points to something that is no longer a real cell, range, or valid name. If you leave it alone, formulas can stop returning the intended result, and linked objects may stop updating.

What the error means

What the error means
An Excel error message appears over a spreadsheet grid

This message means Excel found a spot where it expected a valid reference, not plain text. A valid reference can be a single cell, a range such as B5:B15, a structured table reference, or a defined name that still points to real cells. Excel formulas start with =. If a sheet name is used, the sheet must still exist and the spelling must match exactly.

The fastest first check is simple: inspect the exact place where the error appears and decide whether it is a worksheet formula, a defined name, or a linked object such as a text box or chart.

Likely causes, ranked

  1. A missing, deleted, or mistyped cell reference.
  2. A defined name that now points to #REF! or a missing sheet.
  3. Wrong syntax in a table formula, chart source, or linked object.
  4. An external workbook link that still points to an old file or path.

If a formula references a range that was deleted or a sheet that was renamed, Excel flags the formula where the broken reference is used. That is why the fix starts with the exact formula text, not just the visible result.

Worksheet formulas: check the reference first

If the error appears in a normal cell, the formula usually contains a broken address, a missing sheet name, or a range written in the wrong form. Start by checking whether the formula contains the right cell address and whether any sheet name still exists. If the formula is long, use Formulas > Evaluate Formula to step through the expression and stop at the first broken reference.

Fix steps

  1. Select the cell that shows the error.
  2. Look at the formula bar and confirm that the formula starts with =.
  3. Check every range reference for a valid address, such as B5:B15, not plain text like "B5:B15".
  4. If the formula uses a sheet name, confirm the sheet still exists and the spelling matches exactly.
  5. Use Formulas > Evaluate Formula to step through the expression and stop at the first broken reference.

If a formula uses =SUM(B5:B15) and the source range was deleted, the missing range is the failure point. If the workbook is complex, copy the formula to a blank sheet and remove one part at a time until the bad reference is obvious.

Defined names: check Name Manager

When the message involves a defined name, Excel is trying to resolve a named range or named formula and cannot find a valid target. The place to inspect is Formulas > Name Manager. Look at the Refers to column first; a name that points to #REF! or to a sheet that no longer exists can trigger the error when the name is used in a formula or object source.

Fix steps: open Formulas Name Manager

  1. Open Formulas > Name Manager.
  2. Scan the Refers to column for #REF! or a missing sheet name.
  3. Select the broken name and note every formula or object that uses it.
  4. Delete the name only if nothing in the workbook still depends on it.

A defined name must still point to a real cell or range, not to text that only looks like one. If the name was meant to return a single value, it still needs a valid reference behind it.

Text box formulas: use a helper cell

A text box is a common place where this error can appear while trying to combine text with a reference. Build the text in a worksheet cell first, then link the text box to that cell or name. Do not type a worksheet-style expression directly into the object and expect it to behave like a normal formula.

Fix steps: create a helper cell

  1. Create a helper cell on the worksheet.
  2. Build the display string in that cell with a normal formula.
  3. If dates or numbers need formatting, use TEXT in the helper cell, for example =TEXT(A1,"dd/mm/yyyy")&" to "&TEXT(B1,"dd/mm/yyyy"). Microsoft Support recommends using the TEXT function for this kind of display string.
  4. Select the text box and link it to the helper cell or to a defined name that points to that cell.
  5. Remove any direct concatenation typed into the text box itself.

Why does a text box formula work until you add quoted text? Because the object often accepts a plain linked reference, then fails when the content becomes a mixed string. Move the assembly into a worksheet cell, then link the object to that result.

Can you concatenate text and a cell reference in an Excel text box? Build the final sentence in a helper cell instead, then use the linked cell as the text source. That keeps the reference real and lets TEXT format dates or numbers before display.

Reference error decision table

Reference error decision table
An Excel worksheet shows formula errors and an error menu

This table helps you go straight to the object that is likely failing. Start with the row that matches where the message appears, then check the exact place in Excel shown in the third column. Use the safest fix first, and do not change multiple sources at once.

Where the error appears Likely cause Exact place to check in Excel Safest fix
Worksheet formula A missing cell, range, or sheet reference Formula bar and Formulas > Evaluate Formula Repair the address, restore the sheet name, or replace the broken range
Text box Direct concatenation typed into the object instead of a linked result Text box content and the linked cell or name source Build the text in a helper cell, use TEXT for dates or numbers, then link the box to that cell
Table formula Wrong structured reference syntax or a column name that does not match Formula inside the table and the exact table and column names Rewrite the structured reference so it matches the table names exactly
Chart Bad series, axis, label, or title reference The chart source formulas for series, axis, labels, and title Edit the source reference and point it to valid cells or ranges
PivotTable Invalid data source range or source fields that no longer match PivotTable Tools > Analyze > Change Data Source Reset the source range, then refresh the PivotTable
Defined name Name points to #REF! or a missing sheet Formulas > Name Manager, especially the Refers to column Edit the name so it points to a valid reference or delete the broken name
External link Workbook-to-workbook reference no longer resolves Workbook links and connection sources in the workbook Update the source workbook path or replace the link with a valid one

These objects can hide the bad reference because the visible error is often a symptom, not the source. Table formulas use structured references, so a column name typo can trigger the same message even when the syntax looks close. Charts can store broken series or axis references. PivotTables can keep an invalid source range. External links can keep pointing to a workbook that no longer matches the formula.

Fix steps: for a table formula, check

  1. For a table formula, check the structured reference against the table and column names exactly.
  2. If the formula sits inside a table, confirm whether it should use a row context reference or a full-column structured reference.
  3. For a chart, select the chart and inspect the series formula, axis formula, labels, and title source.
  4. For a PivotTable, open PivotTable Tools > Analyze > Change Data Source and confirm the source range is valid, then refresh the PivotTable.
  5. For workbook links, inspect the linked source workbook and update the reference if the file moved.

The message can involve workbook links, range names, and cell references. That means the message is not limited to one kind of formula. If the workbook contains many charts or linked objects, check those sources before rewriting formulas that are already correct.

Still not working?

Still not working?
An Excel worksheet shows formula errors and an error menu

If the error survives the obvious fixes, isolate it. Copy the formula to a blank sheet and remove one part at a time until the broken reference is obvious. If the problem appears only in a chart, PivotTable, or linked object, correct the source and then refresh that object. That is often faster than searching the whole workbook cell by cell.

Next escalation

  1. Copy the formula or object source into a blank sheet.
  2. Strip out one reference at a time until the message stops.
  3. Return to the original workbook and fix the source reference, not the visible symptom.
  4. Refresh the chart or PivotTable after correcting the source.
  5. If a workbook link keeps failing, replace it with a direct reference that points to the current file path.

Prevention is simple when the workbook has many moving parts: keep named ranges valid, use helper cells for text displays, and avoid typing object content as if every box were a worksheet cell. That reduces repeat errors when sheet names, table columns, or source files change.

Frequently asked questions

Why does Excel say this formula is missing a range reference or a defined name?

Excel gives that message when it cannot resolve a cell, range, or name where the formula expects one. Check whether the formula starts with =, then inspect the referenced cells, sheet names, and any defined name in Formulas > Name Manager. Broken workbook links can trigger the same result.

How do I fix this formula is missing a range reference error in a text box?

Put the display text in a worksheet helper cell first, then link the text box to that cell or a defined name. If dates or numbers are involved, use TEXT to format them before linking. Do not type worksheet-style concatenation directly into the text box.

Why does a text box formula work until I add quoted text?

The object can often display a linked reference, but quoted text changes the entry into a mixed string that the box does not evaluate like a normal worksheet formula. Move the expression to a helper cell, build the final string there, and link the text box to the result.

Can I concatenate text and a cell reference in an Excel text box?

Use a worksheet cell for the concatenation, not the text box itself. A safe pattern is =TEXT(A1,"dd/mm/yyyy")&" to "&TEXT(B1,"dd/mm/yyyy") in a helper cell, then link the box to that cell. That keeps the reference valid and the output readable.

What does Excel mean by a defined name in this error?

A defined name is a named range or named formula that Excel can use in place of a direct address. If the name points to #REF! or a missing sheet, the formula that uses it can fail. Open Formulas > Name Manager and inspect the Refers to column.

Could a chart or pivot table cause formula reference errors in Excel?

Yes. A chart can hide a bad series formula, axis formula, label source, or title source, and a PivotTable can keep an invalid data source. Check PivotTable Tools > Analyze > Change Data Source and inspect chart source formulas after you fix the underlying range.