Contact
12 programs
Explore the site

Excel #DIV/0!: Repair the Denominator, Not Just the Display

#DIV/0! means a division cannot produce a valid number because its denominator is zero or an empty referenced cell. Find the denominator before deciding whether the workbook needs corrected data or an explicit “not ready” state. Microsoft’s division-error reference.

Follow the denominator

Work on a copy. If the formula is =B2/C2, inspect C2 and the formula that supplies it. If the denominator is an expression, put that expression alone in a spare cell. An error in a final percentage may originate several calculations earlier; changing the final display does not repair its source.

Ask what the denominator represents. A quantity of zero can be valid input while the requested price per unit is undefined. A missing quantity is a different business state. Do not insert 1 merely to make the division work.

A small control with three outcomes

In a spare sheet, enter 84 in B2 and 0 in C2. Put =B2/C2 in D2. The documented expected result is #DIV/0!.

  1. Change C2 to 7. D2 should become 12: 84 divided by 7.
  2. Change B2 to 0, leaving C2 at 7. D2 should become 0. This is a valid zero result, not missing data.
  3. Restore B2 to 84 and C2 to 0. If zero quantity genuinely means “not ready” in this model, test =IF(C2=0,"Check quantity",B2/C2). It should display Check quantity, not a manufactured numeric zero.

The guard is deliberately narrow. It is not a general validator for text, negative quantities or errors already present in C2. Establish those rules separately, using the meaning of the data rather than a universal cleanup formula.

Why not wrap everything in IFERROR?

Microsoft warns that IFERROR hides other error classes too. During repair, keep the original calculation visible alongside any presentation formula. A report containing zero sales per unit implies a measurement; a report saying “Check quantity” signals unfinished work. Choose deliberately before downstream totals or charts consume the output.

Scope and next step

Checked October 21, 2026. Microsoft’s reference includes Excel Web App and desktop editions. This cell-only exercise does not require desktop auditing commands or claim web/desktop UI parity. Arithmetic was independently calculated, not executed in Excel; no application screenshot was captured. Examples use English function names and comma argument separators; adapt separators to your locale.

See IF, IFERROR and the Excel diagnostic starting point.