How to fix the #REF! error in Excel

What #REF! actually means

A #REF! error means a formula points at cells that no longer exist. When you delete a row, column, or sheet that a formula depended on, Excel doesn't guess a replacement — it overwrites the reference with #REF! inside the formula itself. That's the painful part: the original address is gone. =A2/B2 becomes =A2/#REF!, and nothing in the cell tells you it used to say B2. Worse, the error cascades — every formula that reads the broken cell shows #REF! too, so one deleted column can light up half a model.

The most common causes

• Deleted rows, columns, or sheets that other formulas referenced — by far the usual culprit.
• Cut-and-paste over a formula's inputs: cutting cells (Ctrl+X) and pasting them on top of a range another formula uses destroys the reference; copy-paste doesn't.
• Lookups reaching outside their range: a VLOOKUP with a column index larger than the table has columns, or an INDEX/OFFSET pushed past the edge of the sheet.
• Broken links to another workbook that was moved, renamed, or never sent along with this file.

Step-by-step fix

1. If it just happened, press Ctrl+Z. Undo restores the deleted row or column and every reference with it — the only fix that recovers the original addresses.
2. Find every broken cell. Press F5 (or Home → Find & Select → Go To Special), choose Formulas, tick only Errors, and OK — Excel selects every error cell on the sheet at once. Ctrl+F searching for #REF! with "Look in: Formulas" works across a sheet too.
3. Read the formula bar, not the cell. The #REF! token sits exactly where the dead reference used to be (=SUM(A1:#REF!)), which tells you what kind of range you need to rebuild.
4. Trace the damage. Formulas → Trace Dependents on a repaired cell shows which downstream formulas were poisoned by the cascade; Trace Precedents shows what a broken formula still reads.
5. For external links, use Data → Edit Links to repoint or break links to the missing workbook.

To prevent the next one: convert ranges to Tables (Ctrl+T) or use named ranges — both survive row and column deletions far better than raw addresses.

Or let us find every one in seconds

Deleting one column can break references on sheets you never opened. Upload your workbook and we'll find every #REF! in your workbook in seconds — plus the fragile lookups likely to break next.

More fixes: run a full spreadsheet audit · fix the #DIV/0! error · fix circular references