Excel #REF! Error: Causes, Fixes, and Prevention

A #REF! error in Excel means a formula contains an invalid cell reference: Excel can no longer find a cell, range, or sheet the formula needs. Find the affected formula, identify what it was meant to refer to, then restore or replace that reference; hiding the error does not repair the underlying problem.

What the #REF! Error Means

Excel formulas rely on references such as A1, B2:B10, or a cell on another worksheet. The reference tells Excel where to get the value used in a calculation. If that reference becomes invalid, Excel displays #REF! instead of calculating the formula. The error may appear by itself or as part of a longer formula, and formulas that depend on the broken result can show the same error too.

A #REF! error is different from other common Excel errors. #DIV/0! indicates division by zero, while #VALUE! often signals an unsuitable value or argument. #NAME? usually means Excel cannot recognize text in a formula, such as a misspelled function name or an unquoted text value. These errors can look similar in a worksheet, but the message is a clue: #REF! specifically points to a reference that is missing or invalid.

The error does not necessarily mean the whole workbook is damaged. It may affect one formula, a group of formulas that use the same deleted cell, or a chart, name, or other workbook feature that depends on a removed range. The best fix depends on what the formula was supposed to calculate.

How a Formula Loses Its Reference

The most familiar cause is deleting a row, column, cell, or worksheet that a formula explicitly refers to. Excel often adjusts references when you insert or remove cells, but it cannot preserve a reference to something that has been deleted. For example, a total formula that refers to a cell on a sheet can break if that sheet is deleted. The formula may then contain the visible text #REF! where the original reference used to be.

Other causes are less obvious. A formula may use a range that was partly removed, or a copied formula may contain a reference that becomes invalid as it is filled into another location. Functions such as INDIRECT can also produce #REF! when the address text they are given does not identify a valid reference. Some lookup or reference functions can return #REF! when a requested row or column position falls outside the supplied range.

References can also be affected by workbook changes made by another person, an imported file, a macro, or a process that rebuilds a worksheet. If a formula was already broken before you received the file, the error may not be connected to anything you just did. A formula can even calculate correctly in one workbook and fail after a sheet is renamed or removed from another workbook it relies on.

Deleted source: A formula points to a cell, range, or sheet that no longer exists.
Invalid position: A function requests a position outside the range it was given.
Text-built address: A function turns text into a reference, but the text does not describe a valid address.
Workbook changes: A copied, imported, or edited formula retains a reference that no longer fits the workbook.

How to Find the Broken Formula

Start by clicking a cell displaying #REF! and looking at the formula bar. If the formula itself contains #REF!, the reference is already visibly broken. Read the rest of the formula to identify its purpose: it may be adding a range, looking up a value, or combining values from other sheets. Nearby headings, labels, and formulas often reveal what the missing reference should have been.

To locate errors across a worksheet, use Home > Find & Select > Go To Special, choose Formulas, select Errors, and confirm. Excel selects formula cells that return errors, which can help when a large sheet has many rows. This selection may include errors other than #REF!, so inspect each selected cell rather than assuming every result is the same kind.

You can also search for #REF! with Find. On Windows, press Ctrl+f; on Mac, press Command+f. Search the current sheet or the workbook, as appropriate, and examine each result in the formula bar. Search can help locate formulas that literally contain the broken reference, while Go To Special is useful for selecting cells whose formulas currently return errors. If you use Excel for the web, available search and selection options can differ from the desktop application.

For a formula that is difficult to interpret, inspect its pieces rather than changing the entire expression at once. In Excel for Microsoft 365 on Windows, select the formula cell and choose Formulas > Evaluate Formula to follow the calculation step by step. This can show which part introduces the invalid reference. Make a copy of the workbook before substantial repairs, especially if many linked sheets or formulas are involved.

Ways to Repair a #REF! Error

There is no universal replacement for #REF!. First establish what the formula should refer to, using the workbook’s labels, an earlier saved version, source data, or a matching formula in a nearby row. Then repair only the damaged reference, and check that the result makes sense in the context of the sheet.

Restore the missing source. If a required worksheet or data range was removed and can be recovered from a backup or earlier workbook version, restoring it may be safer than rewriting many dependent formulas.
Edit the formula to point to the intended cells. Click the formula cell, use the formula bar or press F2 on Windows to edit, replace the broken reference with the correct cell or range, then press Enter. On Mac, use the equivalent formula-editing command available for your keyboard and Excel settings; F2 behavior can vary by keyboard.
Fix the source formula before its copies. If a whole column contains similar errors, compare the formulas and identify a correct pattern. Repair a representative formula, then fill it only if relative and absolute references are intended to change in the same way.
Check names and links. If the formula relies on a named range, another sheet, or another workbook, confirm that the name or source still exists and that the reference points where you expect.

