π¦ Handle Errors with IFERROR and ISERROR β Create Cleaner Reports
When errors such as #DIV/0!, #N/A, and #VALUE! appear in an Excel report, readability suffers. In these cases, you can use the IFERROR and ISERROR functions to display the message or value you want instead of an error.
β IFERROR Function β Return a Replacement Value When an Error Occurs
=IFERROR(formula, value to display if an error occurs)
Example: Handle an error in a division calculation
=IFERROR(A2/B2, "Unable to calculate")
β If B2 is 0, βUnable to calculateβ is displayed.
β ISERROR Function β Check Whether an Error Occurred
=ISERROR(formula)
Example: Return TRUE/FALSE depending on whether an error occurred
=ISERROR(VLOOKUP("ABC", A2:B10, 2, FALSE))
β Practical Example 1: Handle VLOOKUP Errors
=IFERROR(VLOOKUP("ABC", A2:C10, 2, FALSE), "Value not found")
β Practical Example 2: Prevent Divide-by-Zero Errors
=IFERROR(A2/B2,0)
β Replaces the error with 0 if one occurs.
β Practical Example 3: Calculate an Average Excluding Error Data
Combine ISERROR and IF:
=IF(ISERROR(A2), "", A2)
β Practical Example 4: Display a Clean Message in a Report
=IFERROR(HLOOKUP("Sales", A1:D5, 2, FALSE), "No data")
π Summary
- π IFERROR: Display a replacement value when an error occurs
- π ISERROR: Check whether an error occurred
- π Essential for handling #DIV/0! and #N/A errors
- π Improves the readability of reports and dashboards
π Frequently Asked Questions (FAQ)
Q1. What is the difference between IFERROR and IFNA?
A. IFNA handles only #N/A errors, while IFERROR handles all errors.
Q2. Can ISERROR catch only a specific error?
A. No. It returns TRUE for all errors. To handle a specific error, use ISNA, ISERR, and similar functions.
Q3. Can I nest other functions inside IFERROR?
A. Yes. It is commonly used with VLOOKUP, HLOOKUP, MATCH, and other functions.
Q4. Can I display a blank cell instead of an error?
A. Yes. Set it as IFERROR(formula, ββ).
Once you master the IFERROR and ISERROR functions, the quality and reliability of your reports will improve significantly π