#REF!Supported on: Microsoft Excel® & Google Sheets™

How to Fix the #REF! Error in Excel & Google Sheets

The #REF! error occurs when a formula references a cell, row, or column that has been physically deleted or moved out of the active sheet boundaries.

Common Root Causes of #REF!

  • 1Deleting a column or row that was actively referenced by formulas on other sheets.
  • 2Cutting and pasting cells over top of coordinates referenced by existing formulas.
  • 3VLOOKUP or INDEX index argument exceeding the total number of columns in the source range.
  • 4Copying relative formulas beyond the edge of the worksheet grid.

Formula Syntax Comparison

Broken Formula
=SUM(B2:B10, #REF!)
Corrected Formula
=SUM(B2:B10, C2:C10)

Manual Step-by-Step Fix

1

Step 1

Press Ctrl+Z / Cmd+Z immediately if the deletion was recent to restore the missing cell references.

2

Step 2

Inspect the formula bar to see where '#REF!' replaced the original coordinates (e.g., =SUM(A1, #REF!)).

3

Step 3

Update lookup functions to use dynamic modern alternatives like XLOOKUP or INDEX/MATCH that do not break when column positions shift.

Automate this Fix with SheetFactorys

Zero manual debugging required

SheetFactorys inspects formula syntax trees and surrounding relational dependencies to reconstruct lost range coordinates and upgrades legacy VLOOKUP formulas to resilient XLOOKUP functions.

Available in Microsoft AppSource & Google Workspace
Install for Excel®Install for Sheets™