Recognize Excel's error values and name the cause of each, so you can fix formulas quickly.
Excel errors: reading the “diagnosis”
The four most common errors
When Excel can't calculate a result, it shows an error code — a kind of “diagnosis” of the formula. Once you know the diagnoses, you know right away where to look for the cause.
#DIV/0! appears when you divide by zero or by an empty cell. For example, =A1/B1 where B1 is empty. Solution: make sure the divisor isn't zero, or protect the division: =IF(B1=0,"",A1/B1) or =IFERROR(A1/B1,"").
#N/A means “not available” — the value wasn't found. Most often it's VLOOKUP or MATCH failing to find a match in the table. Causes: a typo in the lookup value, extra spaces (TRIM helps), mismatched formats (text instead of a number). Wrap the formula in IFERROR to show an empty string or “—” instead of the error.
#REF! means the formula refers to a cell or column that no longer exists. A typical case: a row or column was deleted, but the formula still refers to it. Excel puts #REF! in place of the deleted address.
#NAME? signals a typo in a function name or unrecognized text. If you type =SUMM(A1:A5) instead of =SUM(A1:A5), you get #NAME?. It also appears when you type a function name from another language version of Excel — for example, the German SUMME in US Excel.
Lesson notes
The four most common errors
When Excel can't calculate a result, it shows an error code — a kind of “diagnosis” of the formula. Once you know the diagnoses, you know right away where to look for the cause.
#DIV/0! appears when you divide by zero or by an empty cell. For example, =A1/B1 where B1 is empty. Solution: make sure the divisor isn't zero, or protect the division: =IF(B1=0,"",A1/B1) or =IFERROR(A1/B1,"").
#N/A means “not available” — the value wasn't found. Most often it's VLOOKUP or MATCH failing to find a match in the table. Causes: a typo in the lookup value, extra spaces (TRIM helps), mismatched formats (text instead of a number). Wrap the formula in IFERROR to show an empty string or “—” instead of the error.
#REF! means the formula refers to a cell or column that no longer exists. A typical case: a row or column was deleted, but the formula still refers to it. Excel puts #REF! in place of the deleted address.
#NAME? signals a typo in a function name or unrecognized text. If you type =SUMM(A1:A5) instead of =SUM(A1:A5), you get #NAME?. It also appears when you type a function name from another language version of Excel — for example, the German SUMME in US Excel.