fx LookupLab
Reference · Print-friendly

Excel Error Codes:
What Every #Error Means and How to Fix It

Excel returns a small, fixed set of error values. Each one tells you exactly what kind of thing went wrong — a missing lookup, a wrong data type, a broken reference. This reference covers all of them, including the newer dynamic-array errors, with the cause and the fix. Bookmark it or print it.

#N/A

“Not available” — a lookup function could not find the value you asked for. It is the most common Excel error and usually a data problem, not a formula bug.

Common causes

  • The lookup value genuinely is not in the source table.
  • A trailing space or different data type makes two “equal” values not match ("101" text vs 101 number).
  • VLOOKUP/HLOOKUP omitted the exact-match argument, or the table is unsorted for an approximate match.
Fix: confirm the value exists; wrap the lookup key in TRIM() and match types; use VLOOKUP(...,FALSE) for exact match. To show a friendly default instead of the error, use XLOOKUP(...,"not found") or IFNA(VLOOKUP(...),"—"). Use IFNA rather than IFERROR so you only hide missing lookups, not real bugs.
→ IFNA lab · VLOOKUP · XLOOKUP

#VALUE!

Wrong type of argument — Excel expected a number (or a specific type) and got something it cannot coerce, like text where a number belongs.

Common causes

  • Math on a cell that holds text, e.g. =A1+10 where A1 is "abc".
  • A date stored as text that looks like a date.
  • Array-formula arguments of mismatched dimensions in older Excel.
Fix: convert text to numbers with VALUE() or --; clean imported data with TRIM()/CLEAN(); check that ranges fed to array operations are the same size.
→ VALUE lab · TRIM

#REF!

Invalid cell reference — a formula points to a cell that no longer exists, almost always because rows, columns or sheets it referenced were deleted.

Common causes

  • Deleting a row/column/sheet that a formula referenced.
  • Cutting and pasting cells over a reference.
  • VLOOKUP column index larger than the table width.
Fix: undo the deletion if possible; rebuild the reference. Unlike other errors, #REF! cannot be recovered by wrapping — the reference is gone. Prefer structured table references and named ranges, which survive row inserts better than raw A1 addresses.
→ INDEX-MATCH lab

#DIV/0!

Division by zero (or by an empty cell, which Excel treats as zero).

Common causes

  • A denominator cell is 0 or blank.
  • A percentage-of-total formula where the total is zero.
Fix: guard the denominator: =IF(B1=0,0,A1/B1), or =IFERROR(A1/B1,0). For averages over a possibly-empty range, AVERAGEIF with a condition avoids counting blanks.
→ IFERROR lab · SUMIF

#NAME?

Excel does not recognize text in the formula — a misspelled function name, an undefined named range, or missing quotes around text.

Common causes

  • Typo: =VLOKUP(...) instead of VLOOKUP.
  • A named range that was never defined or was deleted.
  • Text not wrapped in quotes: =FIND(hello,A1).
  • A function from a newer Excel version opened in an older one (e.g. XLOOKUP in Excel 2016).
Fix: check spelling; define the name in Name Manager; quote literal text; confirm the function exists in your Excel version. Compatibility matters — see each lab’s 8-platform matrix.
→ XLOOKUP lab (compatibility)

#NUM!

Invalid numeric value — a calculation produced a number Excel cannot represent, or an iterative function failed to converge.

Common causes

  • =SQRT(-1) — square root of a negative.
  • A result too large or too small for Excel (~1E±308).
  • IRR/RATE cannot converge on a solution.
Fix: validate inputs (e.g. =SQRT(ABS(A1)) if negatives are expected); supply a better guess to iterative functions; check the magnitude of the result.
→ Statistical labs

#NULL!

An intersection of two ranges that do not actually intersect — usually a missing comma between arguments, which Excel reads as the intersection operator (a space).

Common causes

  • =SUM(A1:A5 B1:B5) — a space instead of a comma.
  • Intentional intersection syntax applied to non-overlapping ranges.
Fix: add the missing comma: =SUM(A1:A5,B1:B5). If you meant a union, use a comma; if you meant intersection, ensure the ranges overlap.
→ SUMIF lab

#SPILL!

A dynamic-array formula (Excel 365 / 2021+) cannot spill its results because the spill range is blocked. Newer Excel only.

Common causes

  • A cell in the intended spill range already holds data.
  • The spill range overlaps a merged cell or a table.
  • The formula would spill beyond the worksheet edge.
Fix: clear the cells in the spill range (Excel highlights them); unmerge cells; move the formula so it has room. #SPILL! is a layout problem, not a logic problem.
→ FILTER lab · UNIQUE

#CALC!

A calculation error in the dynamic-array engine — most often an array function asked to operate on an empty array. Excel 365 / 2021+ only.

Common causes

  • FILTER with no matching rows and no if_empty argument.
  • Nested array functions producing an empty intermediate result.
Fix: give FILTER a third argument for the empty case: =FILTER(A:C,B:B="x","none"). Guard upstream functions so they never return an empty array.
→ FILTER lab

#####

Not an error at all — the column is too narrow to display the number or date, or a date/time is negative.

Fix: widen the column (double-click the column boundary to auto-fit). If widening does not help, check for a negative date/time value.

#GETTING_DATA

A temporary placeholder shown while Excel fetches data from an external source (a data model, cube, or linked workbook). It resolves on its own when the data arrives.

Fix: wait for the refresh to finish; if it persists, check the external connection. It is a status, not a formula error.
Rule of thumb: reach for IFNA to hide only missing lookups, and IFERROR only when you genuinely want to suppress every error type. Blanket IFERROR around a whole formula can mask real bugs — fix the cause first, then decide what to hide. For hands-on practice with error handling, see the free practice workbook.