Excel Error Decoder

Type or select an error such as #N/A, #VALUE!, #REF!, #DIV/0! or #SPILL! and the Error Decoder explains what Excel is reporting, the usual causes, and the checks to run in order. Use it when a cell shows an error code and you are not sure where to start.

What the Excel Error Decoder does

Excel shows a short code when a formula cannot produce a valid result. The code tells you the category of problem, not the exact cell that caused it. The Error Decoder lists each common error code with a plain-English meaning, the situations that typically produce it, and a sequence of fixes to try.

Use it when a report suddenly fills with #N/A after new data is pasted in, when a formula works in one row but fails in the next, or when a dynamic array formula refuses to spill. You can search by the error code itself or by a description such as divide by zero or spill.

How to use it

  1. Click the cell that shows the error and note the exact code, including the punctuation.
  2. Type the code or a keyword into the search box on this page.
  3. Open the matching entry and read the meaning first, so you know what kind of problem to look for.
  4. Work through the listed causes in order. They start with the most frequent cause.
  5. In Excel, use Formulas > Evaluate Formula to step through the calculation and see which part fails.
  6. Once the cause is fixed, decide whether an IFERROR or IFNA wrapper is still needed. Hiding an error you do not understand can hide wrong results.

Worked examples

Division by an empty cell

Input: =A2/B2 where A2 is 500 and B2 is blank

Result: #DIV/0!

Why: Excel treats a blank cell as zero in arithmetic, and dividing by zero is undefined. =IF(B2=0,"",A2/B2) returns an empty string instead until B2 contains a value.

A lookup value with a trailing space

Input: =VLOOKUP("P-104",A2:C20,3,FALSE) where the table contains "P-104 " with a space

Result: #N/A

Why: An exact match compares every character, so the trailing space makes the two values different. Cleaning the column with TRIM, or looking up TRIM of the value, removes the mismatch.

A blocked spill range

Input: =SEQUENCE(5) entered in A1 while A3 contains text

Result: #SPILL!

Why: SEQUENCE(5) needs A1:A5 to be empty so it can return five values. Clearing A3 lets the result spill and the error disappears.

What each error means

#N/A
A lookup or match function could not find the value. Check spelling, extra spaces, and numbers stored as text.
#VALUE!
A formula received the wrong type of data, for example text such as "n/a" in a cell used in addition.
#REF!
A reference points to a cell that no longer exists, usually after rows, columns or a sheet were deleted.
#DIV/0!
A number was divided by zero or by a blank cell.
#NAME?
Excel does not recognise a name: a misspelled function, a missing named range, or text without quotes.
#SPILL!
A dynamic array formula needs empty cells to return its results, and something is in the way.

Frequently asked questions

Should I wrap every formula in IFERROR?

No. IFERROR hides every error type, including ones that point to real mistakes such as a deleted reference. Fix the cause first, then use IFNA for lookups where a missing value is expected.

Why does a lookup return #N/A when I can see the value?

The two values usually differ in a way that is hard to see: a trailing space, a non-breaking space, or a number stored as text in one column and as a number in the other.

How do I find which part of a long formula fails?

Use Formulas > Evaluate Formula in Excel. It calculates the formula one step at a time and underlines the part about to be evaluated.

Can #REF! be undone?

If you just deleted the cells, Undo restores the reference. Otherwise the formula must be edited to point to the correct range again.

Related