Fixes / Excel · Checked

Excel #REF! error: what it means and how to fix it

Short answer

#REF! means a formula refers to a cell, range or sheet that is no longer valid, usually because a row, column or worksheet it used was deleted or pasted over. If it just happened, press Ctrl+Z (Cmd+Z on Mac) to undo. Otherwise click the cell, find #REF! in the formula bar, and replace it with the correct cell or range.

What this message means

#REF! is Excel's "invalid reference" error. A formula was built to read a specific cell, range or sheet, and that target no longer exists or cannot be reached. When you delete a column that a formula used, Excel rewrites the formula and puts the literal text #REF! where the cell address used to be, so =SUM(B2,C2,D2) becomes =SUM(B2,#REF!,C2). The same error appears when a lookup asks for a column or row outside its range (VLOOKUP, INDEX), when INDIRECT points to a closed workbook, or when a copied formula shifts off the edge of the sheet. Other cells that depend on the broken cell show #REF! too.

Common causes

CauseHow to tell
A row, column or cell the formula used was deleted, or cells were pasted over itClick the error cell and look at the formula bar. You will see #REF! inside the formula itself, for example =SUM(B2,#REF!,C2) or =A5*#REF!.
A worksheet the formula referred to was deletedThe formula bar shows something like =#REF!A1 or =SUM(#REF!B2:B10) where a sheet name such as Sheet2! used to be.
VLOOKUP or HLOOKUP asks for a column (or row) number larger than the table rangeThe formula looks intact, but the third argument is bigger than the number of columns in the range, e.g. =VLOOKUP(A8,A2:D5,5,FALSE) where A2:D5 has only 4 columns.
INDEX asks for a row or column outside its rangeThe formula looks intact, but the row or column number exceeds the range size, e.g. =INDEX(B2:E5,5,5) on a 4 by 4 range.
INDIRECT (or a dynamic array formula) points to a workbook that is closed, or to an invalid addressThe formula uses INDIRECT with a file name like "[Budget.xlsx]Sheet1!A1". It shows #REF! while that file is closed and works as soon as you open it.
A copied formula with relative references was pasted where it would point off the sheetThe original formula works, but the copy shows #REF!. For example, =A1 in cell B2 pasted into B1 would need a row above row 1, so the copy becomes =#REF!.

How to fix it

1. Undo the deletion right away

  1. If the error appeared just after you deleted a row, column, sheet or pasted over cells, press Ctrl+Z on Windows or Cmd+Z on Mac.
  2. Keep pressing it until the deleted cells come back and the #REF! errors disappear.
  3. Instead of deleting the cells again, clear only their contents (select them and press Delete) so the formulas keep a valid reference.
  4. Note: deleting a worksheet cannot be undone with Ctrl+Z. If you deleted a sheet, close the file without saving and reopen it, or restore an earlier version (File > Info > Version History in Microsoft 365 with OneDrive or SharePoint).

2. Rewrite the broken reference in the formula

  1. Click the cell showing #REF! and look at the formula bar.
  2. Find the text #REF! inside the formula. That is the spot where a cell, range or sheet name was lost.
  3. Replace #REF! with the correct address, for example change =SUM(B2,#REF!,C2) to =SUM(B2,C2) or to the new cell you need.
  4. If the whole formula is =#REF!, retype the formula from scratch pointing at the data where it now lives.
  5. Press Enter, then fix the first broken cell in a chain first, since cells that depend on it will clear automatically.

3. Fix VLOOKUP, HLOOKUP or INDEX ranges

  1. Click the cell and check the column (or row) number in the formula against the size of the range.
  2. For VLOOKUP, either widen the range, e.g. =VLOOKUP(A8,A2:E5,5,FALSE), or lower the column number, e.g. =VLOOKUP(A8,A2:D5,4,FALSE).
  3. For INDEX, keep the row and column numbers within the range, e.g. =INDEX(B2:E5,4,4) for a 4 by 4 range.
  4. To stop this happening when columns are inserted or removed later, use XLOOKUP (Microsoft 365 and Excel 2021 or later) or INDEX with MATCH instead of a hard-coded column number.

4. Open the workbook that INDIRECT or an external link points to

  1. Click the cell and check whether the formula uses INDIRECT or names another file in square brackets, such as [Sales.xlsx].
  2. Open that other workbook in the same Excel session. INDIRECT cannot read closed workbooks and returns #REF! until the source is open.
  3. If the file was renamed or moved, go to Data > Queries & Connections group > Edit Links (Workbook Links in newer Microsoft 365 builds) and use Change Source to point to the new file.
  4. If the formula is not INDIRECT, also check that the address it builds is valid and within 1,048,576 rows and 16,384 columns (column XFD).

5. Find every #REF! in the sheet at once

  1. Press Ctrl+F (Cmd+F on Mac) to open Find.
  2. Type #REF! in the Find what box, open Options, and set Look in to Formulas.
  3. Click Find All. The list shows every cell whose formula contains #REF!. Click each one to fix it.
  4. Alternatively, go to Home > Find & Select > Go To Special, choose Formulas, tick only Errors, and click OK to select all error cells.
  5. On Windows, Formulas > Formula Auditing > Error Checking > Trace Error draws arrows to the cell that caused the error, which helps in long chains.

6. Make formulas harder to break next time

  1. Use ranges instead of lists of single cells: =SUM(B2:D2) adjusts itself when a column inside the range is deleted, while =SUM(B2,C2,D2) breaks.
  2. Turn source data into a table (Ctrl+T on Windows, Cmd+T on Mac) and refer to column names, which follow the data when it moves.
  3. Use absolute references with $ (for example $A$1) where a formula must always point to the same cell after copying.
  4. Only use IFERROR, e.g. =IFERROR(your_formula,""), to hide errors after you have confirmed the reference is correct. It hides the symptom, it does not repair a lost reference.

Next time, skip the search

Crade is an AI assistant for Mac and Windows that sees your screen. When a message like this pops up, ask “what does this mean?” and it explains the exact error in front of you, step by step.

Download Crade

Still not fixed?

If the formula bar shows =#REF! with no trace of the original address, the reference is gone and Excel cannot recover it. Close the workbook without saving and reopen the last saved copy, or restore an earlier version from File > Info > Version History (OneDrive or SharePoint) or from your backup. For formulas that use OLE or DDE links to other programs, make sure the source program is running and check File > Options > Trust Center > Trust Center Settings > External Content, since blocked external content can also produce #REF!. In a shared company workbook, ask the file owner or your IT team whether a linked file or sheet was renamed, moved or deleted.

Questions people ask

What does #REF! mean in Excel?

It means the formula refers to a cell, range or sheet that is not valid. Most often the referenced cells were deleted or pasted over, so Excel no longer knows where to look.

Can I get back a reference after it turned into #REF!?

Only by undoing (Ctrl+Z or Cmd+Z) or reopening an earlier saved version. Once the file is saved, Excel stores the literal text #REF! in the formula and the original address is lost, so you have to retype it.

Why does VLOOKUP return #REF!?

The column number you asked for is larger than the number of columns in the lookup range. Widen the range or reduce the column number, or switch to XLOOKUP or INDEX with MATCH.

How do I replace #REF! errors with 0 or a blank?

Wrap the formula in IFERROR, for example =IFERROR(A1*B1,0). This only hides the error, so first check the reference really should be empty rather than broken.

Why does my INDIRECT formula show #REF! only sometimes?

INDIRECT returns #REF! whenever the workbook it points to is closed. It starts working again as soon as that file is open in Excel.

Sources

Related errors