How to Resolve a #REF! Error in Excel

To resolve a #REF! error in Excel, find the formula containing the broken reference, then undo the deletion that caused it or edit the formula to point to the correct cell or range. In Microsoft 365 for Windows, start by selecting the error cell and checking the formula bar. If the change was just made, press Ctrl+Z before doing anything else; otherwise, repair the reference and check any formulas that depend on it.

A #REF! error means a formula refers to a cell, range, worksheet, or workbook reference Excel cannot use. It is not the same as a missing value or a calculation error such as #DIV/0!. The formula needs a valid reference before Excel can calculate it correctly. Save a copy of the workbook before making widespread edits, especially if several sheets or reports depend on the affected formulas.

What You’ll Need

The workbook that contains the error, preferably opened in desktop Microsoft Excel for Microsoft 365 on Windows.
Permission to edit the workbook and, if the broken reference points to another file, access to that source workbook.
A recent saved copy or version history if you may need to restore deleted cells, sheets, or formulas.
A little context about what the formula should calculate, such as the intended input cells or the correct worksheet name.

Step-by-Step

Save a working copy. Use File > Save As to make a separate copy before repairing a workbook with many errors. This preserves the original in case a formula is changed incorrectly. If the error appeared after a recent edit and you have not made other changes you need to keep, use Ctrl+Z on Windows to undo that edit. On a Mac, use Command+Z. Check whether the error disappears; undo only as far as needed.
Select the cell showing #REF!. Click the cell and read the formula bar above the worksheet. The cell may contain only the error, or the formula may have a broken section such as #REF! within a longer calculation. Note the formula and the surrounding cells before changing anything. If the formula bar shows a value rather than a formula, the error may be in a different cell than the one you selected.
Identify what the formula was meant to reference. Look at nearby headings, source data, similar formulas in adjacent rows, and other worksheets. Check whether the formula used a relative reference, an absolute reference with dollar signs, a named range, or a reference to another workbook. For example, if a calculation multiplies a unit price by a quantity, confirm which two cells hold those inputs. Do not replace #REF! with a guess simply because a nearby cell looks plausible.
Undo a deleted cell, row, column, or sheet if possible. If you know the error started after a deletion, undoing that deletion is usually the safest fix. Once the missing content is restored, confirm the formula calculates and that the restored data is correct. If the deletion happened earlier or the workbook was closed, check File > Info for version history or a saved backup. In OneDrive or SharePoint, use the file’s version history if available. Restoring a deleted sheet with the same name may not automatically repair a formula whose reference has already become #REF!; inspect and edit the formula afterward.
Rewrite the broken reference in the formula bar. Click the error cell, select the broken part of the formula, and type or select the intended cell or range. Press Enter to commit the change. For instance, with a unit price of 12 in B2 and a quantity of 4 in B3, the intended formula in C2 is =B2*B3, which returns 48. If B3 was deleted and the formula now contains a broken reference, restore the quantity in the right cell or replace the broken part with the cell that actually contains 4. Confirm the inputs and result rather than relying on the example’s cell addresses.
Repair copied formulas carefully. If the error appears in a column of similar calculations, compare the formula with a working row. With prices in B2:B4 and quantities in C2:C4, the formula in D2 can be =B2*C2; if B2 is 12 and C2 is 4, it returns 48. When copied down, normal relative references adjust to the next row. Use the fill handle or copy a known-good formula only after checking that each row follows the same pattern. A formula copied from a different section may point to the wrong inputs even if it no longer shows #REF!.
Check named ranges and table references. If a formula uses a name rather than a cell address, open Formulas > Name Manager and inspect the Refers to entry. Correct the address if it contains #REF! or points to the wrong range. For a structured table formula, check that the named table and column still exist. Renaming or deleting a table column can break formulas that refer to it. Update the name or formula to match the current table layout, then verify the result in a few rows.
Check links to other workbooks. If the formula contains another workbook’s file name, confirm that the source file exists and that you can open it. In Microsoft 365 for Windows, look for Data > Workbook Links when that command is available. The link controls can help identify a source workbook, but changing a link does not automatically repair a formula that already contains #REF!. Edit the broken reference to the correct source sheet and range. Avoid replacing a source link unless you know the new file contains the intended data.
Search for other broken references. Press Ctrl+F, search for #REF!, and open the search options. Set Within to Workbook and Look in to Formulas so Excel can find errors embedded in formulas across sheets. Choose Find All to review matches, then repair each formula that is genuinely broken. On a Mac, use Command+F; the Find options may be arranged differently. After the repairs, check important totals and dependent reports rather than assuming that fixing one visible error fixed the entire workbook.

