Spreadsheet error values, explained

Every hash-error is the engine telling you something specific. Here’s what each one means, the usual causes ranked by likelihood, and the fastest fix — for Excel, Google Sheets, and LibreOffice Calc.

#NAME? — unrecognized name

The engine doesn’t know a function or name in the formula. In order of likelihood:

• The function doesn’t exist in this app or version. XLOOKUP in Excel 2019, QUERY anywhere outside Sheets, MAP in LibreOffice. Paste the formula into the compatibility checker to see exactly which function fails where, and check the LibreOffice version page — upgrading often IS the fix (XLOOKUP needs LO 24.8+).
• A typo — =SUMIFF(...), =VLOOKUPP(...).
• Text without quotes — =IF(A2=yes,...) reads yes as a name; it needs "yes".
• Generated files missing the storage prefix. If a Python/library-generated .xlsx shows #NAME? on modern functions in every app, it’s the OOXML _xlfn. prefix issue — explained in our methodology.

#REF! — broken reference

The formula points at cells that no longer exist: rows/columns deleted, a sheet removed, or a copied formula whose relative references walked off the edge of the grid. VLOOKUP with a column index bigger than its table is the classic. Undo is your friend; longer-term, INDEX/MATCH and whole-range references survive edits that hard-coded positions don’t.

#VALUE! — wrong type of input

A function got text where it needed a number or date: arithmetic on cells containing text (often invisible — see the TRIM vs CLEAN guide), dates stored as text (convert them), or FIND/SEARCH not finding its target. In legacy Excel it’s also un-entered array formulas that needed Ctrl+Shift+Enter.

#DIV/0! — division by zero

The denominator is zero or blank. Averages of empty ranges throw it too (AVERAGEIF with no matches). Guard the specific case — =IF(B2=0,"",A2/B2) — rather than blanket-wrapping in IFERROR, which also hides real bugs (why that matters).

#N/A — not found (usually not an error)

Lookup functions return it when the value isn’t there — it’s a legitimate answer, not breakage. Handle the expected miss with XLOOKUP’s 4th argument or IFNA; investigate only when everything comes back #N/A (usually a type mismatch: numbers vs text-numbers, or stray spaces — the two-kinds-of-empty and TRIM issues).

#SPILL! / blocked arrays

A dynamic-array formula (FILTER, SORT, UNIQUE, SEQUENCE…) needs room to spill and something occupies the target cells. Clear the blocking cells — the error message highlights them in Excel. Google Sheets says #REF! with a “result was not expanded” note instead; LibreOffice reports its own error code. Legacy note: in pre-dynamic-array versions these functions don’t exist at all (#NAME? instead) — see which versions have them.

#NUM! — impossible number

Math that can’t produce a representable result: SQRT of a negative, IRR that doesn’t converge (add a guess argument), dates before the epoch, or numbers beyond ~1E308. Usually the inputs are wrong, not the formula.

Tools for diagnosing

• =ERROR.TYPE(A2) returns a code per error kind (7 = #N/A) — see the IS-function guide for detector strategies.
• The formula checker answers “is this #NAME? a version problem?” instantly.
• Our quirks catalog lists cases where an app returns a DIFFERENT error than Excel for the same formula — found by executing them.