Excel #REF! Error: Fixing Broken Cell References in Formulas

A #REF! error in Excel means a formula contains a cell reference that is no longer valid. If it appeared right after you deleted or changed cells, press Ctrl+Z on Windows (Command+Z on Mac) to undo; otherwise, find the broken reference and edit the formula to point to the correct cells.

What #REF! means

Excel formulas refer to cells, ranges, worksheets, or other locations in order to calculate. The error value #REF! tells you that Excel cannot resolve one of those references. The formula may still be recognizable, but at least one part points to something Excel can no longer use.

For example, a formula that adds two cells might become =A2+#REF! after the referenced cell is deleted. Excel cannot infer which replacement cell you intended, so it displays the error rather than guessing. A formula that uses the broken result may then show #REF! too. That is error propagation: the original fault can affect totals, charts, or other calculations that depend on it.

The error is different from #VALUE!, which generally signals a problem with the kind of value or operation in a formula, and #NAME?, which often indicates an unrecognized name or function. #SPILL! is also separate: it concerns a dynamic-array formula whose output range is blocked, not a missing reference.

Common causes of a #REF! error

Deleting a row, column, cell, or worksheet is a frequent cause. If a formula depends on what was deleted, Excel may replace the lost address with #REF!. Deleting a worksheet can affect formulas elsewhere in the workbook that referred to it. A range reference can also break if its required cells are removed.

Other causes arise from formulas that construct or calculate references:

INDIRECT: This function turns text into a reference. If the text does not describe a valid reference, the formula can return #REF!.
INDEX: If the requested row or column number is outside the dimensions of the supplied array, the result can be #REF!.
OFFSET: If the reference it calculates would extend beyond the worksheet, it can return #REF!.
Copied formulas: A relative reference may shift when a formula is copied. Copying alone does not necessarily create #REF!, but the changed address can become invalid in some circumstances, especially in formulas that calculate references.
Table or range changes: Removing a table column or other source that a formula relies on can leave dependent formulas with an invalid reference.

Moving cells is not the same as deleting them. Excel often adjusts formulas when cells are moved or inserted, whereas deleting a referenced cell can remove the target. The result depends on the operation and how the formula refers to its source, so inspect the formula rather than assuming every edit has the same effect.

How to find the broken formula

Start with the cell displaying #REF! and look at the formula bar. If you see the literal text #REF! inside the formula, that is a strong clue that an address was lost. If the formula looks normal, a function inside it may be producing the error, or the displayed cell may simply depend on another cell that contains the original fault.

To search the workbook in Excel for Microsoft 365 on Windows, press Ctrl+F, search for #REF!, open the search options, set the search scope to the workbook, and set Look in to Formulas. Choose Find All to see matching cells. Check each result in the formula bar: a cell could contain the characters as ordinary text rather than as an error or formula reference. On Mac, use Command+F; the dialog labels and layout can differ slightly.

Then follow the formula’s dependencies. Select the cell and, on Windows, choose Formulas > Formula Auditing > Trace Precedents to show cells that feed into it. Repeat the check on any referenced cells that also contain errors. This can help distinguish the first broken formula from later formulas that merely inherit its error. Remove the arrows with Formulas > Formula Auditing > Remove Arrows when finished.

Ways to repair the reference

The right repair depends on what the formula was meant to calculate. Excel cannot know whether a deleted reference should point to the cell above, a replacement column, or a different sheet. Confirm the intended source before changing the formula, particularly in a workbook used for reporting or operational decisions.

Undo a recent deletion. If the error appeared immediately after removing cells, use Ctrl+Z on Windows or Command+Z on Mac. Undo is most useful before making many other changes; it reverses the latest action, not just the deletion you have in mind.
Restore the original source. If undo is no longer practical, restore the deleted row, column, or worksheet from a known-good copy or an available workbook version. Check that the restored data belongs in the current workbook before relying on formulas again.
Edit the formula directly. Select the error cell, replace the invalid part with the correct address, and press Enter. For instance, if a calculation should multiply quantity in B2 by price in C2, use the formula shown below.
Check every dependent result. Review totals and other formulas that use the repaired cell. A repaired source may remove several downstream errors, but it does not prove that the resulting figures are otherwise correct.

Assuming B2 contains a quantity of 4 and C2 contains a price of 12, enter this in D2:

