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:

=SUM(C5:C9) // original C10
=SUM(#REF) // when copied to 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.

If you cause a #REF! error, it's best to fix immediately. For example, if you delete a column, and #REF! errors appear, undo that action (you can use the shortcut Control + Z). When you undo, the #REF! errors will disappear. Then edit the formula(s) to exclude the column you want to delete, moving data if needed. Finally, delete the column and confirm there are no #REF! errors.

Clearing multiple #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 Find and Replace dialog.  Enter #REF! in the find input area, and leave the replace input area 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.

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. Just edit the formula and replace #REF! with a valid reference. 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.

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 workbook (or save a copy) before deleting one or more sheets.

#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.