Excel Lessons

#REF! in Excel: what breaks a reference and how to fix it

#REF! is damage, not a data problem. The three things that cause it, why undo is the only real fix, and how to survive a deleted column.

Published . 7 min read.

By Ali, Founder, Excel Lessons

#REF! means the formula is pointing at a cell that no longer exists.

This makes it different from the other two common errors. #N/A and #VALUE! are complaints about data. You can fix the data and the formula recovers. #REF! is damage. The address the formula held has been erased, and Excel cannot guess what you meant.

Which leads to the most useful sentence in this article: press Ctrl+Z now. Undo is the only fix that restores what was there. Everything below is about the times you cannot.

The three things that cause it

1. You deleted a row, a column or a sheet

D2 holds =B2+C2. Delete column C. D2 becomes =B2+#REF!.

Note what did not happen. Excel did not shift the formula to the next column along. It knows the cell you named is gone. So it says so, instead of adding the wrong thing and saying nothing. That is the right behaviour, even though it does not feel like it.

Row A B C D
1 Region Q1 Q2 Total
2 North 1200 1400 2600
3 South 900 1100 2000
4 East 1500 1250 2750

2. You copied a formula somewhere it cannot fit

C2 holds =A2+B2. Copy it to A2. The formula tries to move two columns left. There is nothing to the left of column A. You get =#REF!+#REF!.

A relative reference is a direction, not an address. "Two columns left" is meaningless in column A. This is the cause people find hardest to believe, because nothing was deleted.

3. VLOOKUP was asked for a column that is not there

=VLOOKUP("North",A2:C4,4,FALSE) returns #REF!. The range A2:C4 is three columns wide. There is no fourth.

This one usually happens later, and by accident, which is the next section.

The failure that arrives weeks after the mistake

Here is the sequence that fills a workbook with #REF! and nobody sees it coming.

  1. You write =VLOOKUP(F2,A:D,4,FALSE). Column 4 is Total. It works.
  2. Weeks later somebody gets rid of column B. It looked empty to them.
  3. The table is now three columns wide. Every one of those formulas breaks.

Undo is long gone. The person who deleted the column did nothing obviously wrong. And a hard number inside a formula is what made it fragile.

The fix is to stop counting columns. =INDEX(D:D,MATCH(F2,A:A,0))

MATCH finds the row. INDEX returns the cell in column D on that row. Neither one holds a column number. Delete column B and both references move with the data, because Excel adjusts an address it can see.

=XLOOKUP(F2,A:A,D:D) does the same thing in one function, if your Excel has it.

Finding every one of them

#REF! spreads. One broken cell makes every formula that reads it break too. A workbook can go from one error to two hundred.

  1. Press Ctrl+F, search for #REF!, and set "Look in" to Formulas. Then Find All. You get a list you can click through.
  2. Fix the one that others depend on first. Select a broken cell and use Formulas → Trace Precedents to see what it was reading.
  3. Search and replace works on formulas too. Say two hundred formulas all lost the same reference. Replacing #REF! with the correct address fixes them in one go.

Writing formulas that survive a deleted column

Instead of Write Because
VLOOKUP with a column number INDEX and MATCH, or XLOOKUP No number to go stale
A2:D100 An Excel Table, then Sales[Total] Names survive; addresses do not
Deleting a column Hiding it Hidden columns break nothing

The third row is the cheapest habit on this page. Hide a column you are not sure about. Delete it in a month, when you know.

Questions people ask

Can I recover the cell that was deleted?

Only with undo, or from a saved copy. Excel does not remember what the address used to hold. If the file has been saved and closed, check File → Info → Version History. It works on files kept in OneDrive or SharePoint.

Why did my formula not just move to the next column?

Because that would be a guess. Adding the wrong column and saying nothing is worse than an error. A wrong total is the failure you would never find.

A whole sheet returns #REF! after I deleted a tab

A formula reading another sheet holds that sheet's name. Delete the sheet and the name is gone, so every formula pointing at it breaks at once. Undo restores both the sheet and the formulas.

Is #REF! the same as #NAME?

No. #NAME? means Excel does not recognise a word in the formula. Usually a misspelled function, or text without quotation marks. #REF! means the address itself is gone.

Related articles