How to fix the #REF! error
About the #REF! error
The #REF! error occurs when a reference is invalid. In many cases, this is because sheets, rows, or columns have been removed, or because a formula with relative references has been copied to a new location where references are invalid. In the example shown, the formula in C10 returns a #REF! error when copied to cell E5:
Preventing #REF errors
The best way to prevent #REF! errors is to prevent then from occurring in the first place. Before you delete columns, rows, or sheets be sure they aren't referenced by formulas in the workbook. If you are copying and pasting a formula to a new location, you may want to convert some cell references to an absolute reference to prevent changes during the copy operation.
Fixing #REF errors
#REF errors are somewhat tricky to fix because the original cell reference is gone forever. If you know what the reference should be, you can simply fix it manually. If you don't know what the cell reference should be, you may have to study the worksheet in more detail before you can repair the formula.
Because of the above, it's best to fix #REF errors immediately. For example, if you delete a column, and see one or more #REF errors appear, undo that action. When you undo, the #REF errors disappear, replaced by original formulas. Then, if needed, you can edit the formulas to exclude the column you want to delete, then remove the column.
Note: you can't undo the deletion of a sheet in Excel. If you delete a worksheet tab, and see #REF errors, your best option is probably to close the file and re-open the last saved version. For this reason, always save a copy of a workbook before deleting one or more sheets.
Clearing #REF errors
To quickly remove many #REF errors from a worksheet, you can use Find and Replace. Use the shortcut Control + H to open the dialog, and enter #REF! in the find input area, and lave the replace input blank:
You can then make case-by-case changes with Find next + Replace, or use Replace All to replace all #REF errors in one step.
#REF! errors with VLOOKUP
You may see a #REF! error with the VLOOKUP function, when a column is specified incorrectly. In the screen below, VLOOKUP returns #REF! because there is no column 3 in the table range, which is B3:C7:
When the column index is set to the correct value of 2 the #REF! error is resolved and VLOOKUP works properly:
Note: you may also see a #REF error with the INDEX function when a row or column reference is not valid.
Trapping the #REF! error with IFERROR
In most cases, it doesn't make sense to trap the #REF! error in a formula, because #REF! indicates a low-level problem. However, one situation where IFERROR may make sense to catch a #REF error is when you are building references dynamically with the INDIRECT function.