Excel IFERROR lets you replace an error result with a value you choose, making formulas easier to read and reports easier to use.
These examples show how to apply it to common tasks, while preserving errors when they point to a problem that needs fixing.
How the IFERROR function works
The syntax is IFERROR(value, value_if_error).
Excel evaluates value; if it returns an error, Excel returns value_if_error instead.
Otherwise, it returns the original result.
=IFERROR(value, value_if_error)For example, =IFERROR(A2/B2,"Check denominator") returns the division result when possible.
If the division produces an error, it displays Check denominator.
The fallback can be text, a number, a blank string (""), or another formula.
IFERROR catches errors such as #DIV/0!, #N/A, #VALUE!, #REF!, #NAME?, #NUM!, and #NULL!.
Because it catches many types, a broad fallback can hide errors unrelated to the case you intended to handle.
Practical Excel IFERROR formula examples
1. Prevent a division-by-zero error
Suppose A2 contains revenue and B2 contains units sold.
To calculate revenue per unit and show a message when the unit count is zero or invalid, use:
=IFERROR(A2/B2,"No units to calculate")If A2 is 1200 and B2 is 30, the result is 40.
If B2 is 0, the result is No units to calculate.
If a zero denominator is a normal condition in your report, a blank may be more suitable: =IFERROR(A2/B2,"").
2. Replace a missing lookup result
In Microsoft 365 or Excel 2021 and later, XLOOKUP has its own optional not-found argument.
IFERROR is useful when wrapping a lookup that returns an error, including VLOOKUP:
=IFERROR(VLOOKUP(E2,$A$2:$C$100,3,FALSE),"Not found")This searches for the value in E2 in the first column of A2:C100 and returns the value from the third column when an exact match exists.
If the lookup fails or another error occurs, the formula displays Not found.
The same wrapper can be used with INDEX and MATCH:
=IFERROR(INDEX($C$2:$C$100,MATCH(E2,$A$2:$A$100,0)),"Not found")For a formula that should handle only a missing match, consider using XLOOKUP's not-found argument or checking for #N/A with IFNA.
These approaches avoid masking other lookup errors, such as an invalid range reference.
3. Show a blank when a formula returns an error
For a cleaner display in a calculated column, return an empty string when the calculation fails:
=IFERROR(C2/D2,"")The cell looks blank if the division produces an error.
It still contains a formula, however, and the empty string is not the same as an actually empty cell.
Functions that count or test cells may treat the result differently from a cell with no content.
4. Calculate an average without displaying an error
AVERAGE returns #DIV/0! when the range contains no numeric values.
To show a message in that situation, use:
=IFERROR(AVERAGE(B2:B20),"No numeric entries")If B2:B20 contains 10, 20, and 30, the result is 20.
If there are no numbers to average, the fallback text appears.
This wrapper also hides other errors within the range, so inspect the source cells if the message appears unexpectedly.
5. Avoid an error in a percentage calculation
To calculate the percentage of a total in C2 relative to a value in B2, use:
=IFERROR(C2/B2,0)Format the result cell as a percentage.
If C2 is 25 and B2 is 100, the result is 25%.
If B2 is zero or the calculation otherwise fails, the result is 0%.
Use zero only when it accurately represents your reporting rule; a blank or explanatory message may be less misleading.
6. Handle errors across a spilled array
In Microsoft 365 and Excel 2021 or later, a dynamic array formula can return multiple results.
IFERROR can wrap the entire array calculation:
=IFERROR(A2:A10/B2:B10,"Check row")Each corresponding pair is divided, and the formula spills results into neighboring cells.
An error in an array calculation may cause the fallback to apply to the returned result as a whole rather than provide a customized message for each row.
If you need row-by-row handling, put an IFERROR formula in each row or use a suitable array-aware formula and verify its behavior in your Excel version.
When to use IFERROR, IFNA, or an IF check
Choose the narrowest error-handling method that matches the expected condition:
- Use IFERROR when several possible errors should receive the same fallback.
- Use IFNA when only
#N/A, often a missing lookup match, should be handled.
IFNA is available in Excel 2013 and later. - Use an IF check when you can test the condition directly, such as checking whether a denominator is zero before dividing.
For example, this formula distinguishes a zero denominator from other errors:
=IF(B2=0,"No units to calculate",A2/B2)An IF check can make the intended business rule clearer, though it does not automatically handle other errors in A2 or B2.
You can combine checks and error handling when both conditions matter.
Common mistakes and edge cases
- Hiding a broken formula:
=IFERROR(formula,"")can make a workbook look clean while concealing a misspelled function, deleted reference, or invalid value.
Use a descriptive fallback while building and checking the workbook. - Using the wrong argument order: the formula is
=IFERROR(value, value_if_error).
The first argument is the calculation; the second is what to return if it errors. - Confusing errors with ordinary values: IFERROR does not replace a legitimate result such as 0, an empty cell, or the text
Not applicable.
Those are not error values. - Returning text in a numeric column: a fallback such as
"Check value"makes the formula result text when an error occurs.
That can affect later arithmetic, charts, and summaries. - Ignoring compatibility: IFERROR is supported in Excel 2007 and later, including Microsoft 365, and in Google Sheets.
Older Excel versions may not recognize it.
How to troubleshoot an IFERROR formula
If the fallback appears when you expected a normal result, temporarily remove IFERROR to reveal the underlying error.
Check the referenced cells, confirm that ranges have the expected dimensions, verify lookup arguments, and look for numbers stored as text.
Once the cause is understood, choose a fallback that communicates the remaining condition accurately.