Excel #REF! Error: How to Fix Broken Cell References

To fix an Excel #REF! error, edit the formula that displays it and replace the invalid reference with the correct cell, range, sheet, or table reference. If the reference broke because you deleted cells or a worksheet, undo the deletion or restore the missing content when possible; otherwise, rebuild the formula using the intended data.

What the #REF! error means

#REF! means a formula contains a reference Excel cannot resolve. A reference tells Excel which cell or range to use, such as A2 or B2:B20. When that target is removed or a formula asks for a location that does not exist, Excel may replace the reference with #REF! rather than calculate a result.

The error appears in the cell containing the formula, but the break may have happened somewhere else. For example, a total on a summary sheet can show #REF! after someone deletes a column on a data sheet. Formulas that depend on the broken result may then show the same error, so one damaged reference can affect several sheets or reports.

#REF! is different from errors such as #N/A, which often means a lookup did not find a match, or #VALUE!, which can indicate an unsuitable value or argument. It is also different from a formula that returns an incorrect number: a broken reference is usually visible in the formula itself or its dependencies.

Common causes of a #REF! error

Deleted cells, rows, columns, or sheets: A formula that pointed to deleted content may lose its target. Deleting a whole worksheet can break formulas on other sheets that referred to it.
An invalid function argument: Some functions return #REF! when a row or column number asks for a position outside the supplied range.
Text that does not resolve to a reference: INDIRECT can return #REF! when its text argument does not describe a valid reference. It can also fail when used to refer to a closed external workbook.
Broken defined names or external links: A named range may refer to deleted cells, or a formula may contain a reference to a workbook or sheet that was removed or renamed.
Copied or edited formulas: A manual edit, paste, or formula change can leave an address invalid. Relative references can also shift when a formula is copied, although that more often produces a wrong result than #REF!.

Moving a cell is not always the same as deleting it. Excel often adjusts formulas when cells are moved or inserted, but deleting the referenced target can leave a formula without a valid destination. The exact result depends on the operation and on how the formula was written.

Find the broken reference before changing it

Start by selecting the cell that shows #REF! and reading the formula in the formula bar. In the formula, look for the literal text #REF!, which often appears where a cell address, sheet name, or range should be. Do not replace it with a guessed address until you know what the formula was meant to calculate.

Select the error cell and inspect the formula bar. Note the function, the remaining valid references, and any sheet or workbook names.
Check nearby formulas or the same formula in another row. A working version can reveal the intended pattern, especially when a formula was filled down a column.
Use Formulas > Trace Precedents to see cells that feed the selected formula. Use Formulas > Error Checking to inspect available error information. These tools can help locate a source, but they do not determine the intended business logic for you.
Check whether rows, columns, sheets, or named ranges were recently deleted, renamed, or moved. If the file is shared, ask whether another editor changed the source data.

On Windows, Ctrl+Z (Undo) can reverse a recent deletion or edit if it is still in the undo history. On Mac, use Command+Z. Undo affects the workbook’s recent actions, so review what will be reversed before continuing. If the workbook was closed or the undo history is no longer available, look for a backup or, for a file stored in OneDrive or SharePoint, an earlier version.

Repair a reference to deleted cells or a sheet

If you know the deleted content was needed, the safest repair is often to restore it rather than patch every formula. Undo the deletion when possible. If that is no longer possible, restore the missing row, column, or sheet from a backup or an earlier file version, then confirm that the dependent formulas calculate again. A replacement sheet may need the same name and compatible layout as the original for existing formulas to work.

If the original data is not coming back, edit each affected formula and point it to the correct surviving data. Select the formula cell, click in the formula bar, replace the #REF! portion with a valid reference, and press Enter. For a reference to another sheet, use the existing sheet name followed by an exclamation mark, as in Sales!B2. If the sheet name contains spaces, Excel encloses it in single quotation marks, as in ‘Monthly Sales’!B2.

Check the result against the source values and review other formulas that rely on it. A formula can stop showing an error while still pointing to the wrong range. If a deleted column used to hold prices and a neighboring column holds quantities, changing the address to the neighbor may make the formula calculate but produce a misleading total.

Correct invalid lookup and reference arguments

A formula can contain a valid-looking range but ask a function for an item outside that range. For example, VLOOKUP returns #REF! if its column index number is greater than the number of columns in its table array. Suppose A2:C4 contains product codes in column A, product names in B, and prices in C; E2 contains P-102, and that product’s price in C4 is 12.50.

With the lookup value in E2 and the three-column table in A2:C4, this formula requests the fourth column, which is outside the table, so it returns #REF!: =VLOOKUP(E2,A2:C4,4,FALSE)

