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.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
