Nine Ways a Cell Can Say No: Excel's Error Values, IFERROR, IFNA and the 2,952.75 Somebody Turned Into a Zero

The September partner payout run totals 5,995.44. Twelve orders, one commission column, no red cells, nothing flagged. Finance approves it and the money goes out.

The correct number was 8,948.19.

Nothing in that sheet was broken. The formulas were right, the rate card was right, the source data came straight out of the order system. What went wrong is that somebody, at some point, got tired of looking at red cells and wrapped the commission column in IFERROR(…, 0). Two of the twelve rows had genuine problems. IFERROR turned both into zeros, and zeros add up perfectly quietly.

That is the whole argument of this article. An error value is not a malfunction — it is a message. Excel is telling you something specific about a specific cell, in one of nine different words, and each word means a different thing. Suppressing the message does not answer it; it just moves the cost from a red cell somebody would have fixed to a total nobody will ever check.

What you need. Sections 1 to 11 work in every version of Excel this century and in Google Sheets, with the noted exceptions. IFNA is Excel 2013 and later. XLOOKUP and its if_not_found argument, #SPILL! and #CALC! are Microsoft 365 and Excel 2021 or later.

1) A Payout Sheet That Is 2,952.75 Light

🎯 Scenario: Month end. Twelve partner orders came out of the order system, the rate card lives on another tab, and the commission run has to be approved this afternoon. Every cell in the commission column shows a number.

The rate card is five rows on another sheet:

The commission is the obvious thing. Order value in G2:

=C2*D2

and commission in H2:

=G2*XLOOKUP(B2, RateCard[Partner Code], RateCard[Rate])

Fill both down and the column is not clean. Two rows go red:

Two red cells in a column of twelve is annoying, and the reflex is to make them go away:

=IFERROR(G2*XLOOKUP(B2, RateCard[Partner Code], RateCard[Rate]), 0)

The column is now clean. =SUM(H2:H13) returns 5,995.44, and here is what that number is made of:

2,952.75 underpaid, and the sheet has no memory of it. There is no red cell, no comment, no audit trail — the two zeros look exactly like the legitimate zero on row 4, where a cancelled order genuinely earns nothing.

The order value column tells the same story more starkly. =SUM(G2:G13) reads 68,233.50; the real order value for the month is 95,533.50. One text cell, 27,300.00 of missing revenue.

The error values were doing their job. Somebody turned the smoke alarm off and went back to sleep.

2) The Nine Errors and What Each One Accuses You Of

Excel has nine error values you will meet in ordinary work, plus a handful of newer ones tied to data types and connections. They are not interchangeable, and the fastest way to fix a spreadsheet is to read the error as a sentence rather than as "broken".

Two rules cut across all nine.

Errors are values. #N/A is as much a value as 42 or "Shipped". It can be stored in a cell, returned by a function, passed as an argument and tested for. That is why =A1=#N/A does not work — #N/A typed into a formula is not a literal — and why =ISNA(A1) does.

Errors propagate. Any formula that takes an error as an argument returns an error, and by default it returns that error, not a new one. This is a feature: it means the #VALUE! you are staring at in the grand total was born somewhere else, and section 13 is about finding where.

3) #VALUE! — the Argument Is the Wrong Shape

#VALUE! is the most common error in imported data and almost always means the same thing: a cell you believe holds a number holds text instead.

D5 in the example grid displays 1,050.00. It looks like the other prices. It is left-aligned rather than right-aligned, which is Excel's only visual hint, and most people have never been told to read that. =ISTEXT(D5) returns TRUE; =ISNUMBER(D5) returns FALSE; =C5*D5 returns #VALUE!.

The diagnostics are one line each:

=ISNUMBER(D5)              FALSE — this is the whole problem
=COUNT(D2:D13)             11, not 12 — COUNT counts numbers only
=SUMPRODUCT(--ISTEXT(D2:D13))   1 — how many text cells are hiding in the column

That COUNT check is worth making a habit. On any column that should be numeric, COUNT against COUNTA tells you in one glance whether the column is what you think:

=COUNTA(D2:D13)-COUNT(D2:D13)    → 1 text cell in a numeric column

Fixing it properly. The formula workaround is =C5*VALUE(SUBSTITUTE(D5,",","")), and it is a workaround — it patches one cell and leaves the underlying data wrong for the next person. Fix the values instead:

• Select the column → Data → Text to Columns → Finish. A one-step no-op that re-parses every cell and converts everything Excel can read. It is faster than any formula and it fixes the data rather than the symptom.
• Paste Special ▸ Multiply by 1 from an empty cell, which coerces text-numbers in place.
• If the export does this every month, fix it in Power Query at the point of import, where the column gets a declared type once and stays that way.

