This tutorial demonstrates how to find, interpret, and fix #REF! errors in Excel and Google Sheets.
Locate #REF! Cells in Excel
An #REF! error in Excel is a message that is displayed when a formula refers to an invalid cell. Usually, the cell is not valid because it was deleted or pasted over.
Say that, in cell C2, you have the formula: “=B2+B3+B4”.
Suppose you deleted Row 3, causing the #REF! error.
Note: If you are referencing the cells as a continuous range (e.g., “=SUM(B2:B4)”), you won’t get a #REF! error after deleting a row or a column. Also, if you are specifically referencing cells and want to delete some of them, just delete the cell values. Don’t delete the whole row, and you won’t produce the #REF! error.
To locate the #REF! cells on your worksheet just follow these steps:
- In the Ribbon, go to Home > Find & Select > Go To Special.
- In the Go To Special window, select Formulas and uncheck all boxes except Errors. Click OK.
As a result, all the cells with the #REF! error message are selected.
Note: It’s also possible to cause a #REF! error by deleting a column or row, by having an incorrect row or a column reference in an INDEX Function, or by using incorrect range references in a VLOOKUP Function. Issues with OLE (Object Linking and Embedding), DDE (Dynamic Data Exchange), or macros can also cause the #REF! error.
Fix #REF! Errors
The best way to fix the problem above is to use Find & Replace feature in Excel. Replacing the #REF! with blanks deletes the reference errors from your formulas.
- In the Ribbon, go to Home > Find & Select > Replace (or use the CTRL + F shortcut to open the Find & Replace window).
- After that the Find & Replace window will open. (1) In the Find what: box type #REF! and (2) Replace with: box leave empty. Then (3) press Replace All and (4) click Close.
- The pop-up information will inform you how many replacements were made. To close it, click OK.
As a result, the #REF! error is fixed.
Other Potential Reasons For #REF! Error and Solutions
VLOOKUP With Incorrect Range Reference
In this example, “=VLOOKUP(F2,$B$2:$C$6,3,FALSE)” returns the #REF! error, because it’s looking to return the value from the 3rd column, but the referenced range is $B$2:$C$6, which has only 2 columns. The solution is to correct the column lookup value to match the reference range (in this example: “=VLOOKUP(F2,$B$2:$C$6,2,FALSE)”).
Another way to get rid of the #REF! error is to use the IFERROR Function. If there is an error in the formula the text from the function is displayed instead of #REF! (error in this example).
INDEX With Incorrect Row or Column Reference
The #REF! error is displayed in this example (“=INDEX(B2:C6,3,3)”), because it’s looking to return the value from the 3rd row, 3rd column but the INDEX range is 5 rows by 2 columns. To correct this, choose a valid reference column (“=INDEX(B2:C6,3,2)”).
As a result, the #REF! error disappears, and cell E2 shows the correct value.
You can also use the IFERROR Function to get rid of #REF! error message.
Fix #REF! Errors in Google Sheet
#REF! errors in Google Sheets work very similar to those in Excel. In the case of a deleted cell causing the error, you can fix it with Find and replace.
Say that, in cell C6, you have the formula: “=SUM(C2+C3+C4+C5+C6)”.
If you delete Row 3, for example, that causes a #REF! error.
To fix #REF! error in Google Sheets, follow these steps:
- In the Menu, go to Edit and choose Find & Replace.
- The Find and replace window will open. (1) In the Find box enter #REF! and (2) the Replace box leave blank. After that, (3) click Find and (4) Replace All. (5) Press Done.
As a result, the error is fixed.