Excel practice › Excel errors
Excel formula errors: what each one means, with a broken formula to fix
An error code tells you what kind of thing went wrong, not where. Each page lists the causes in the order worth checking, and reproduces them in a working sheet: the formula is broken, and you fix it.
Lookups that fail
- VLOOKUP not workingWhy a VLOOKUP returns #N/A, the wrong value or 0, and the fix for each of the 8 causes.
Error codes
- #N/A errorA lookup didn't find its value. The causes, and how to show a blank, a 0 or a message with IFNA.
- #VALUE! errorA formula got the wrong kind of value, usually text where it needs a number. How to find the cell and fix it.
- #REF! errorThe formula points at a cell or a column that no longer exists. What to do after a deletion, and in a lookup.
- #DIV/0! errorA division by zero or by an empty cell, or an average of nothing. When to use IF and when IFERROR.
- #NAME? errorExcel doesn't recognize a word in the formula: a misspelled function, text without quotes, or a function your version lacks.
- #SPILL! errorA formula returns several values and a cell in the range it needs isn't empty. What to clear, and when to change the formula.