A report full of red error cells is unpleasant, so wrapping everything in IFERROR is tempting. The catch is that the same fallback can hide a missing customer, a deleted range and an incorrect formula. Those are not interchangeable problems.
Look at the raw error first. Once you know its cause, you can choose a sensible message.
Three different outcomes that can look like zero
| Expression | Raw result | After IFERROR(expression,0) |
|---|---|---|
| =0/5 | 0 | 0 |
| =100/0 | #DIV/0! | 0 |
| =”pending”/2 | #VALUE! | 0 |
The first zero is a valid result. The second has no valid denominator, and the third contains text where arithmetic expects a number. If all three appear as zero, an average or total may conceal incomplete data.
Handle expected conditions directly
If a zero quantity means a report row has no units, check that condition explicitly. With revenue in A2 and units in B2:
=IF(B2=0,"No units",A2/B2)A text revenue will still expose its error, which helps distinguish a data problem from the anticipated zero-unit case. This short formula assumes numeric, nonblank units; add input validation if an empty cell should mean missing rather than zero.
Use narrow handling for missing lookups
IFNA catches #N/A rather than every error. For a legacy exact lookup with IDs in D2:D5 and prices in E2:E5:
=IFNA(INDEX(E2:E5,MATCH(G2,D2:D5,0)),"Missing ID")A broken reference or other structural error can still remain visible. In XLOOKUP, its if_not_found argument also expresses a missing-match fallback. Neither approach validates duplicate keys, incorrect return-column alignment or text/number differences.
Keep an exception count beside a clean report
If a result range H2:H100 contains the explicit label Missing ID, =COUNTIF(H2:H100,”Missing ID”) counts those exceptions. Review them before distributing the report. A count of zero does not prove numerical correctness, but a nonzero count should not disappear into blank dashboard cells.
A practical repair sequence
First remove the wrapper in a working copy. Identify the exact raw error, inspect the referenced inputs, and correct the underlying formula or data. Then reintroduce only the fallback required by the reader of the result. Test a genuine zero and a deliberately broken input to confirm that they remain distinguishable.
When IFERROR is appropriate
A presentation formula can use IFERROR after its calculation has been checked and exceptions are reported elsewhere. Choose a message that explains the action rather than silently returning zero by habit. The IFERROR examples show how a valid zero differs from a failed calculation.
What to check before you rely on this
A visible exception often saves more time than a tidy-looking blank. Decide which errors are expected before you choose how to display them. Microsoft Excel documentation is a useful product reference. The example data is fictional; test the formula in your spreadsheet version and locale before using it for actual work.