=B2*C2

The result is 48. If the intended cells are different, change the references to match the actual layout rather than copying this example blindly. If a formula contains several #REF! entries, repair each one based on its original purpose.

Understand reference behavior before editing

Excel uses relative, absolute, and mixed references. A relative reference such as B2 can change when a formula is copied to another location. An absolute reference such as $B$2 stays fixed when copied; a mixed reference fixes only the row or column. These dollar signs affect how a reference moves during copying. They do not restore a cell that has been deleted.

For example, if a formula is meant to multiply each row’s quantity by its price, relative references are usually useful when copying the formula down. If every row should use one fixed tax-rate cell, that reference may need dollar signs so it does not shift. The choice depends on the layout and intended calculation, not on the #REF! message alone.

With quantities in B2:B4, prices in C2:C4, and the multiplication formula entered in D2 and filled down, the formula for the first row is:

=B2*C2

If B2 is 4 and C2 is 12, D2 returns 48. When copied one row down, Excel normally adjusts the references to B3 and C3. This normal adjustment is useful, but it is worth checking copied formulas after inserting, deleting, or reorganizing source data.

Preventing future reference errors

Prevention is mostly about changing workbook structure carefully and keeping a recoverable copy. Before deleting a sheet, row, or column, identify formulas and reports that might depend on it. Searching for the sheet name or checking formulas that use the affected area can reveal dependencies that are not visible on the current worksheet.

Save a copy before substantial structural edits, especially in a workbook with many sheets or linked calculations.
Use Excel tables for expanding lists when appropriate; structured references can make formulas easier to read, though deleting a source field can still affect dependent calculations.
After moving or deleting data, search formulas for #REF! and inspect important totals and outputs.
Use clear sheet names and consistent layouts so that the intended source is easier to identify when repairing a formula.
Where a formula relies on a fixed cell, consider whether an absolute or mixed reference is needed before copying it.

For a workbook stored on OneDrive or SharePoint, Excel may provide version history that can help recover an earlier state. Availability and the route to version history depend on how the file is stored and your account or organization settings. A backup or prior version can restore lost content, but compare it carefully with the current workbook so you do not overwrite newer work unintentionally.

Why hiding the error is not the same as fixing it

It can be tempting to make an error disappear from a report without repairing its cause. A function such as IFERROR can return an alternative value when a formula produces an error, but that alternative may conceal a missing reference and make an incomplete result look trustworthy. Error handling is suitable when the fallback is intentional and clearly understood; it is not a substitute for restoring a broken source.

For example, if A2 contains a valid number and B2 contains zero, this formula returns the text Check input rather than showing a division error:

=IFERROR(A2/B2,Check input)

That example is not valid Excel syntax because text results must be enclosed in straight double quotation marks. The valid formula is:

=IFERROR(A2/B2,"Check input")

With A2 equal to 10 and B2 equal to 0, it returns Check input. This handles the division error in that particular example; it does not repair a #REF! elsewhere in the workbook. If you use an error-handling formula, document what its fallback means and make sure it cannot be mistaken for a real calculated value.

Frequently Asked Questions

Can I fix #REF! without knowing the original formula?
Not reliably. Excel identifies that a reference is invalid, but it cannot infer the intended replacement. Look for an earlier workbook version, a backup, a similar formula in a neighboring row, or documentation that identifies the correct source. Treat a guessed reference as unverified until you confirm the result.

Why does #REF! appear in cells that I did not edit?
Those cells may depend on a formula or source that was changed elsewhere. A single broken reference can pass its error to formulas that use its result. Find the original error first, then check downstream formulas after repairing it.

Will pressing Delete remove a #REF! error?
Deleting the contents of the cell removes the formula, but it does not repair the calculation. If other formulas depend on that cell, they may then show a different result or another error. Preserve the formula and correct its reference unless you have a clear reason to remove the calculation.

Does #REF! mean the workbook is corrupted?
No. In most cases, it indicates a broken formula reference, not a damaged workbook file. Inspect the formula and its source cells. If the file also fails to open or shows unrelated problems, that is a separate issue to investigate.

Can IFERROR solve a #REF! error?
It can replace an error result with a chosen fallback, but it does not restore the missing reference. Use it only when suppressing that error is an intentional part of the calculation, and keep the underlying source problem visible or documented while you diagnose it.

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.