Excel #REF! Error: Causes and Fixes

An Excel #REF! error means a formula contains a reference to a cell, range, or worksheet that Excel cannot identify. Find the broken reference in the formula bar and replace it with the correct location; if a deletion caused the error, undo the deletion when possible or rebuild the formula using the cells that still exist.

What the #REF! error means

Excel formulas rely on references: addresses such as A1, ranges such as A1:A10, and sheet-qualified addresses such as January!B4. When Excel can no longer resolve a reference in a formula, it displays #REF!. The error is not simply a warning that a value is missing. It says the formula’s address is invalid.

For example, if a formula points to a cell on a worksheet that has been deleted, Excel cannot retrieve the value from that location. A formula that uses a range or a function can also contain an invalid reference. The error may appear directly in the cell, or it may flow into other formulas that depend on that cell. A total or chart based on an errored result can therefore be affected even if its own formula does not visibly contain #REF!.

The error is different from #N/A, which commonly indicates that a lookup did not find a match, and #VALUE!, which generally indicates an inappropriate value or data type in a calculation. Those errors have different causes and fixes.

Common causes of a broken reference

Deleting a worksheet, row, column, or referenced cells is a frequent cause. Excel often adjusts formulas when cells are moved or removed, but not every deletion can be translated into a valid new reference. Deleting a sheet used in a formula is especially likely to leave a visible #REF! in that formula.

Other causes include copying a formula beyond the range its relative references were designed for, changing a formula by hand, or using a function with an out-of-range position. A reference can also be inside a defined name, a chart series, or a formula that builds an address from text. The visible error may therefore be in a place that is not obvious from the worksheet cell where you first noticed it.

Deleted source: A formula depended on a cell, range, or sheet that was removed.
Edited formula: A reference was accidentally deleted or replaced while changing the formula.
Copied formula: Relative references shifted as the formula was filled into neighboring cells.
Invalid function position: A function was asked for a row or column outside its supplied range.
Broken name or link: A defined name or an external reference used by the formula no longer points to a usable location.

How to find the source of the error

Start by selecting the cell showing #REF! and reading the formula bar. If the formula is short, you may see the broken part immediately. A formula can contain valid references alongside one broken reference, so inspect the entire expression rather than replacing the first error text you spot without understanding what the formula is meant to calculate.

In Excel for Microsoft 365 on Windows, press Ctrl+f, search for #REF!, and use the search options to look in formulas and, when relevant, within the whole workbook. Choose Find All to see matching formulas together. On Mac, use Command+f to open Find; the available search options are in the Find interface. In Excel for the web, use the workbook’s Find feature, though the desktop app offers more complete formula inspection tools.

Searching is helpful, but it may not reveal every downstream effect. A formula can calculate an error because a referenced name or a function-generated address is invalid, even when the formula text does not contain the literal characters #REF!. Check formulas feeding the affected cell and inspect any defined names used by the calculation. In desktop Excel for Windows, open Formulas > Name Manager to review names and their Refers to values. A name whose target displays #REF! needs correction, or the name may need to be removed if it is no longer used.

If the error appeared immediately after an edit, consider using Undo before making more changes. Press Ctrl+z on Windows or Command+z on Mac. Undo may restore the deleted content and its references, but it is only useful while the relevant action remains in the undo history. Save a copy first if you are unsure what an edit will affect.

Repair the formula based on its purpose

There is no universal replacement for #REF!. The right fix depends on what the formula was intended to calculate. If you know the correct source cell, edit the formula and select that cell or range directly. In the formula bar, you can also use the arrow keys to move through the formula and replace only the broken reference. Press Enter to commit the change, then check the result against the underlying data.

For a simple example, assume cells A1:A3 contain 10, 20, and 30, and the intended total belongs in B1. The formula for B1 is =SUM(A1:A3), which returns 60. If the original source range was deleted or the formula was damaged, restore the intended range only if A1:A3 really contains the values that should be totaled. Choosing a nearby range just to remove the error can produce a plausible but incorrect answer.

For a reference to another worksheet, confirm both the sheet name and the target address. Suppose a worksheet named Sales 2025 has 1250 in B2, and you want another cell to display that value. The formula is ='Sales 2025'!B2, and it returns 1250. The single quotation marks are required around a sheet name containing a space. If the referenced sheet was deleted, editing the formula to a different sheet is only correct if that sheet contains the intended data.

When a formula is too complicated to repair safely, reconstruct it in a spare cell first. Compare the result with a known total or a few source records, then replace the damaged formula once you are confident. This is particularly useful for long formulas with multiple sheet references, nested functions, or references to external workbooks.

Functions and formulas that can produce #REF!

Some errors arise not because a cell was deleted, but because a function is asked to return a position that is outside its range. For instance, assume A1:B3 is a three-row, two-column range. The formula =INDEX(A1:B3,4,1) asks for the fourth row of that range. Since the range has only three rows, the formula returns #REF!. Check the function’s range and its row or column arguments; if those arguments come from other cells, check those inputs too.

Functions that build references from text can also fail when the text does not describe a valid address. For example, INDIRECT interprets a text string as a reference. If the string is misspelled, points to a missing sheet, or otherwise cannot be resolved, the formula can return #REF!. Check the text being assembled and confirm that the named sheet and address exist. If a formula uses a name or a cell to construct that text, inspect those inputs rather than changing the formula blindly.

Lookup formulas can contain references as well as search logic. A lookup that finds no match more commonly produces #N/A, not #REF!, so do not treat the two errors as interchangeable. If a lookup formula shows #REF!, examine its table range and any row, column, or return-range references. Correcting the source range is different from handling an unmatched lookup value.

Why copying or filling formulas can break references

Excel normally adjusts relative references when you copy or fill a formula. If a formula in C2 refers to B2 and you fill it down to C3, the reference typically shifts to B3. That behavior is useful for row-by-row calculations, but it can become a problem when a formula is copied into a position where its intended source no longer exists or where a reference has shifted unexpectedly.

Absolute references, marked with dollar signs, stay fixed when copied. For example, a formula referring to $B$2 keeps that address when filled elsewhere, while a reference such as B2 can adjust. Mixed references, such as $B2 or B$2, lock only the column or row. When investigating a copied formula, compare it with a neighboring correct formula and check whether the row and column references moved as intended. Restore the appropriate reference style before filling the formula again.

Do not assume a formula is correct just because Excel accepted it or stopped showing an error. A repaired reference can point to the wrong data and return a normal-looking number. Verify a few outputs against the source cells, especially at the top and bottom of a filled range where relative references are more likely to go beyond the intended data.

Why IFERROR is not a true repair

IFERROR can display an alternative result when a formula returns an error, but it does not fix the invalid reference. For example, assume A2 contains 10 and B2 contains 0, and the desired fallback for a division error is zero. The formula =IFERROR(A2/B2,0) returns 0. That result may be suitable for a deliberate display choice, but it does not correct the division or explain why it failed.

Wrapping a damaged reference in IFERROR can make a workbook look clean while concealing missing data or a broken calculation. If the formula should point to a valid source, repair that source first. Use error handling when an error is an expected possibility and the fallback is meaningful to the person using the workbook. For example, a report may intentionally show a blank or a message when an optional input is absent, but that decision should be explicit rather than a substitute for checking a broken reference.

Preventing future reference errors

Prevention starts with understanding which formulas depend on the cells or sheets you plan to change. Before deleting a worksheet or a large block of data, search for its name in formulas and check whether it feeds summaries, charts, or other sheets. Save a version of the workbook before structural edits, especially when multiple people maintain it.

Use clear worksheet names and avoid renaming or deleting source sheets without checking dependent formulas.
Keep a backup or versioned copy before removing rows, columns, or worksheets.
Test copied formulas in the first and last rows of the intended range.
Use absolute or mixed references when a fixed row or column is part of the calculation.
Review defined names when formulas use names instead of visible cell addresses.
Recheck totals and key outputs after repairing references; removing the error is not proof that the result is correct.

For recurring reports, consider structuring source data consistently and documenting which sheets feed the final totals. This makes it easier to identify the intended replacement if a reference breaks. It also helps distinguish a genuine missing source from a formula that was simply copied or edited incorrectly.

Frequently Asked Questions

Can I fix a #REF! error without changing the formula?
Only if the cause can be reversed, such as immediately undoing a deletion that removed the referenced content. Otherwise, the formula needs a valid reference or an intentional error-handling rule. Restoring a worksheet with the same name does not necessarily repair a formula that already contains a literal #REF!; inspect and edit the formula.

Why does Excel show #REF! after I delete a row?
The deleted row may have contained a cell or part of a range that a formula needed. Excel adjusts many references automatically, but a reference that cannot be preserved may become invalid. Check the affected formula and determine which remaining cells should replace the deleted source.

Does #REF! mean the number in my formula is wrong?
Not usually. It indicates an invalid reference, rather than a calculation result that is merely too large, too small, or not found. A function argument that requests a position outside its range can also cause it, so inspect both addresses and function arguments.

Why does a formula work in one row but show #REF! in another?
When formulas are copied, relative references can shift from row to row. The shifted reference may be valid in one row but invalid in another, or it may point to an unintended location. Compare the formulas in the working and failing rows, including any dollar signs that lock references.

Can I use IFERROR to remove #REF! from a worksheet?
You can use it to display a fallback when a formula returns an error, but it does not repair the source reference. First decide whether the error is expected and whether the fallback is meaningful. If the formula should calculate from existing data, correct the reference instead of hiding the problem.

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.