What are Excel formula errors? Definition and every error type

The set is small and finite. Excel's own ERROR.TYPE function is the closest thing to an official register: it maps each error value to a number, and the list stops at eight. Learning which symbol means what turns debugging into a lookup.

The error values and what each one means

Error	ERROR.TYPE	What it means
#NULL!	1	A space used instead of a comma or colon between two references — "the result of a typo"
#DIV/0!	2	The formula "tries to divide by zero, or by a value equivalent to zero"
#VALUE!	3	"A value is not an expected or valid type" — usually text where a number was needed
#REF!	4	"A reference becomes invalid", usually because sheets, rows or columns were deleted
#NAME?	5	"Excel does not recognize something" — a misspelled function, or a named range that does not exist
#NUM!	6	"A number is too large or small, or … a calculation is impossible", such as the square root of a negative
#N/A	7	"Something can't be found" — the standard result of a lookup that missed
#GETTING_DATA	8	Named in Microsoft's ERROR.TYPE list; not a formula fault

Two newer values sit outside that list because they came with dynamic arrays. #SPILL! "occurs when a formula outputs a spill range that runs into a cell that already contains data", and #CALC! "occurs when a formula runs into a calculation error with an array". A column of hash marks, ######, is not an error at all: Exceljet is explicit that it is "not technically an error", just a value too wide for the column, which is why it turns up most often on long date formats.

The distinction that matters in practice is between errors that mean the formula is wrong and errors that mean the data is missing. #NAME?, #NULL! and #REF! are almost always the formula's fault. #N/A usually is not — it is a lookup honestly reporting that the value it was sent to find is absent, which is information, not a bug.

What Google Sheets shows that Excel does not

Sheets carries the same error values, plus one with no Excel equivalent: #ERROR!, shown alongside the message "Formula parse error". Ben Collins' guide defines it as the case where "Google Sheets can't understand the formula you've entered, because it can't parse the formula to execute it" — a mismatched bracket, a missing & between concatenated pieces, a stray $ typed into a currency amount. Collins notes it is unique to Sheets, and the reason is that Excel refuses the entry instead: type a formula Excel cannot parse and it raises a dialog and keeps you in the cell.

That matters when a workbook travels between the two: a malformed formula Excel would never have let you commit can sit in a Sheets file as a cell reading #ERROR!, and the fix is a syntax fix, not a data one. Sheets also folds circular references into #REF! rather than reporting them separately, and #NULL!, in Collins' words, "hasn't been recreated in the wild" there.

Finding the cause

Excel marks suspicious cells as you work. Microsoft: "Any error that is found is marked with a triangle in the top-left corner of the cell", driven by background error checking that can be switched off in the options. The green triangle is a heuristic, not a verdict — it flags numbers stored as text and formulas inconsistent with their neighbours, several of which are deliberate on any real model.

For a systematic pass, three tools sit together under Formulas → Formula Auditing:

• Error Checking walks the workbook cell by cell, naming the rule that was broken.
• Evaluate Formula lets you "see the different parts of a nested formula evaluated in the order the formula is calculated" — the fastest way to find which argument of a nested IF returns #VALUE!.
• Watch Window keeps a live list of chosen cells with their sheet, address, value and formula, so you can watch a cell on another tab while editing the one that feeds it.

Trace Precedents and Trace Dependents belong in the same pass, because an error value propagates: a #REF! in one cell turns every formula downstream into #REF! too, so the cell you are looking at is often not the cell that broke. Trace precedents until the arrows reach a formula whose own inputs are clean — that is the one to fix.

Trapping an error is not fixing it

IFERROR and IFNA catch an error and substitute something else — Exceljet's framing is that they "catch" errors by "providing an alternative value when an error is detected." They are the right tool for the case you expect: a lookup against a list that legitimately will not contain every key, a division whose denominator is blank until someone fills it in.

They are the wrong tool everywhere else. Wrapping every formula in IFERROR(…, "") converts #REF!, #NAME? and #VALUE! — all of which mean the formula is broken — into blanks that look like data. Prefer IFNA when the only case you want to absorb is a missed lookup, so genuine breakage stays visible.

How Formula Foundry handles this

Formula Foundry catches the class of problem that produces #NAME?, #NULL! and Sheets' #ERROR! — the ones living in the text of the formula — and it catches them before the formula reaches the cell. The editor parses what you type as you type it: matching parentheses highlight, and the parser reports specific problems rather than a generic complaint, naming a missing operator or a LET name that is missing its colon. Errors are collected in one pass with best-effort recovery, so you get the whole list instead of fixing one and discovering the next.

It is also locale-aware. A formula written with ; as the argument separator in a European locale is understood rather than rejected, and when the separator genuinely is the wrong one for the sheet, the parser says so instead of leaving you to guess. For unknown names there is a "did you mean" suggestion against the names it can see — the LET and LAMBDA names in scope and the add-on's own variables. It stays silent about workbook named ranges, because those are not visible to the parser and a confident wrong guess would be worse than nothing.

Be clear about what it cannot do. Formula Foundry has no calculation engine for your workbook: it parses, formats, explains and translates, and never evaluates the sheet. It cannot tell you that a lookup will return #N/A, that a denominator will be zero, or that a deleted column will produce #REF! — those are facts about your data, and only Excel and Sheets know them. What it adds once an error has appeared is the AI assistant, which explains an existing formula step by step in plain English and offers debug hints for the usual suspects: a missing range, a misused function, a range off by one row. You can try the parser and its messages on your own formulas in the free playground, in the browser, with nothing to install.

FAQ