Explanation
About the #REF!
error
The #REF!
error shows up when a reference is invalid.
In the example shown, the formula in C10 returns a #REF!
errors is to prevent then from occurring in the first place.
If you cause a #REF!
error, it’s best to fix immediately.
For example, if you delete a column, and #REF!
When you undo, the #REF!
Finally, delete the column and confirm there are no #REF!
Clearing multiple #REF!
Use theshortcutControl + H to initiate the Find and Replace dialog.
Just edit the formula and replace #REF!
with a valid reference.
Note: you’re able to’t undo the deletion of a sheet in Excel.
#REF!
errors with VLOOKUP
You may see a #REF!
error with theVLOOKUP function, when a column is specified incorrectly.
In the screen below, VLOOKUP returns #REF!
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.
error
How to fix the #REF!
error
How to fix the #NAME?
error
How to fix the #VALUE!
error
How to fix the #NUM!
error
How to fix the #NULL!
error
How to fix the #CALC!
you might use the ISREF function to check for a reference in a formula.
IFERROR is an elegant way to trap and manage errors without using more complicated nested IF statements.
Related videos
Excel formula error codes
