Fix #REF! Error in Excel

This error occurs when a formula refers to a cell that is not valid. Often happens when cells are deleted that were referenced by formulas.

Why this happens

  • Deleted cells that were referenced by formulas
  • Copying formulas to invalid locations
  • VLOOKUP column index exceeds table range
  • Invalid external workbook references

How to fix it

  • Undo the deletion if possible
  • Rebuild the formula with correct references
  • Use structured references for tables
  • Check external links are valid

High-impact #REF! checks

  • Look for formulas that reference deleted rows, columns, worksheets, or external workbooks.
  • Repair the reference manually or rebuild the formula using named ranges or structured table references.

#REF! recovery steps

  • Undo deleted rows/columns that fed the formula.
  • Replace #REF! with intentional ranges after restructuring sheets.
  • Avoid whole-column refs on huge sheets when moving blocks.

Functions that often show this error

  • XLOOKUP: Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, XLOOKUP can return the closest (approximate) match.
  • VLOOKUP: Looks for a value in the leftmost column of a table, and then returns a value in the same row from a column you specify.
  • INDEX: Returns the value of an element in a table or array, selected by the row and column number indexes.
  • HLOOKUP: Searches for a value in the top row of a table and returns a value in the same column from a row you specify.
  • INDIRECT: Returns the reference specified by a text string. References are immediately evaluated to display their contents.
  • OFFSET: Returns a reference offset from a starting point by a specified number of rows and columns.
  • GETPIVOTDATA: Returns data stored in a PivotTable report.