The other #VALUE! causes, in rough order of frequency:

• A space in a cell that looks blank. =LEN(D5) on a "blank" cell returning 1 is the tell.
• A date stored as text being used in arithmetic — ="15/09/2026"-1 is #VALUE!, =DATEVALUE("15/09/2026")-1 is not.
• Handing a function a range where it wants one cell, such as =LEFT(A2:A13, 3) in a version without dynamic arrays.
• The wrong argument count or order — =DATE(2026, 9) with a missing day.

4) #N/A — the Only Error That Is Often Telling the Truth

#N/A is different from the other eight. The others say your formula is wrong. #N/A says "I looked, and it is not there" — which may be a perfectly accurate description of reality.

PT-047 in B7 is a real partner. Its orders are real, its 150 units at 24.75 are real revenue. It is simply not on the rate card, because whoever onboarded it never added the row. XLOOKUP returning #N/A is not a malfunction; it is the sheet reporting an incomplete rate card, in the only way it has.

This is why the fix for #N/A is so often not a formula:

The mismatch case deserves a note, because it produces an #N/A that looks impossible. "PT-014" and "PT-014 " are different strings. "0300412" as text and 300412 as a number are different values. Both look identical on screen and neither will ever match. Before you write a fallback, check whether the key is really absent:

=COUNTIF(RateCard[Partner Code], B7)          0 — not there under any spelling
=COUNTIF(RateCard[Partner Code], "*"&TRIM(B7)&"*")   1 — it is there, with whitespace

If those two disagree, you have a data-cleaning job, not a lookup problem.

Approximate match is the silent version of this error. VLOOKUP with the fourth argument omitted defaults to approximate match, which does not return #N/A when it fails to find your key — it returns the nearest value below it, confidently and wrongly. An unsorted table plus a missing fourth argument gives you the worst possible outcome: a plausible number where an #N/A should have been. Always write FALSE (or 0) as the fourth argument, or use XLOOKUP, which is exact by default.

5) #DIV/0! — Two Different Problems Wearing One Face

Row 4 of the example is a cancelled order: zero units, zero ship days. Any per-unit or per-day figure on that row divides by zero.

=H4/C4      #DIV/0!   — commission per unit, on an order with no units
=H4/E4      #DIV/0!   — commission per shipping day, on an order that never shipped

Excel also produces #DIV/0! when the denominator is empty, because an empty cell coerces to zero in arithmetic. That matters, because "zero" and "not filled in yet" are very different situations that produce an identical error.

Ask which one you have, because the honest answer differs:

The pattern that keeps the distinction visible:

=IF(C4=0, "—", H4/C4)

Test the denominator, not the result. That is the difference between "this ratio does not apply" and "catch anything that goes wrong here", and only the first one is a statement about your data.

The trap: =IFERROR(H4/C4, 0) reads as harmless and is not. A commission-per-unit of 0 is a claim that this order earned nothing per unit, which is false — it earned nothing per unit because there were no units, and those are different sentences. Feed a column of those into an AVERAGE and the average is wrong, quietly, in the direction of zero. Section 12 has the arithmetic.

6) #NAME? — Excel Does Not Recognise a Word

#NAME? means Excel found something in your formula it cannot resolve to a function, a name or a reference. It has five common causes and they are worth telling apart, because one of them is not your fault.

A typo in a function name. =XLOOOKUP(…), =SUMIFS written =SUMIFFS. Excel's autocomplete usually prevents these, which is why they mostly appear in formulas that were pasted in from somewhere else.

A missing quote around text. This is the one that catches people who know what they are doing:

=IF(F2=Shipped, H2, 0)      #NAME?  — Shipped is read as a name that does not exist
=IF(F2="Shipped", H2, 0)    correct

Excel does not have a category for "bare word", so an unquoted Shipped is parsed as a defined name, and there is no such name.

A named range that does not exist. =SUM(Commission) when the name is actually Commissions, or when the name was defined in a different workbook and did not travel with the sheet. Ctrl+F3 opens the Name Manager; a name with a #REF! value in it is a broken name, and it will hand #NAME? to everything that uses it.

A function your Excel does not have. XLOOKUP, TEXTSPLIT, LET and LAMBDA all return #NAME? in older versions. This is the important case, because the formula is correct — it just cannot run here. A workbook that opens fine on your machine and shows #NAME? on a colleague's is almost always this. When Excel loads a file containing a function it does not know, it prefixes it with _xlfn., so a formula bar reading =_xlfn.XLOOKUP(...) is telling you exactly that.

