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.

Frequently asked questions

  • What does #REF! error mean in Excel? #REF! (Reference Error) occurs when a formula refers to a cell that doesn't exist or is invalid. This commonly happens when you delete rows, columns, or cells that are referenced by formulas, or when a VLOOKUP column index exceeds the table range.
  • How do I fix #REF! error in Excel? To fix #REF! error: 1) Press Ctrl+Z to undo if you just deleted cells; 2) Find and replace #REF! with correct references; 3) Check VLOOKUP/INDEX column numbers don't exceed range; 4) Verify external workbook links are valid; 5) Use structured table references to prevent future errors.
  • Why did deleting a column cause #REF! errors? When you delete a column that's referenced by formulas, Excel can't find those cells anymore and shows #REF!. To prevent this: use named ranges, use structured table references (Table1[Column]), or use INDIRECT() for dynamic references. Always check for dependent formulas before deleting.
  • How do I find all #REF! errors in my workbook? Find all #REF! errors by: 1) Press Ctrl+F, click Options, search for #REF!; 2) Use Go To Special (Ctrl+G > Special > Formulas > Errors); 3) Use conditional formatting to highlight error cells; 4) In newer Excel, use =FILTER() with ISERROR() to list all errors.
  • Can I recover data after #REF! error? If you just caused the error, press Ctrl+Z immediately to undo. If the file was saved, check for AutoRecover versions (File > Info > Manage Workbook). For external links showing #REF!, update the link path. Unfortunately, once saved with #REF!, the original reference is lost.