15 errors · The hash family

Formula Errors

The classic #ERROR family — hash-prefixed error codes that appear in cells when a formula can't compute. Cryptic on purpose, but each has a specific cause and specific fix.

Every Excel user meets these errors. #REF! when you delete a column that a formula depended on. #NAME? when you misspell a function. #VALUE! when you try to multiply text by a number. #DIV/0! when a divisor turns out to be zero. Below is every hash error with the top cause and the copy-paste fix.

The seven core hash errors

#REF!

Broken reference

Cause: A cell, row, or column your formula referenced has been deleted. Or an INDIRECT/OFFSET reference points outside the valid range.

Fix: rebuild the formula pointing to the correct location, OR press Ctrl+Z if you just deleted something.
Full #REF! diagnostic guide →
#NAME?

Unknown name

Cause: Excel doesn't recognize a name in your formula. Usually a misspelled function (VLOKUP), a text value without quotes (=IF(A1=paid...)), or a named range that doesn't exist.

Fix: check spelling, add quotes around text values, verify named ranges exist in Name Manager.
Full #NAME? diagnostic guide →
#VALUE!

Wrong data type

Cause: A formula received the wrong type of input. Math on text (="abc"*2), date operation on non-date, or a text argument where a number was expected.

Fix: check that inputs match expected types. Convert with VALUE(), N(), or DATEVALUE() if needed.
Full #VALUE! diagnostic guide →
#DIV/0!

Divide by zero

Cause: A formula tried to divide by zero or by an empty cell (which Excel treats as zero). Also appears in AVERAGE of empty range or MOD by zero.

Fix: wrap in IFERROR — =IFERROR(A1/B1, 0) — or add explicit check for zero.
Full #DIV/0! diagnostic guide →
#N/A

Value not available

Cause: A lookup function couldn't find the requested value. VLOOKUP, XLOOKUP, MATCH failing to match. Or a formula explicitly returned NA().

Fix: wrap in IFNA — =IFNA(VLOOKUP(...), "Not found"). Or check for exact-match issues.
Full #N/A diagnostic guide →
#NUM!

Invalid number

Cause: A number is out of range for the calculation. Common with SQRT of negative, LOG of zero, IRR that can't converge, dates before 1900, or numbers too big/small.

Fix: check for negative arguments where positive is required. For IRR, provide a guess value.
Full #NUM! diagnostic guide →
#NULL!

No intersection

Cause: Two ranges specified with a space between them (the intersection operator) don't intersect. Usually a typo — a space where you meant a comma or colon.

Fix: replace the space with the correct operator. =SUM(A1:A10 B1:B10) → =SUM(A1:A10, B1:B10).
Full #NULL! diagnostic guide →
CIRCULAR

Circular reference

Cause: A formula refers back to itself, directly or through a chain of other formulas. Excel can't compute because the answer depends on itself.

Fix: click Formulas → Error Checking → Circular References to find the loop. Then break it by restructuring.
Full circular reference guide →

Diagnostic flow — which error is it?

  1. Does the error contain "#REF!"? Something the formula depended on was deleted. Look at what changed most recently in the workbook. Undo or rebuild the reference.
  2. Does the error contain "#NAME?"? Excel doesn't recognize something. Check function spelling, quotes around text, and Name Manager for named ranges. Most common cause: typo in a function name.
  3. Does the error contain "#VALUE!"? Wrong type of input. Trace which cell has the bad value using Formulas → Evaluate Formula. Usually a text-where-number issue.
  4. Does the error contain "#DIV/0!"? Division by zero or empty cell. Wrap the formula in IFERROR with a safe fallback value.
  5. Does the error contain "#N/A"? A lookup didn't find its target. Check for spaces/casing in lookup value, exact match flag, and column alignment.
  6. Does the error contain "#NUM!"? Number out of valid range. Check for negative inputs where positive is required, or provide an initial guess for iterative functions like IRR.
  7. Does the error contain "#NULL!"? Two ranges don't intersect. Almost always a typo — check for accidental spaces between range references.
  8. Does Excel show "Circular reference" warning? A formula refers back to itself. Formulas → Error Checking → Circular References shows the offending cells.

All 15 formula errors

Prevention patterns

  • Wrap risky formulas in IFERROR: =IFERROR(A1/B1, 0) catches all errors with one fallback
  • Prefer XLOOKUP over VLOOKUP: XLOOKUP has a built-in "if not found" argument, no wrapping needed
  • Use tables (Ctrl+T) instead of raw ranges: tables auto-expand and prevent many #REF! errors
  • Test with sample data before production: catch #VALUE! and edge cases early
  • Turn on Show Formulas (Ctrl+`): lets you eyeball a whole sheet for errors visually
  • Use Formulas → Evaluate Formula: step through a formula piece by piece to find where the error appears

Frequently asked questions

Why does Excel show cryptic errors instead of plain-English messages?

Cell width. Excel uses short codes so the error fits inside the cell. Hovering shows a one-line tooltip, and clicking the yellow warning icon shows a slightly longer explanation. For the full diagnosis, you need dedicated pages like these.

What's the difference between #N/A and #REF!?

#N/A means "the value doesn't exist here — I looked but didn't find it". #REF! means "the location I was looking at doesn't exist anymore — it was deleted". Different problems, different fixes.

Why does my formula show ###### with hash marks all the way across?

Column too narrow to display a number or date. Widen the column, or right-click → Format Cells to change the number format. Not really an error — just a display issue.

Can I suppress errors globally?

Yes — Formulas → Error Checking → Error Checking Options lets you disable various green-triangle warnings. But wrapping errors with IFERROR at the formula level is much better practice than suppressing them globally.

Which error type is most common?

By search volume: #REF! and #N/A are tied for most-searched. In finance workbooks, #DIV/0! is the most common. In lookup-heavy workbooks, #N/A dominates. Every Excel user hits all seven eventually.

Fix any error in one click.

The Add-in's Error Explainer identifies which error you have, why it happened, and gives you the exact fix formula — right inside Excel.

Get the Add-in →