Common Mistakes

Replacing every #REF! with the same cell address. Different formulas may have lost different inputs. Inspect each formula and its purpose before editing.
Assuming a blank cell is the cause. A blank cell usually does not create #REF! by itself. The error normally indicates a reference Excel can no longer resolve, such as a deleted cell or sheet.
Changing a formula to make the error disappear. A formula can return a plausible number while using the wrong cells. Verify the intended inputs and compare the result with a manual check or a known-good row.
Using Find and Replace without reviewing matches. Replacing #REF! in formulas in bulk can produce incorrect references or change formulas that need different repairs. Use search to locate errors, then review and fix them individually.
Deleting a source sheet or column before checking dependencies. Formulas, charts, names, and summaries may use that data. Before removing source material, search for references or keep a backup.
Confusing #REF! with another error. Errors such as #NAME? or #VALUE! have different causes. Confirm the exact error text before following these repair steps.

Tips

Use Formulas > Formula Auditing > Trace Precedents to inspect cells that feed a formula. Arrows can help show where a calculation gets its inputs. If a reference is already broken, the arrows may not reveal the missing cell, so also inspect the formula itself.
Use descriptive worksheet names and keep input data in clearly labeled tables. This makes it easier to identify the intended source when a formula breaks.
When moving data, prefer moving or copying the cells rather than deleting a source and rebuilding formulas from memory. Excel can adjust many references when cells are moved, but always verify formulas after reorganizing a workbook.
After fixing a formula, check a few related calculations and any final totals, charts, or reports. A repaired cell may feed other formulas, so confirm that downstream results also look right.
For workbooks stored in OneDrive or SharePoint, use version history as a recovery option before attempting extensive manual reconstruction. Save a separate copy of the repaired workbook once the results have been checked.

Troubleshooting

The formula still shows #REF! after editing. Select the cell again and inspect the whole formula for another broken reference. A formula can contain more than one #REF! token, including inside a function or a named range. Press Enter after editing, and check that Excel has not left the cell in edit mode.

Find does not locate all the errors. In the Find dialog, confirm that Within is set to Workbook and Look in is set to Formulas. Search for #REF! rather than searching only the currently selected sheet. Some errors may be on hidden sheets, so check sheet visibility and review the workbook structure if results seem incomplete.

The original data is gone and there is no undo available. Look for an earlier workbook version, a backup, or the original source file. If none exists, reconstruct the missing reference from documented inputs or a matching formula elsewhere, then independently verify the calculation. Do not treat an estimated replacement as confirmed data.

The error involves an external workbook. Open the source workbook if possible and confirm its worksheet and range names. If the source was moved or renamed, locate the correct file and update the formula or link. If the formula itself contains #REF!, it still needs a valid sheet and cell reference after the source is available.

The error appears in a name or table formula. Check Formulas > Name Manager for broken named ranges, and verify the table name and column headings used by the formula. Repair the definition or update the formula to match the current workbook structure.

Frequently Asked Questions

What does #REF! mean in Excel?
It means a formula contains an invalid reference to a cell, range, worksheet, or workbook. Excel cannot calculate the formula until the reference is restored or corrected.

Can I fix a #REF! error without changing the formula?
Sometimes. If the error followed a recent deletion, Ctrl+Z on Windows or Command+Z on Mac can undo the deletion and restore the original reference. If undo is unavailable, use version history or edit the formula.

Why did deleting a row cause #REF!?
A formula that directly referred to a cell in the deleted row may lose its target. Excel adjusts many references when rows are inserted or moved, but deleting a referenced cell, range, or sheet can leave an invalid reference.

Will Find and Replace repair every #REF! error?
No. It can help locate formulas, but each broken reference may need a different replacement. Review the formula and intended inputs before making a change; a global replacement can silently produce wrong results.

Can a #REF! error affect other formulas?
Yes. Formulas that depend on the broken cell may also display an error or produce unreliable results. After repairing the original reference, check dependent totals, summaries, charts, and reports.

Does this process differ on Mac or Excel for the web?
The formula repair approach is the same. On Mac, use Command+Z and Command+F instead of the Windows Ctrl shortcuts. In Excel for the web, some link-management and workbook navigation commands may differ or be unavailable, so open the file in desktop Excel if you need those controls.

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.