Exceljet

Quick, clean, and to the point

How to fix the #REF! error

Excel formula: How to fix the #REF! error
Explanation 

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.

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:

Clear REF errors with find and replace

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:

Example of #REF! error with VLOOKUP

When the column index is set to the correct value of 2 the #REF! error is resolved and VLOOKUP works properly:

Example of #REF! error resolved with VLOOKUP

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.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.