Handle Excel Errors with IFERROR and IFNA Without Hiding Problems
On this page
An error can reveal the input problem a report needs to expose. IFERROR and IFNA help present expected failures clearly, but wrapping every formula immediately can hide a broken reference or an incomplete record.
Practice setup: Start in a new workbook with fictional data. Formulas use English function names and commas; your regional settings may require semicolons. Interface labels vary between Excel versions. Keep the source data and check your results before reusing the method.
Create three test cases
Use Sales, Orders and Average in A1:C4. Enter 600 and 3 in row 2, 400 and 0 in row 3, and 0 and 2 in row 4. In C2 use =A2/B2 and fill down.
The results are 200, #DIV/0! and 0. Row 3 has no valid ordinary average because its denominator is zero. Row 4 has a genuine average of zero. Those meanings should remain distinct.
Diagnose before replacing the error
Ask whether zero orders is expected or a data-entry problem. A sales amount of 400 with no orders may require investigation. Excel cannot decide which input should be corrected.
=IFERROR(A3/B3,"Check inputs") replaces the error with a message. IFERROR catches many error types, including broken references, not only division by zero. A message naming one cause can mislead if a different error occurred.
Express a known rule directly
=IF(B3=0,"No average",A3/B3) handles the known zero-denominator case. Missing inputs may need a separate check. A blank is not automatically a confirmed zero, so test both cases if your sheet permits them.
Text in a numeric result column can affect later summaries. Consider a separate status column when the report needs a clean numeric field. Also avoid averaging row-level averages without accounting for different order counts; an overall ratio often needs total sales divided by total orders instead.
Use IFNA for a missing lookup
For an exact-match lookup, =IFNA(VLOOKUP(E2,$H$2:$J$5,3,FALSE),"Code not found") handles #N/A while leaving other errors visible. Use this only with an actual reference list in H2:J5 and a key in E2.
IFERROR would catch more types of failure. Neither function validates the reference list, removes duplicate codes or corrects a misspelling. Test the lookup without a wrapper first so you understand what is failing.
Retest a valid input
Change row 3's order count to 2. Its average should become 200. If the message remains, check for a hard-coded result or wrong reference. A friendly display should still respond correctly to corrected data.
Keep unresolved records visible in a review list or count. A tidy dashboard with hidden failures can be less useful than one with a clear warning. Handling an error is not the same as resolving its cause.
Try it yourself
Why would replacing every error with zero make rows 3 and 4 misleadingly similar? What should a reviewer see instead?
Check your answer
Row 3 has an undefined or invalid average under the formula; row 4 has a valid numeric zero. Replacing both with 0 conceals that difference.
Show an explanatory status for row 3, investigate its inputs and preserve row 4's genuine zero. Error handling should communicate what is known rather than merely remove visible error symbols.
Keep exploring: Excel learning path.