For a simple example, assume cells B2:B4 contain 12, 18, and 20, and B5 is meant to total those three values. In B5, the formula =SUM(B2:B4) returns 50. If the formula in your actual workbook has a broken reference, do not blindly substitute this range; use it only if B2:B4 really are the intended inputs. A total that displays a plausible number can still be wrong if it excludes a required row or includes unrelated data.

After changing a formula, check both the displayed result and the formula bar. Compare a few affected rows, verify that the expected source cells are included, and recalculate if the workbook uses manual calculation. On Windows, the usual full recalculation shortcut is Ctrl+Alt+F9; on Mac, shortcut behavior can differ, and you can use Formulas > Calculate Now. Recalculation can refresh formulas, but it cannot recreate a deleted reference.

Special Cases: INDIRECT, Lookups, and Linked Workbooks

Some formulas create references indirectly. INDIRECT interprets text as a cell address, so a formula can return #REF! if that text produces an invalid address or names a sheet that is unavailable. In these cases, inspect the text being assembled as well as the formula itself. A cell containing a sheet name may have a typo, an extra space, or a name that changed when the sheet was renamed.

Reference and lookup formulas can fail when their requested position is outside the range supplied to them. Check the range dimensions and the row or column number being requested. If a position is calculated from another cell, verify that input too. Fixing the range or the position may resolve the error; increasing a range without checking the intended data can instead produce a misleading answer.

For formulas connected to another workbook, check whether the source file has moved, been renamed, or changed its sheet structure. Open the source workbook if available and confirm the relevant sheet and cells still exist. Do not assume that every external-link problem is a #REF! problem: a missing or inaccessible file can produce different prompts or behavior. Excel’s Data > Workbook Links options may be available for managing links in supported desktop versions, but the commands shown can depend on the workbook and Excel version.

Preventing Broken References

Preventive habits reduce both accidental deletions and hard-to-trace repairs. Before removing a sheet, row, or column, search for formulas and workbook features that depend on it. If a worksheet is only temporarily unnecessary, hiding it is less destructive than deleting it. Before changing a shared workbook, save a separate copy or use your organization’s version-history process so an earlier structure can be recovered.

Use clear sheet names and avoid renaming or deleting source sheets without checking dependent formulas.
Keep raw data in consistent tables or clearly defined ranges, and add new data in a way that preserves the intended calculation area.
Review formulas after importing or copying worksheets from another workbook.
When working with external links, document where the source workbook is stored and avoid moving it casually.
Check important totals and reports after structural edits, not just whether the error message disappeared.

Excel Tables can help formulas expand with added rows when the data is organized as a table, but they do not make every reference immune to deletion. Likewise, absolute references such as $B$2 keep a row and column fixed when a formula is copied; they do not protect the referenced cell from being deleted. A good prevention strategy combines stable workbook structure with checks on formulas that matter.

Why Not Hide the Error?

It can be tempting to wrap a formula in an error-handling function or replace the error display with a blank. That may be appropriate in a finished report when an error is expected and there is a deliberate way to handle it. But suppressing #REF! before finding its cause can conceal missing data and make totals, charts, or decisions unreliable. The output may look clean while the calculation is still incomplete.

Repair the reference first whenever possible. If an error is genuinely an expected condition, decide what the formula should display in that situation and document the choice. For example, returning a blank, zero, or explanatory message can each communicate something different; none is automatically correct. Error handling should describe a known business rule, not serve as a substitute for identifying a broken formula.

Frequently Asked Questions

What does #REF! mean in Excel?
#REF! means a formula refers to a cell, range, or worksheet reference Excel cannot resolve. Inspect the formula and determine which source it was meant to use before replacing the reference.

How do I find all #REF! errors in a workbook?
On desktop Excel, use Home > Find & Select > Go To Special > Formulas > Errors to select formula cells returning errors on the sheet. You can also search for #REF! and inspect each matching formula; search scope may be set to the current sheet or workbook.

Can I fix a #REF! error without knowing the original cell?
Not reliably. Check nearby formulas, labels, earlier versions, or the source workbook to infer the intended reference. Guessing can remove the error while producing an incorrect result.

Why does #REF! appear after I delete a sheet?
Formulas that referred to the deleted sheet may no longer have a valid destination. Restore the sheet if it is needed, or edit the affected formulas to use the correct remaining sheet or range.

Will IFERROR fix #REF!?
It can replace an error result with another display, but it does not restore a missing reference. Use error handling only when the error is expected and the replacement value is appropriate; otherwise, repair the formula first.

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.