How to Fix the #REF! Error in Excel

#REF! means Excel cannot find a cell, row, column, sheet, or range that a formula used to reference. It usually appears after someone deletes rows, deletes columns, cuts and pastes data, renames sheets, or copies a formula into a place where the original reference no longer makes sense.

Treat #REF! as a broken link inside the workbook. The fastest fix is to find what disappeared, then decide whether to restore the missing range or rewrite the formula around the current structure.

Check what the formula is pointing to

Click the cell with the error and look at the formula bar. You will usually see #REF! inside the formula itself:

or:

That tells you the original reference was removed. If the workbook changed recently, start with the deleted row, deleted column, or renamed sheet.

Undo if the change just happened

If you see the error immediately after deleting or moving cells, press Ctrl+Z or use Undo before making more edits. This is the cleanest repair because it restores the formula references and the missing cells together.

If the workbook has already been saved or shared, do not keep undoing blindly. Review the formula and repair the reference directly.

Save a copy before repairing a large workbook. A visible #REF! no longer contains the original deleted address, so version history or a known-good copy may be the only reliable evidence of what used to be there.

Fix a deleted column or row reference

Suppose a budget sheet used this formula to total planned and actual spend:

If column D was deleted, formulas that depended on it may break. Add the column back if the field still matters, or update the formula to use the remaining columns.

For example, if actual spend moved to column E, use:

This shows up often in small business budget templates and expense trackers when someone removes a category, helper column, or summary field without checking dependent formulas.

Fix a broken sheet reference

When a formula points to another sheet, a deleted or renamed worksheet can trigger #REF!:

If the sheet was renamed, update the formula with the current sheet name:

If the sheet was deleted, restore it from version history or rebuild the missing table before rewriting formulas.

Common causes and fixes

Find every broken reference

Use Find to search the workbook for #REF!, checking formulas rather than values when that option is available. Then review each match in context. A formula may contain more than one valid reference around the broken token, which can reveal the intended row, column, or sheet.

For new totals, a continuous range such as =SUM(A2:C2) usually adjusts more safely when rows or columns are inserted or removed than a chain such as =A2+B2+C2.

Do this with AI

Finding #REF! errors manually means opening formulas one by one and tracing what was deleted. That gets slow in operational workbooks with budget summaries, invoice logs, and project reports.

"Find every #REF! error in this workbook, explain what each formula was trying to reference, and repair the broken ranges where possible"

Griddy can inspect the formulas, flag the broken dependencies, and update the workbook without forcing you to trace each reference by hand.

Review every proposed replacement against a known total or source table. A syntactically valid reference is not proof that the repaired number is correct.

Sources

• Microsoft Support: Detect formula errors in Excel
• Microsoft Support: How to avoid broken formulas in Excel