#REF!Excel error guide No upload

#REF! in Excel — Broken cell reference

#REF! means a formula points to a cell, row, column or sheet that no longer exists. It usually appears after deleting rows or columns, moving a sheet, or pasting a formula into a workbook that does not contain the referenced sheet.

Reviewed 31 October 2026 Tested in Excel 365; fixes apply to 2021, 2024 and Mac where the feature exists

The short answer

What it looks like

A formula worked yesterday and now shows #REF!
The error appeared after deleting a column, row or worksheet
A VLOOKUP or SUM formula contains a reference like #REF!

Why #REF! happens

Deleting the row, column or sheet that a formula referenced.
Cutting and pasting cells over a range used by another formula.
A formula copied from another workbook that references a sheet that is not present.
An INDIRECT or OFFSET formula that builds a reference to a deleted or misspelled name.
Sorting or filtering a range that contains formulas with relative references outside the range.

How to fix #REF!

1
Undo the deletion first
If the error appeared immediately after deleting a row, column or sheet, press Ctrl+Z (Cmd+Z on Mac) before doing anything else. This restores both the data and the references.
2
Find and replace the broken reference
Select the formula, look for #REF! in the formula bar and replace it with the correct range. Use Find (Ctrl+F) → Options → Within: Workbook to locate every occurrence.
3
Re-point the formula at the current range
If a column was moved, rewrite the reference or use INDEX/MATCH or XLOOKUP so the formula does not depend on a fixed column position.
```
=XLOOKUP(A2,$D$2:$D$500,$B$2:$B$500,"Not found")
```
4
Make INDIRECT references safer
INDIRECT turns text into a reference, so a renamed or missing sheet produces #REF!. Use a real structured reference or validate the sheet name before using INDIRECT.

A safer formula pattern

=IFERROR(XLOOKUP(A2,$D$2:$D$500,$B$2:$B$500),"Not found")

A lookup that returns a controlled value instead of a broken reference when the key is missing.

Check it in your own workbook

Paste the formula into the free browser tools to see the exact fix without uploading your file. The Error Fixer explains the code, the Formula Checker finds syntax problems, and the Formula Explainer breaks down nested logic.

Open the interactive Error Fixer Check a formula

Related functions

XLOOKUP INDEX MATCH INDIRECT OFFSET

Frequently asked questions

What does #REF! mean in Excel?

How do I fix #REF! in Excel?

Undo the deletion first: If the error appeared immediately after deleting a row, column or sheet, press Ctrl+Z (Cmd+Z on Mac) before doing anything else. This restores both the data and the references. Find and replace the broken reference: Select the formula, look for #REF! in the formula bar and replace it with the correct range. Use Find (Ctrl+F) → Options → Within: Workbook to locate every occurrence.

Why does #REF! appear only in some rows?

Usually because only some rows contain the trigger: Deleting the row, column or sheet that a formula referenced. Cutting and pasting cells over a range used by another formula. A formula copied from another workbook that references a sheet that is not present.

How can I stop #REF! from appearing again?

A lookup that returns a controlled value instead of a broken reference when the key is missing. Use this pattern as a guard: =IFERROR(XLOOKUP(A2,$D$2:$D$500,$B$2:$B$500),"Not found")

Related Excel error guides

All error codes →

#VALUE!Wrong value type#VALUE! means Excel expected a number, date or logical value but found text, an empty cell or a value from another data type. The fix is to convert the input, remove hidden characters, or guard the calculation with IFERROR.#N/ALookup value not found#N/A means a lookup or match function could not find the value. The value may be missing, spelled differently, stored as text instead of a number, or surrounded by spaces. Use exact-match mode and return a clear fallback.#DIV/0!Division by zero#DIV/0! appears when a formula divides by zero or by an empty cell. Excel treats a blank denominator as zero. Check the denominator, exclude blank rows, or return a controlled blank with IFERROR.#NAME?Unknown function or name#NAME? means Excel does not recognise a function, named range or reference in the formula. The usual causes are a misspelled function, missing quotation marks around text, or a function that is not available in your Excel version.

Still stuck?

Send the workbook and get the formula fixed

A human Excel expert can repair the formula, explain the cause and return a tested workbook — usually within 24 hours. Fixed quote before work starts, from $15/hr.

Get a fixed quote Ask on WhatsApp

#REF! in Excel — Broken cell reference

Reviewed 31 October 2026 Tested in Excel 365; fixes apply to 2021, 2024 and Mac where the feature exists

Why #REF! happens

Deleting the row, column or sheet that a formula referenced.

Cutting and pasting cells over a range used by another formula.

A formula copied from another workbook that references a sheet that is not present.

An INDIRECT or OFFSET formula that builds a reference to a deleted or misspelled name.

Sorting or filtering a range that contains formulas with relative references outside the range.

How to fix #REF!

Undo the deletion first

If the error appeared immediately after deleting a row, column or sheet, press Ctrl+Z (Cmd+Z on Mac) before doing anything else. This restores both the data and the references.

Find and replace the broken reference

Select the formula, look for #REF! in the formula bar and replace it with the correct range. Use Find (Ctrl+F) → Options → Within: Workbook to locate every occurrence.

Re-point the formula at the current range

If a column was moved, rewrite the reference or use INDEX/MATCH or XLOOKUP so the formula does not depend on a fixed column position.

=XLOOKUP(A2,$D$2:$D$500,$B$2:$B$500,"Not found")

Make INDIRECT references safer

INDIRECT turns text into a reference, so a renamed or missing sheet produces #REF!. Use a real structured reference or validate the sheet name before using INDIRECT.