A translated function name. Excel translates function names by display language. A Spanish installation writes =SI.ERROR(...); typing =IFERROR(...) into it gives #NAME?, and the reverse is equally true. The file format stores the English name, so saved workbooks travel fine — it is typed and pasted formulas that break.

7) #REF! — the Only Error That Cannot Be Repaired

Every other error on this list can be fixed by editing the formula or the data. #REF! cannot, because the information it needed is gone.

When you delete a row, column or sheet, Excel updates every formula that pointed into it. There is nothing sensible to update those references to, so it writes #REF! into the formula itself:

=C2*D2          before deleting column D
=C2*#REF!       after

The formula has been rewritten. The original reference is not recoverable, because it no longer exists to be recovered — you have to know what it used to be and type it again. Ctrl+Z immediately after the deletion is the only real cure, which is why noticing matters more here than anywhere else.

The other common source is a lookup asking for a column that is not in its range:

=VLOOKUP(B2, RateCard!A:B, 3, FALSE)     #REF! — the range is two columns wide

VLOOKUP is unusually good at generating these, because its column index is a number counted from the left edge of the range and nothing keeps the two in step. Insert a column inside the lookup range and the index silently points at the wrong column — that one does not even give you a #REF!, it gives you a wrong answer. INDEX/MATCH and XLOOKUP avoid the whole class by referring to columns as ranges rather than as counted positions.

Prevention beats cure. Before deleting anything a formula might depend on, select it and use Formulas ▸ Trace Dependents to see what is downstream. On anything shared, converting the source to a Table and referring to it by structured reference means inserted and deleted columns keep their meaning instead of their position.

8) #NUM! and #NULL! — the Two You Meet Rarely

#NUM! means the arithmetic is valid but the answer is not. The maths was well-formed and there is simply no number at the end of it:

=SQRT(-4)                        #NUM!  — no real square root
=DATE(1899, 12, 31)              #NUM!  — before Excel's calendar begins
=DATEDIF(B2, A2, "d")            #NUM!  — the end date is before the start date
=IRR(C2:C13)                     #NUM!  — no sign change, so no rate exists
=RATE(360, -1200, 100000)        #NUM!  — did not converge in 20 iterations

The DATEDIF case is the one that turns up in real work, because it is almost always an argument order mistake — DATEDIF takes start date first, and swapping them gives #NUM! rather than a negative number. The RATE and IRR cases usually want a guess: both take an optional final argument, and =IRR(C2:C13, -0.1) will often converge where the default 0.1 will not.

#NULL! means an intersection that is empty, and it is nearly always a typo. Excel's space character is the intersection operator: =SUM(C2:C13 E2:E13) asks for the cells those two ranges have in common, which is nothing.

=SUM(C2:C13 E2:E13)     #NULL!  — space, meaning "intersect"
=SUM(C2:C13, E2:E13)    correct — comma, meaning "and"

If you see #NULL!, look for a space where a comma belongs. It is the rarest of the nine and there is very little else that causes it.

9) #SPILL! and #CALC! — the Modern Pair

These two only exist in Microsoft 365 and Excel 2021 and later, and both are about dynamic arrays.

#SPILL! means the result has nowhere to go. =UNIQUE(B2:B13) wants to write five partner codes down five cells. If anything is sitting in any of them — a number, a stray space, a merged cell — the whole formula refuses rather than overwriting:

=UNIQUE(B2:B13)     #SPILL!  — something is in the way

Click the cell, open the warning triangle, and choose Select Obstructing Cells; Excel highlights exactly what is blocking it. The usual culprits are an old heading, a cell containing a single space, and a merged cell anywhere in the spill range — dynamic arrays and merged cells cannot coexist at all.

There is a second, sneakier #SPILL!: a whole-column reference inside a function that returns an array of the same size. =UNIQUE(B:B) wants a million rows, which do not fit below row 2. Reference the data range, or the Table column, rather than the whole column.

#CALC! means the calculation produced something Excel cannot put in cells, and in practice it means an empty array:

=FILTER(A2:A13, F2:F13="Returned")      #CALC!  — nothing has that status
=FILTER(A2:A13, F2:F13="Returned", "none")   "none"

Every FILTER that could legitimately match nothing should carry the third argument. It is not an error handler — it is part of the specification of what the formula should do when the answer is "no rows", which for a filter is a normal outcome rather than a fault.

10) IFERROR Is a Blindfold; IFNA Is a Scalpel

This is the section the 2,952.75 was about.

IFERROR(value, value_if_error) catches every error there is. #N/A, #VALUE!, #REF!, #NAME?, #DIV/0!, #NUM!, #NULL!, #SPILL!, #CALC! — all of them, replaced with whatever you put in the second argument. That is a very large net, and the fish you did not want to catch are the ones that mattered.

