#REF! error — what it means and how to fix it

The fastest fix: undo

If you just deleted a row, column, or sheet and haven't saved yet, press Ctrl+Z immediately. It undoes the deletion and the #REF! errors disappear. If undo isn't an option anymore — you saved, closed, or the deletion was several steps back — work through the steps below to find and repair each broken cell.

Step 1 — figure out what triggered it

Think about what you did just before the error appeared. The trigger is almost always one of these:

• Deleting a row or column that a formula was pointing into
• Deleting a sheet that a cross-sheet formula depended on
• Moving a formula using cut-and-paste into a position where its relative references now point outside the sheet's boundaries
• Copying a formula that uses relative references into a cell where the shifted addresses would land outside the grid

Step 2 — find every broken cell

For a large file, hunting #REF! cells one by one wastes time. Here's a faster approach:

1. Press Ctrl+G to open the Go To dialog.
2. Click Special.
3. Choose Formulas, then uncheck everything except Errors.
4. Click OK. Excel selects every cell with any formula error — including all your #REF! cells — at once.

Alternatively, press Ctrl+H (Find & Replace), type #REF! in the Find field, and click Find All. This gives you a list at the bottom of the dialog showing every affected cell and its current formula.

Step 3 — read the broken formula to understand what it was

Click on a cell showing #REF!. Look at the formula bar. You'll see something like =SUM(A1:#REF!) or =B3*#REF!. The #REF! in the formula is where the deleted reference used to be. That tells you what kind of reference it was (a single cell, a range, a sheet reference) so you know what to point it at instead.

Step 4 — repair or rebuild

If you know what the formula should have pointed to, click the cell, go to the formula bar, and replace the #REF! part with the correct cell address. If the formula has multiple #REF! pieces, fix each one.

If the data that was deleted is truly gone and can't be recovered, you'll need to decide whether to rebuild the formula against new data, simplify it to remove the dependency, or leave the cell blank. There's no automatic recovery — Excel can't invent data that no longer exists.

What #REF! is telling you

#REF! stands for "invalid reference." A formula contains a cell address — like B3 or Sheet2!D7 — and that address no longer points to anything real. The row was deleted. The column was removed. The sheet was renamed or deleted. Whatever the formula was counting on is gone, so Excel replaces the broken address with #REF! to mark the damage.

It's worth knowing that #REF! cascades: if cell A10 gets a #REF!, and cell B10 has a formula that uses A10, B10 will also show #REF! — even though B10's own references are fine. One root breakage can ripple through many dependent cells.

Preventing it going forward

The most reliable defense is to delete rows and columns by right-clicking and choosing Delete from within the spreadsheet — rather than selecting the row header and pressing the Delete key (which clears the content but leaves the row). Also, when a row contains data that formulas depend on, consider hiding it instead of deleting it if you're not certain it's safe to remove.

If you inherited a spreadsheet with scattered #REF! errors and aren't sure where they all are or what they originally pointed to, the fix formula errors tool can scan the file and give you a complete inventory of every broken reference — column, row, and the formula as it currently reads.