Excel #REF! Error: Causes and How to Fix It

The #REF! error in an Excel formula means that the formula contains an invalid cell reference. Find the broken reference, then replace it with the cell or range the calculation is meant to use; if you cannot tell what the original reference was, recover it from a backup or rebuild the formula from the worksheet’s layout.

What the #REF! error means

Excel formulas rely on references to cells, ranges, worksheets, and, sometimes, other workbooks. A reference tells Excel where to get a value. When Excel can no longer resolve a reference, it shows #REF! in the cell. The error may appear by itself or as part of a longer formula, and it can flow into other calculations that depend on the affected cell.

For example, a formula that adds two cells may stop working if one of those cells is deleted. The formula can retain the part of the calculation that still makes sense while showing #REF! where the deleted reference used to be. Excel does not know what replacement you intended, so there is no universally correct automatic repair.

It helps to distinguish the displayed error from a text label. If a cell simply contains the characters #REF! as text, it is not necessarily a broken formula. Select the cell and inspect the formula bar: a formula containing a reference error needs repair, while a cell containing only text may just be a note or imported value.

How a reference becomes invalid

The most familiar cause is deleting a row, column, or cell that a formula refers to. Excel often adjusts references when cells are inserted or moved, but deleting the referenced cells can leave a formula pointing nowhere. Deleting a worksheet can also break formulas elsewhere that referred to that sheet.

Other causes are less obvious. A formula can produce #REF! if a function is asked for a position outside the range it was given. A reference assembled from text can fail if the text does not describe a valid address. Copying or editing a formula may also create a reference that is no longer valid, especially when a formula is filled near the edge of a worksheet.

Deleted source cells: A calculation refers to a cell, row, or column that was removed.
Deleted or renamed sheet: Another formula expects a worksheet that is no longer available under that name.
Out-of-range function argument: A lookup or reference function is asked to return a position that does not exist in its supplied range.
Invalid text-based address: A function that turns text into a reference receives an address Excel cannot use.
Broken external reference: A formula refers to a location in another workbook that has been changed or removed.

Moving cells is not always the same as deleting them. Excel generally updates many direct references when cells are moved, but the outcome depends on how the move is made and how the formula was constructed. If an error appears after rearranging a sheet, inspect the formula rather than assuming that every move caused the same kind of break.

Find the broken formula

Start by selecting a cell that displays #REF! and reading its formula in the formula bar. Look for the error token inside the formula and note the surrounding operators, function name, sheet names, and ranges. Those clues can reveal what the formula used to calculate. If the selected cell contains another error instead, trace its inputs: a downstream formula may be displaying an error inherited from an earlier cell.

To search a workbook in Excel for Microsoft 365 on Windows, choose Home > Find & Select > Find, enter #REF!, and open the search options if needed. Set the search to look in formulas and search the whole workbook rather than only the active sheet. Choose Find All to list matching cells, then select each result to inspect it. Searching formulas matters because a formula may contain a broken reference even if another formula or display setting makes the problem less obvious.

On Mac, use Home > Find & Select > Find where available, or press Command+F to open Find; the dialog’s options let you choose the search scope and what to search. In Excel for the web, use Find on the Home tab or Ctrl+F, and check whether the search is limited to the current sheet. Browser search behavior and available options can differ from desktop Excel, so confirm results in the workbook rather than relying on a browser’s page search alone.

Also check formulas that depend on the error cell. Select the affected cell and use Formulas > Trace Precedents or Formulas > Trace Dependents where appropriate. These auditing tools can help show the chain of inputs and outputs, although they cannot reconstruct a reference Excel has already replaced with #REF!.

Repair the formula safely

Before changing a formula, establish what it is supposed to calculate. Compare it with neighboring formulas, check the relevant column headings, and look at the source data. In a repeated calculation down a table, a working formula in the row above or below may show the intended pattern. Do not simply replace every instance of #REF! with the nearest cell: that can produce a plausible-looking but incorrect result.

Select the error cell and inspect the complete formula in the formula bar.
Identify the missing input or range from nearby formulas, labels, prior workbook versions, or the calculation’s purpose.
Edit the formula to refer to the correct existing cells, then press Enter.
Check the result against the source values and review any dependent totals or reports.

For a concrete example, suppose costs are listed in B2:B4, with 12 in B2, 18 in B3, and 25 in B4. If a total formula was broken when a source cell was deleted, and the intended total is the three remaining listed costs, enter =SUM(B2:B4) in the total cell. With that layout, the result is 55. This repair is correct only if B2:B4 really are the complete intended set of costs; a formula’s original purpose must be confirmed from the workbook.

