Skip to content
Klargrid

Excel errors

A cell shows #N/A, #REF! or #VALUE!: Excel does not say why. Each error has its usual causes, almost always the same ones, and its fix.

The nine error values

  • #N/A: the value looked for was not found.
  • #REF!: the formula points to a cell that no longer exists.
  • #DIV/0!: a division by zero, or by an empty cell.
  • #VALUE!: text where a number or a date was needed.
  • #NAME?: Excel does not recognise a name in the formula.
  • #NUM!: an impossible calculation, or a number out of range.
  • #NULL!: two ranges that do not intersect.
  • #SPILL!: a result that cannot spill into the neighbouring cells.
  • #CALC!: an array calculation that returns nothing.

Finding them all at once

In an inherited workbook, errors hide in sheets nobody opens. The formula audit lists every one, sheet and cell, with the formula that produces it; the health check counts them among the file's other defects.

For a formula you do not understand, Explain a formula spells it out step by step and flags the pitfalls that lead to these errors.