Consider the payout formula again, and what each fallback does to each problem:

The middle row is the one that shipped. The bottom two are what should have shipped: the #N/A handled deliberately because it was expected, and everything else left alone to be seen and fixed.

IFNA catches #N/A and nothing else. It is the right tool for exactly one job — a lookup where "not found" is a foreseen outcome with a defined answer:

=IFNA(XLOOKUP(B2, RateCard[Partner Code], RateCard[Rate]), 0)

If the rate card gets a column deleted tomorrow, this formula goes #REF! and somebody fixes it. The IFERROR version reports "no rate for this partner" for the entire column and everyone believes it.

XLOOKUP's if_not_found is better still, because it is scoped to the lookup rather than to the whole expression:

=G2*XLOOKUP(B2, RateCard[Partner Code], RateCard[Rate], 0)

IFNA wrapped around the whole formula would also catch an #N/A arriving from G2. The fourth argument catches only the lookup's own failure to find, which is the narrowest and therefore the most honest scope available.

Choose the fallback value with care. 0 is a claim about money. If you cannot defend the claim, do not make it:

A commission column with three cells reading No rate card will not tie out to a total, and that is the point: it cannot be approved without somebody looking at those three rows. The version that read 0 was approved in four minutes.

One performance note. The old pattern =IF(ISERROR(x), fallback, x) evaluates x twice. IFERROR and IFNA evaluate it once. On a slow lookup filled down ten thousand rows that is a straight halving, and there is no reason to write the old form in any version since 2007.

11) Diagnosing Instead of Hiding: IS-functions, ERROR.TYPE and NA()

If the goal is a sheet somebody can act on, the answer is not to suppress errors but to name them. Excel gives you the tools in one line each.

That ISERR is not a typo of ISERROR — it exists precisely because #N/A is the odd one out, and it lets you split "the data is incomplete" from "the formula is broken" in a single test:

=IF(ISNA(H2), "Partner not on rate card",
   IF(ISERR(H2), "Formula fault — check " & CELL("address", H2), ""))

Fill that down beside the commission column and the sheet stops being a wall of red. It says Partner not on rate card on row 7 and Formula fault on row 5, and the person reading it knows which of the two jobs is theirs.

ERROR.TYPE names the error as a number, which is what you want when the diagnosis has to be exact:

ERROR.TYPE on a cell with no error returns #N/A, which is faintly comic and easy to work around. For the seven classic errors, numbered 1 to 7:

=IF(NOT(ISERROR(H2)), "ok", CHOOSE(ERROR.TYPE(H2),
   "empty intersection", "divide by zero", "wrong data type",
   "reference deleted", "unknown name", "impossible number", "not found"))

A count of what is wrong, for the top of the sheet:

=SUMPRODUCT(--ISERROR(H2:H13))      2 — total errors in the commission column
=SUMPRODUCT(--ISNA(H2:H13))         1 — of which are missing lookups

Two cells at the top of a payout sheet reading Errors: 2 and Missing rates: 1 would have stopped the 5,995.44 before it left the building.

NA() creates an #N/A on purpose, and there is one situation where that is exactly right. Charts skip #N/A points and draw a gap; they plot zeros as a line falling to the axis. A monthly series where September has not been reported yet should read =NA(), not 0, or the chart will show a collapse that did not happen:

=IF(D2="", NA(), C2*D2)

AVERAGE also treats these correctly by refusing to average them, which brings us to the aggregates.

12) Which Aggregates Propagate Errors and Which Ignore Them

One error in a column of twelve breaks the total, because SUM propagates. That is by design, and it is more useful than the alternative — a total that silently omitted a row would be worse than one that refuses to compute. But it means you need to know which functions do which.

AGGREGATE is the clean answer when you need a total from a column that legitimately contains errors:

=AGGREGATE(9, 6, H2:H13)     sum, ignoring errors
=AGGREGATE(1, 6, K2:K13)     average, ignoring errors

And this is where IFERROR(…, 0) does its second kind of damage. Take commission per unit in K2 as =H2/C2, filled down. Row 4 is #DIV/0! — a cancelled order with no units. Three ways to average that column:

The middle answer is right and the bottom one is plausible, which is what makes it dangerous. IFERROR(…, 0) did not skip the cancelled order — it replaced it with a claim that the order earned 0.00 per unit, and that claim then got averaged like any other number, dragging the result 8% low with nothing on screen to say so.

The rule that follows: IFERROR to zero is correct when zero is the answer, and wrong when the value is unknown or undefined. A cancelled order's commission per unit is undefined. Skip it; do not zero it.