With the same cell layout, change the column index to 3 to return the price from column C: =VLOOKUP(E2,A2:C4,3,FALSE) If P-102 is in A4 and its price is 12.50 in C4, the result is 12.50. The final argument FALSE requests an exact match.

Other reference-based functions can fail for similar reasons. INDEX can return #REF! when its requested row or column position falls outside the array supplied to it. Check the range dimensions against the row and column numbers in the formula, and confirm that copied formulas have not changed those numbers unexpectedly. Correct the argument that is out of range rather than hiding the error.

Check INDIRECT, names, and external references

When a formula uses INDIRECT, inspect the text it is converting into a reference. A misspelled sheet name, a cell address assembled incorrectly from text, or a missing worksheet can make that text invalid. If the reference is built from another cell, check the input and every text-joining part of the formula. A text value that looks like an address is not necessarily a valid reference.

Defined names are another place to investigate. Open Formulas > Name Manager and review names used by the failing formula. If a name’s Refers to entry contains #REF!, edit the name to point to the intended range or remove it only if it is no longer needed. Changing a name can affect many formulas, so check where it is used before making a workbook-wide change.

For external links, verify that the source workbook and worksheet still exist and that the link points to the intended location. A direct link to another workbook can often calculate while that source file is closed, but INDIRECT-based external references have different limitations and can return #REF! when the source workbook is closed. Do not assume every closed external workbook causes this error; inspect the particular formula and link.

Why replacing the error with blank text is usually not a fix

It may be tempting to hide the visible error with IFERROR, but that does not repair the broken reference. IFERROR can be useful when an error is an expected outcome and the workbook has a deliberate fallback, but using it to conceal a damaged formula can make a report look complete when data is missing. Downstream calculations may also treat a blank or substitute value differently from a valid result.

Fix the reference first, then decide how genuine exceptions should appear. If users need an understandable message for a missing lookup, a planned error-handling formula can be appropriate. Document what the fallback means and make sure it does not disguise missing or incorrect source data.

Prevent future #REF! errors

Before deleting a row, column, or worksheet, search for formulas that refer to it and confirm the effect on summaries, charts, and other sheets.
Keep a backup or use version history for important workbooks, especially before large structural edits.
Use Excel Tables for growing lists when suitable. Structured references such as a table column name can be easier to maintain than fixed cell ranges when rows are added.
When copying formulas, test a few results and inspect how relative and absolute references behave. A dollar sign, as in $A$1, locks a reference when a formula is copied; it does not protect a target from being deleted.
Review named ranges and external links periodically, particularly after renaming sheets or reorganizing source files.

Prevention is especially important in shared workbooks: one person’s cleanup can break another person’s report without changing the report sheet itself. If a workbook is business-critical, consider keeping source data separate from presentation sheets and restricting structural changes to people who understand the dependencies.

Excel for Windows, Mac, and the web

The core repair is the same in Microsoft 365 for Windows, Excel for Mac, and Excel for the web: inspect the formula, identify the missing target, and correct or restore it. Ribbon placement and some workbook-management options can differ by platform and version. On Windows, the paths in this guide use the Microsoft 365 ribbon. If you use Excel for the web, open the formula in the formula bar or edit it in the cell; version-history and external-link options may depend on where the file is stored and what access you have.

For a shared file, coordinate edits before restoring an older version because that version may also undo other people’s recent changes. Make a separate copy if you need to compare versions or test a repair without changing the live workbook.

Frequently Asked Questions

Can I remove #REF! without changing the formula?

You can hide the displayed error with error-handling logic, but that does not restore the missing reference. For a reliable result, correct the formula or restore the deleted target first. Only use a fallback when it represents a deliberate outcome, not as a way to make a broken workbook look error-free.

Why does #REF! appear after I delete a column?

A formula may have referred to cells in that column. If the deleted column held a required input, Excel cannot calculate from the removed target and may replace that reference with #REF!. Undo the deletion or restore the input, or edit the formula to use the correct remaining data.

Does #REF! mean my workbook is corrupted?

Usually, no. It most often indicates an invalid formula reference, not a damaged workbook file. If many unrelated cells suddenly show errors or the file behaves abnormally, make a copy and investigate recent changes, links, and file history rather than assuming one formula repair will solve everything.

Why does VLOOKUP return #REF! instead of #N/A?

#REF! commonly means the VLOOKUP column index asks for a column beyond the table range. #N/A generally indicates that the requested lookup value was not found with the selected match behavior. Check the table width and column index first, then check whether the lookup value exists.

Can I recover a deleted sheet after saving?

Undo may work if the deletion is still in the current undo history. Otherwise, check a backup or version history if the workbook is stored in OneDrive or SharePoint. If neither is available, recreate the sheet and update formulas to refer to its restored data; Excel cannot infer the deleted sheet’s contents from #REF! alone.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.