Handling Errors

#DIV/0!

A formula is dividing by a value that is zero or blank. Common when calculating averages or rates before all data has been entered — a unit cost formula dividing by a quantity column that still has empty rows.

=D9/E9 → E9 is blank

Fix: fill the missing value, or wrap with IFERROR to return 0 or "—" until data is entered.

#VALUE!

A formula expects a number but finds text, a date stored as text, or a cell with extra spaces or symbols. Manually typed "$" signs in amount cells, imported data with invisible characters, and text labels in numeric columns are the most common causes.

=D9+E9 → E9 contains "$45" as text

Fix: this is a data quality issue, not a formula issue. Clean the source data — remove non-numeric characters and reformat cells as numbers.

#REF!

A row or column used in a formula was deleted. Excel replaces the missing reference with #REF! throughout every formula that depended on it. This is a strong signal that the worksheet structure has changed in a way that broke existing formulas.

=E9 → column E was deleted

Fix: press Ctrl+Z immediately to undo the deletion and restore the reference. If that is not possible, find every #REF! and update the references manually.

#N/A

Most often appears in XLOOKUP, VLOOKUP, or MATCH when the lookup value does not exist in the lookup array. In a reporting context this usually means data is missing from the reference table, not that the formula is wrong. XLOOKUP's if_not_found argument is the cleanest way to handle this.

=XLOOKUP(B9, I12:I20, J12:J20) → "Gaming" not in table

Fix: add the missing category to the reference table, or use the if_not_found argument: =XLOOKUP(B9, I12:I20, J12:J20, "No budget defined").

#NAME?

Caused by misspelled function names, missing quotation marks around text values, or references to named ranges that don't exist. The most common version is a typo in a function name — Excel reads it as a named range reference and can't find it.

=IFF(D9>100,"High","Low") → "IFF" is not a function

Fix: check the function name spelling, ensure all text values are in double quotes, and verify any named ranges exist in the workbook.

#NUM!

A formula received a number outside its acceptable range or produced a result that has no mathematical solution. Taking the square root of a negative number, or passing inconsistent inputs to financial functions (like a negative number of payment periods), are typical causes. Unlike #VALUE!, the data types are correct — the numbers themselves are structurally invalid within the formula's logic.

=SQRT(-4) → square root of a negative number

Fix: check the input values feeding the formula. A negative number where a positive is required, or a zero where a non-zero is required, is usually the culprit.

IFERROR

IFERROR lets you replace any error with a clean, readable alternative — a zero, a dash, a message, or an empty string — without hiding the underlying formula logic.

The important thing to understand is that IFERROR wraps the formula — it doesn't change it. The original formula still runs exactly as written. IFERROR only intercepts the result if that result is an error. If the formula succeeds, IFERROR returns the real value unchanged. This means you are not hiding a problem; you are deciding how to display it.

=IFERROR(formula; value_if_error)