13) Finding the Cell That Actually Broke

Errors propagate, so the red cell you are looking at is usually not the one at fault. Four tools find the source, and all four are faster than reading formulas.

Trace Error (Formulas ▸ Error Checking ▸ Trace Error) draws arrows from the error cell back to its precedents, in red for the ones carrying the error. On a chain three or four steps deep this is the fastest thing in Excel.

Evaluate Formula (Formulas ▸ Evaluate Formula) steps through the calculation one operation at a time and shows the intermediate value at each step. When the display flips to #VALUE!, the argument that just got substituted is the culprit. This is the tool for a long nested formula where you cannot tell which of five arguments went wrong.

F9 on a selection does the same thing without leaving the formula bar: select any fragment of a formula in edit mode, press F9, and Excel replaces it with its current value. Press Esc — not Enter — to leave the formula intact.

Go To Special ▸ Formulas ▸ Errors (F5 ▸ Special) selects every error cell on the sheet at once, which turns "how many are there and where" into a single keystroke. Combine it with a fill colour to mark them all before you start fixing.

Error checking rules (File ▸ Options ▸ Formulas) control the green triangles. Two of them are worth knowing about specifically:

• Number stored as text is the rule that would have flagged D5 on sight. It is on by default and it is the single most useful one.
• Formulas inconsistent with other formulas in the region catches the hand-edited cell in the middle of a filled-down column, which is a whole class of wrong answers that never produces an error at all.

And a habit rather than a tool: on any sheet that matters, put a validation cell somewhere visible.

=IF(SUMPRODUCT(--ISERROR(G2:H13))=0, "OK", SUMPRODUCT(--ISERROR(G2:H13)) & " errors")

It costs one cell and it fails loudly, which is the entire point of everything above.

14) Common Traps

1. IFERROR around a whole formula when you meant a lookup. It catches the #REF! from a deleted column, the #NAME? from a version mismatch and the #VALUE! from a text number, and reports all of them as your fallback. Use IFNA, or XLOOKUP's fourth argument.
2. IFERROR(…, 0) on a ratio. Zero is a value, not an absence. It joins every average and every total downstream as a real number.
3. IFERROR applied before the data is checked. Wrapping an import in error handling on day one means you never find out that a column arrived as text.
4. Assuming #N/A is a bug. It is often the most accurate cell on the sheet. Add the missing row to the lookup table instead of silencing the formula that told you it was missing.
5. VLOOKUP without the fourth argument. Approximate match does not return #N/A when it fails — it returns a wrong number. The error you are not getting is worse than the one you are.
6. Deleting a column and not noticing the #REF!. Undo is the only cure, and only in the moment. Check dependents before deleting anything.
7. Reading a #NAME? as your mistake. If the formula works on your machine and breaks on a colleague's, it is a version or a language difference, not a typo. Look for the _xlfn. prefix.
8. Google Sheets differences. IFERROR and IFNA exist and behave the same way. AGGREGATE does not exist at all, so an error-skipping total has to be SUMIF, FILTER or IFERROR per row. ERROR.TYPE exists but numbers the errors differently, and Sheets has #ERROR! for parse failures, which Excel has no equivalent of.

Conclusion

Every error value in Excel is a sentence, and the nine sentences are not the same sentence. #VALUE! says an argument is the wrong shape. #N/A says the lookup was honest. #REF! says something was deleted and is not coming back. #DIV/0! says the question has no answer. Reading them as "the spreadsheet is broken" throws away the most specific diagnostic information a spreadsheet ever gives you.

The 2,952.75 was not lost to a difficult formula. It was lost to a single decision to make red cells stop being red, taken by somebody who was almost certainly in a hurry and did not think of it as a decision about money. IFERROR(…, 0) is a very small piece of typing with a very large blast radius, and its damage is invisible by construction — the whole function of a zero is that it does not draw attention.

So the discipline is narrow and it is worth holding to. Handle the error you expected, at the smallest scope you can, with a fallback you could defend out loud. XLOOKUP's fourth argument over IFNA, IFNA over IFERROR, and IFERROR only where every error the formula could throw genuinely has the same answer — which is much rarer than it looks. Everywhere else, let the cell go red, and put a diagnosis column beside it that says which of the nine things went wrong.

A payout run that refuses to total is an inconvenience for an afternoon. A payout run that totals to the wrong number is a partner querying their September statement in November, and by then nobody remembers that the column was ever red.

If you want hands-on practice with error handling and lookups, try the exercises in the app — each scenario runs on real business data, and a broken formula there costs nothing but a retry.