Excel

How to Fix the #REF! Error in Excel

The #REF! error in Excel means a formula points to a missing cell or range. Learn the common causes and the cleanest ways to repair it.

June 2, 2026/5 min read

#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:

=SUM(B2:#REF!)

or:

=VLOOKUP(A2,#REF!,3,FALSE)

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.

Fix a deleted column or row reference

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

=SUM(C2:D2)

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:

=SUM(C2:E2)

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!:

=SUM(#REF!A2:A100)

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

=SUM(Expenses!A2:A100)

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

Common causes and fixes

Cause	What happened	Fix
Deleted row or column	The formula used cells that no longer exist	Restore the row or rewrite the range
Deleted worksheet	A cross-sheet formula points to a missing tab	Restore the sheet or point to the new tab
Cut and paste	A referenced block moved in a way Excel could not preserve	Re-select the correct range
Bad copy pattern	A relative reference moved outside the valid range	Use absolute references where needed
Broken lookup table	VLOOKUP or INDEX points to a removed table	Rebuild the lookup range

WATCH OUT

Do not hide #REF! with IFERROR until you understand what broke. A hidden broken reference can make a report look finished while the numbers are wrong.

The Griddy way

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.

Skip the manual work

Describe it. Griddy does it.

Instead of writing this formula yourself, just tell Griddy what you need in plain English. Works in Excel and Google Sheets.

Excel Plugin Sheets Plugin

Use this on real templates

Repair broken references in real workbooks

Reference errors are most damaging in budgets, expense logs, invoices, and project reports where one deleted row can break a summary people rely on.

Finance

All posts