Some #REF! errors come from function arguments rather than deleted cells. Suppose B2:B4 contains three values and a formula uses INDEX to return an item from that three-cell range. With that layout, =INDEX(B2:B4,5) asks for the fifth item, which does not exist, so Excel returns #REF!. If the intended item is the third value, use =INDEX(B2:B4,3); it returns the value in B4. This illustrates why checking a function’s range and position arguments can matter as much as checking the cell addresses.

If the error appeared immediately after an accidental deletion, Ctrl+Z on Windows may undo the deletion, provided you have not made later changes you need to keep. On Mac, use Command+Z. Undo is most useful straight away; it is not a reliable recovery method after closing the workbook or continuing through many edits. If the workbook is stored in OneDrive or SharePoint, check its version history for an earlier copy. For a local file, look for a backup or an available previous version. Restore to a separate copy when possible so you can compare the old formula with current work.

When the error is not caused by deleting a cell

Reference functions deserve a closer look when no worksheet changes explain the error. INDEX can return #REF! when its requested row or column falls outside the supplied array. INDIRECT can return #REF! when its text argument does not resolve to a valid reference. OFFSET can produce an invalid reference if its requested location extends beyond the worksheet. In these cases, inspect the function’s arguments and the data that supplies them.

External workbook links need separate attention. A formula may point to another file or sheet, and a change to the source workbook’s location, name, or structure can make the reference unusable. The error is not proof that every external link is broken: some links may still work, while others may show outdated values or require access to the source file. Check the formula’s source path and confirm the source workbook is available before replacing the link with a guessed local address.

Names can also conceal the source of a problem. A formula may use a defined name rather than a visible address, and that name may refer to a deleted range. Open Formulas > Name Manager to review defined names and their references. Repairing the name may fix several formulas at once, but first confirm which formulas depend on it and what range it should represent.

Prevent future #REF! errors

There is no setting that can guarantee formulas will never break, but a few habits make damage less likely and easier to diagnose. Before removing a row, column, or sheet, search for formulas and reports that use it. If a change is part of a larger redesign, keep a dated copy or use workbook version history so the earlier structure remains available.

Use Excel tables for expanding lists where appropriate; formulas based on table columns can be easier to maintain as rows are added.
Give important ranges and worksheets clear names, and review defined names when reorganizing a workbook.
Check formulas after deleting or moving source data, and verify totals against a small manual calculation.
Keep a backup before major structural edits, especially in workbooks used for recurring reports.
Document unusual formulas and external links so another person can identify their intended sources.

Tables and named ranges reduce some maintenance problems, but they are not a substitute for checking a calculation. A table can be renamed or a source column removed, and an external link can still become unavailable. The useful goal is not to eliminate all risk; it is to make the workbook’s logic understandable enough that a broken reference can be traced and repaired accurately.

How #REF! differs from other Excel errors

#REF! specifically points to an invalid reference. By contrast, #VALUE! commonly indicates that a formula received a value of the wrong type for its operation, while #DIV/0! indicates division by zero or a blank divisor. #N/A generally means a value is unavailable to a lookup or another calculation. These errors can appear together in a workbook, but they suggest different checks.

Do not hide a reference error just to make a report look clean. Functions such as IFERROR can replace an error with a message or another value, but that does not restore a missing reference. If the underlying formula is wrong, suppressing the error may conceal an incomplete total or misleading result. Repair the reference first; use error-handling logic only when the workbook has a deliberate, documented response to a genuine error condition.

Frequently Asked Questions

Can Excel automatically fix a #REF! error?
Not reliably. Excel can identify that a reference is invalid, but it cannot know which replacement cell matches your intent. Use surrounding formulas, source data, or an earlier workbook version to determine the right reference.

Why does a formula show #REF! after I deleted a row?
The deleted row may have contained a cell the formula depended on. Excel cannot calculate from a reference that no longer exists, so inspect the formula and decide whether the deleted data should be restored or a different range should be used.

How do I find every #REF! error in a workbook?
Use Find and search for #REF! across the workbook, setting the search to look in formulas. Review each result because some errors may be on hidden sheets or in calculations that feed other cells.

Can I replace #REF! with zero?
Only if zero is genuinely the correct value for the calculation. Replacing a broken reference with zero can make totals and charts appear complete while understating or distorting the result. Find the intended source before making that change.

Will undo restore a deleted reference?
Ctrl+Z on Windows or Command+Z on Mac can reverse a recent deletion if it is still in the undo history. If that is no longer possible, use version history, a backup, or another copy of the workbook to recover the original formula.

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.