Skip to content
Tutorials Center
My learning

Topics

Lesson 13 of 93 ยท Formulas Fundamentals

Formula Errors and How to Fix Them

Learn what every Excel error means (#DIV/0!, #N/A, #VALUE!, #REF!, #NAME?, #NUM!, #NULL!, #SPILL!, ####), why it appears and how to fix or handle it.

Sooner or later a cell in your workbook will show something like #DIV/0! or #N/A. These are error values, and they are not a sign that Excel is broken. They are Excel telling you precisely what kind of problem it ran into. Each error has a specific meaning, so once you can read them, most fixes take seconds.

In this lesson you will learn the nine errors you will meet most, the usual cause of each, how errors spread from cell to cell, and how IFERROR and IFNA can handle them. You will also meet the tools Excel gives you for hunting an error. The error names and formulas are real Excel. The live editors use a stand-in engine that raises the same errors for the same reasons, so you can trigger them safely.

Read the error, find the cause

#DIV/0! Divided by zero or by nothing.
#N/A A lookup found no match.
#VALUE! Wrong type of value.
#REF! A referenced cell is gone.

The error name already points at the problem.

1 / 2

The errors and what they mean

Error Meaning Typical cause Typical fix
#DIV/0! Division by zero =A1/B1 where B1 is 0 or empty; AVERAGE of a range with no numbers Guard with IF(B1=0, ..., A1/B1), or fix the missing input
#N/A A value is not available A lookup finds no match; NA() was used on purpose Check the lookup key (spaces, text versus number); wrap with IFNA
#VALUE! A value is of the wrong type ="abc"+1; a cell holding a space is treated as text; wrong argument type Clean the input (TRIM, VALUE) or fix the argument
#REF! A reference is invalid You deleted a row, column or sheet that a formula used; a lookup column number is larger than the table Undo, or rewrite the reference
#NAME? Excel does not recognize a word Misspelled function or name; text without quotes; function not available in your version Fix the spelling, add quotes, check the version
#NUM! Invalid number =SQRT(-1); a result too large or too small; a function that cannot converge Check the input values and limits
#NULL! Ranges do not intersect A space typed where a comma or colon belongs: =SUM(A1:A3 C1:C3) Use , to combine or : to span
#SPILL! A dynamic array has no room Cells in the spill area are not empty; a merged cell is in the way Clear the blocking cells
#### Display problem, not an error Column too narrow for the number; negative date or time Widen the column; check the date

Two more appear in newer Excel: #CALC! (a dynamic array calculation produced an empty array, for example FILTER with no matches) and #GETTING_DATA (a value is still loading).

#### is not an error value. The formula is fine; Excel just cannot fit the result into the column. Double-click the column border to widen it.

Errors spread

An error in one cell travels to every formula that depends on it. If B1 shows #DIV/0!, then =B1+1, =SUM(B1:B5) and anything referring to those cells show #DIV/0! as well. A single bad input can turn a whole report red.

That is useful: you always know that something upstream is wrong. The skill is to find the first error in the chain, not to fix the last one. Look for the cell that has an error but whose own inputs are fine.

Trying every error

The engine below contains inputs and one formula for each error. Read the formula and the result next to each other, and notice how SUM(B1:B2) inherits an error, while IFERROR, IFNA and a guard handle them in different ways.

JS Triggering and handling errors

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Look at B8: IFERROR replaced the error with 0, and at B10: IFNA did not catch #DIV/0!, because it only handles #N/A. Choosing the narrowest tool is a good habit, as the next section explains.

Handling errors with IFERROR and IFNA

IFERROR(value, value_if_error) returns the first argument, unless it is any error, in which case it returns the second. IFNA does the same but only for #N/A:

TEXT
=IFERROR(A1/B1, 0)
=IFNA(VLOOKUP(E2, Prices, 2, FALSE), "not in list")

Use these carefully. IFERROR wraps a whole formula, so it also hides errors you did not expect, such as a broken reference or a typo in a function name. A report full of zeros can be worse than one full of errors, because zeros look like real data. Good practice:

  • Prefer preventing the error with an explicit check (IF(B1=0, "", A1/B1)).
  • Use IFNA for lookups, since "no match" is the only error you expect there.
  • Use IFERROR only when you can say what errors are possible and what to show for them.

Handling Errors and Blanks in Formulas returns to these patterns in depth.

Display problems and spills

Two of the entries in the table are about layout rather than values. The second stand-in shows how #### appears when a result does not fit, and how a spill range is blocked by a non-empty cell. It does not use the formula engine: it just models the rule.

JS ####, and a blocked spill

Press Run (or Ctrl + Enter) to see the console output here.

No output.

The first try fails because A3 already holds text; once you clear it, the same formula spills. Spill ranges are the topic of Dynamic Arrays and Spilling.

Finding the source of an error

Excel has built-in tools for tracking errors down:

  • Error checking indicator. A small green triangle in a cell's corner flags a suspicious formula. Click the warning icon beside it for the explanation and options such as "Help on this error" and "Show Calculation Steps".
  • Formulas โ†’ Error Checking โ†’ Trace Error draws arrows from the error cell back toward the cells that feed it.
  • Trace Precedents (Formulas tab) shows which cells a formula uses; Trace Dependents shows which cells use it. Remove the arrows with Remove Arrows.
  • Evaluate Formula (Formulas tab) steps through a formula one operation at a time and shows each intermediate result; the step that returns an error is the culprit.
  • Show Formulas (Ctrl + backtick) displays every formula instead of results, which is a quick way to spot an inconsistent one.

Auditing and Debugging Formulas walks through these tools in detail, including partial evaluation with F9.

A short checklist

  1. Read the error name; the table above narrows it down.
  2. Click the cell and read the formula in the formula bar.
  3. Find the first error in the chain, which is usually the cell furthest upstream.
  4. Check inputs: blanks, spaces, text that looks like a number, mismatched types.
  5. Fix the cause. Only then, if the situation is normal and expected (for example "no match found"), handle it with IFNA or a guard.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Which error does =10/0 return?

    Dividing by zero (or by an empty cell) gives #DIV/0!.
  2. 2. A VLOOKUP cannot find its key in the first column of the table. Which error appears?

    #N/A means a value is not available, which is what lookup functions return when there is no match.
  3. 3. You delete column C, and a formula that used it now shows #REF!. What happened?

    Deleting cells that a formula refers to invalidates the reference, and Excel marks it with #REF!. Undo (Ctrl+Z) is often the quickest fix.
  4. 4. Why can IFERROR be risky?

    IFERROR replaces any error with your value, so a typo, a broken reference or a real data problem can disappear behind a harmless-looking 0.
  5. 5. A cell shows ########. What is the first thing to try?

    means the result does not fit in the column. Widening the column normally shows the number.

Summary

  • Each error value has a specific meaning: #DIV/0! (divide by zero), #N/A (no match), #VALUE! (wrong type), #REF! (invalid reference), #NAME? (unknown word), #NUM! (impossible number), #NULL! (no intersection), #SPILL! (blocked spill).
  • #### just means the column is too narrow (or a date is negative).
  • Errors spread to every formula that depends on the faulty cell, so fix the first one in the chain.
  • IFNA handles only #N/A; IFERROR handles all errors and can hide real bugs. Prefer preventing errors with an explicit check.
  • Excel's Trace Error, Trace Precedents and Evaluate Formula tools help find the cause.

Next: Auditing and Debugging Formulas.