The IFERROR function in Excel lets you replace formula errors with a custom result, such as a blank cell, a zero, or a helpful message. It is one of the most useful functions for cleaning up spreadsheets, especially when working with formulas that may occasionally return errors like #DIV/0!, #N/A, or #VALUE!.
Instead of showing confusing error messages, IFERROR allows you to display something more useful and user-friendly.
What is the IFERROR Function?
The IFERROR function checks whether a formula returns an error. If the formula works normally, Excel returns the result. If the formula creates an error, Excel returns a custom value that you choose.
This makes IFERROR especially useful in reports, dashboards, lookup formulas, and spreadsheets shared with others.
IFERROR Function Syntax
=IFERROR(value,value_if_error)Argument Definitions
- value— The formula, calculation, or expression you want Excel to evaluate.
- value_if_error —The value Excel should return if the formula results in an error.
Example Dataset Used in This Tutorial
You can download the sample data we’ll be using in this tutorial by clicking on this link—> IFERROR Function Data
The IFERROR function handles several Excel errors, including:
#DIV/0!#N/A#VALUE!#REF!#NAME?#NUM!#NULL!
Replace a Division Error with Zero
One of the most common uses of IFERROR is preventing division errors. In this example, the formula divides revenue by units sold. If units sold equals zero, Excel would normally return #DIV/0!. IFERROR replaces that error with 0.
The formula used is:
=IFERROR(B2/C2,0)
If the formula works normally, Excel returns the result of the division. If an error occurs, Excel returns 0.
Replace an Error with a Custom Message
Sometimes a message is more helpful than a number. Instead of showing an error, you can display text explaining the problem.
The formula used is:
=IFERROR(B2/C2,"Check units")
When Excel encounters an error, it returns the message Check units instead.
Return a Blank Cell Instead of an Error
In reports or dashboards, you may prefer blank cells instead of messages or numbers.
The formula used is:
=IFERROR(B2/C2,"")
The empty quotation marks tell Excel to display nothing if an error occurs.
Use IFERROR with VLOOKUP
IFERROR is commonly paired with VLOOKUP to replace #N/A errors when a lookup value cannot be found.
The formula used is:
=IFERROR(VLOOKUP(D2,A:C,2,FALSE),"Not found")
If Excel finds the product ID, it returns the product name. If not, it displays Not found.
Use IFERROR with XLOOKUP
IFERROR also works well with XLOOKUP.
The formula used is:
=IFERROR(XLOOKUP(D2,A:A,C:C),"Employee not found")
If the employee ID does not exist, Excel displays a helpful message instead of an error.
Use IFERROR in Reports
IFERROR is useful for dashboards and business reports where visible errors can make spreadsheets look unprofessional.
The formula used is:
=IFERROR(B2/C2,"No sales")
Instead of displaying #DIV/0!, Excel returns the message No sales.
Common IFERROR Mistakes
One common mistake is using IFERROR to hide formula problems. IFERROR should only handle expected errors, not cover up broken formulas.
Another mistake is using IFERROR everywhere unnecessarily. Some formulas may be better left showing errors so issues can be identified and corrected.
Finally, many users forget that IFERROR catches all errors. If you only want to handle #N/A errors, consider using the IFNA function instead.
Example Dataset Used in This Tutorial
You can download the sample data we’ll be using in this tutorial by clicking on this link—> IFERROR Function Data
