What is IFERROR and what is the IFERROR formula?
The IFERROR function returns a custom value when a formula results in an error. If the formula contains no error, it will return the result of the formula.
The function has 2 arguments, which are both required in Excel: value and value_if_error.The function has 2 arguments, which are both required in Excel: value and value_if_error.
Syntax of the function: =IFERROR(value, value_if_error)
• value – the argument checked for error.
• value_if_error – the custom value returned when there is an error. Errors evaluated when using the IFERROR function: NULL!, N/A, REF!, VALUE!, DIV/0!, NUM! or NAME?.
How to use IFERROR in Microsoft Excel?
If you want clean reports with no ugly error values, use the IFERROR formula for the errors mentioned above.
Using the IFERROR function is easy if you follow these 4 steps:
• type =IFERROR( in the selected cell
• in place of value, select the cell you want to check or enter a formula
=IFERROR(value,
• in place of value_if_error, type within ” ” the custom value you wish to appear when there is an error
=IFERROR(value, value_if_error)
• press Enter
Notes
– If you use “” as the value_if_error argument or if it’s empty, nothing will be displayed when an error is detected. You may see a warning message on the left corner of the cell.
– If value is empty, the system does not consider it as an error and 0 will be displayed.
– If value is a formula, the IFERROR function will return the formula’s results if any.
Examples
Return your custom value instead of an Error message
In this example, value_if_error is “Invalid name”.
Return a blank cell instead of an Error message
Using IFERROR with a formula
In this example, the IFERROR function is combined with VLOOKUP; value_if_error is “Not found”.
How to use IFERROR in Google Spreadsheet?
If you use Google Docs instead of Microsoft Excel, you’ll discover it’s quite similar…
Follow the same 4 steps we shared previously for a clean document!
• type =IFERROR( in the selected cell
• in place of value, select the cell you want to check or enter a formula
=IFERROR(value,
• in place of value_if_error, type within ” ” the custom value you wish to appear when there is an error
=IFERROR(value, value_if_error)
• press Enter
Syntax of the IFERROR formula: =IFERROR(value, value_if_error)
In Google spreadsheets, value_if_error is optional and blank by default. If you wish to display an empty cell instead of an Error message, simply use this syntax:
=IFERROR(value)
Note that =IFERROR(value,) will result with an empty cell too in case of errors in value.
Notes
• If you use “” as the value_if_error argument or if it’s empty, nothing will be displayed when an error is detected.
• The system will show an empty cell instead of an Error message if you forget to add ” ” around your value_if_error.
• If value is empty, the system does not consider it as an error and nothing will be displayed.
• If value is a formula, the IFERROR function will return the formula’s results if any.
Examples
Return your custom value instead of an Error message
In this example, value_if_error is “Incorrect value”.
Return a blank cell instead of an Error message – Example 1
In this example, we use =IFERROR(value).
Return a blank cell instead of an Error message – Example 2
In this example, we use =IFERROR(value,).
Return a blank cell instead of an Error message – Example 3
In this example, we use =IFERROR(value, value_if_error); value_if_error is “”.
So if you are tired of handling out reports or dashboards with error values everywhere, now, you know how to modify them!
