Spreadsheets

#REF! error in Excel: why expense formulas break, and fixes

Why #REF! appears in an Excel budget or expense sheet, the three usual causes, a before and after formula, the Ctrl+Z test and how to repair and prevent it.

Editorial illustration for #REF! error in Excel: why expense formulas break, and fixes

#REF! means a formula points at a cell that no longer exists. In a budget workbook it almost always follows one of three actions: you deleted a row or column that a formula used, you deleted or moved a sheet, or a link to another file broke. Press Ctrl+Z straight away; if the error disappears, the last thing you did caused it.

What are the three common causes in a budget workbook?

  1. Deleted rows or columns. A formula that referred to a cell in the deleted line has nothing left to point at.
  2. A deleted or moved sheet. A summary that adds up month tabs breaks the moment you delete one of those tabs.
  3. A broken link. A formula that reads from another workbook shows the other file's name in square brackets; if that file is renamed or moved, the link can fail.

There is a fourth cause that needs no deletion at all: a lookup that asks for a column that is not there. =VLOOKUP(A2,$H$2:$I$40,3,FALSE) returns #REF! because the table has only two columns.

What does a formula look like before and after?

BeforeWhat you didAfter
=SUM(B2:D2)+F2Deleted column F=SUM(B2:D2)+#REF!
=SUM(B2:B13)Deleted rows 2 to 13 together=SUM(#REF!)
=Apr!C10+May!C10Deleted the May tab=Apr!C10+#REF!
=VLOOKUP(A2,$H$2:$I$40,3,FALSE)Nothing; the column number is too big#REF!

Notice that deleting one row from the middle of a range such as B2:B13 does not cause an error; the range simply shrinks to B2:B12. The error appears when the exact cell a formula pointed at, or the whole range, is gone.

Did I just break it? The Ctrl+Z test

If the #REF! showed up seconds ago, press Ctrl+Z once or twice and watch. When the error goes away you know which action caused it, and you can redo the job differently, for example by hiding the column instead of deleting it. Undo does not survive closing the file, so if the error has been sitting there since last week, move on to the next section.

How do I find and repair every #REF! in the workbook?

  1. Press Ctrl+F, click Options, set Within to Workbook and Look in to Formulas, and search for #REF!. Click Find All.
  2. Click each result in the list. The formula bar shows where the hole is.
  3. Retype the missing reference. In the first example, point the formula at the cell that now holds the figure, for instance E2 if the columns shifted left.
  4. For links, open Data, Edit Links. Choose Change Source to point to the right file or Break Link if you no longer need it. Breaking a link turns the values into plain numbers.
  5. Look at the totals again once the errors are gone, because a summary that skipped an error cell may have been wrong for a while.

How do I stop it happening again?

  • Clear contents instead of deleting rows or columns, unless you are sure nothing refers to them.
  • Hide columns you do not want to see rather than deleting them.
  • Leave a spare empty row above and below a summed block, and sum the whole block including them, for example =SUM(B1:B14) when the data is in B2:B13. Rows inserted inside the range are picked up and the range survives edits at either end.
  • Keep expenses in a table and use references like Txn[Amount], which follow the data when rows are added or removed.
  • Before restructuring a workbook, save a copy with today's date in the name.

Is this the same as #NAME? or #VALUE!?

No. #NAME? means Excel does not recognise a name or function, and #VALUE! means a value is of the wrong type. #REF! is specifically about a reference that has gone missing. Treating them separately saves time, because each one has a different set of causes.

A quick checklist when #REF! shows up

  1. Press Ctrl+Z and see whether the error clears.
  2. Find All for #REF! across the workbook.
  3. Rebuild each reference by hand.
  4. Check the links dialog for links that have gone stale.
  5. Compare the repaired totals with the last version you trust.

A deleted row or column in a spreadsheet is easy to lose for good once the file is closed. In SpendixAI, deleted transactions go to a recycle bin for 30 days, which is a softer safety net.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports