Updated date:

How to Remove Errors in Excel With the IFERROR Function

Joshua is a graduate student at the USF. He has interests in business technology, analytics, finance, and lean six sigma.

The IFERROR Function of Excel

The if error is useful for clearing out 7 different types of errors that occur within Microsoft Excel. The great thing about this function is that you can customize you own error messages for simply leave a blank to decrease the distraction.

The if error is useful for clearing out 7 different types of errors that occur within Microsoft Excel. The great thing about this function is that you can customize you own error messages for simply leave a blank to decrease the distraction.

The IFERROR function has a very specific use in Microsoft Excel. It was created to remove error messages when errors occur within cells. Here well look at the syntax of the function, errors messages that can be hidden and an example of this functions use.

Syntax of the IFERROR Function

The IFERROR function has a very simple syntax shown below.

=IFERROR(value, value_if_error)

There are two argument that are required to use this function.

  • Value

    This needs to be a cell reference, a calculation, a function or series of functions.

  • Value if Error Occurs

    This value can be what you want it to be. To display nothing simply use "" for this argument. If you want to add text for a number as text then put them inside of quotes.

Errors Messages that the IFERROR Function Can Hide

These references are provided to show what error messages the IFERROR function can hide. Each error is described in detail. Note that there may be more reasons for these errors to occur besides what is listed below.

#NULL! – Cell references are separated incorrectly.

#N/A – Can’t find referenced data.

#NUM! – Invalid numeric value.

#REF! – Specific reference is not valid within an argument.

#DIV/0! – Division by zero.

#NAME? – Text within a formula is not recognized.

#VALUE! – The wrong type of function or operand has been used.

IFFERROR Example

In the example below, I have an error because one piece of data is missing from one of my reference inputs for an equation. As a result, an error is displayed. Instead of having the error message "#VALUE1" appear when I have this error, I would like a custom message to appear for the user of the worksheet.


how-to-remove-errors-in-excel-with-the-iferror-function

The IFERROR Solution

The formula that is causing the error is shown below.

=2*(1-NORMSDIST(ABS((H12-H8)/(H9/SQRT(H11)))))

The error is occurring because the cell reference H8 in the formula is missing from the H8 field. There are a few steps to replacing the error with a custom message.

  • Step 1. Click in the cell and position the cursor after the equals sign.
  • Step 2. Type IFERROR(
  • Step 3. After the last parenthesis, type a comma
  • Step 4. Type the message that you choose to appear in quotes
  • Step 5. End with a closed parenthesis and enter

Completing these steps will replace the error for a custom message.

How to Remove Errors in Excel With the IFERROR Function

To learn more about using functions in Excel I recommend the following book. I have been using the Excel Bible for years to improve my understanding of all aspects of this Microsoft product.

The Excel 2019 Bible

References:

Microsoft. (n.d.). IFERROR function. Retrieved January 4, 2020, from https://support.office.com/en-us/article/iferror-function-c526fd07-caeb-47b8-8bb6-63f3e417f611.

Smith, J. (2019, January 8). How to Use the IFERROR Function in Excel. Retrieved January 5, 2020, from https://hubpages.com/technology/How-to-Use-the-IFERROR-Function-in-Excel.

Crowder, J. (2020, January 4). How to Remove Errors in Excel With the IFERROR Function. Retrieved January 4, 2020, from https://youtu.be/TNBsbEL_UGY.

Related Articles:

How to Use the COUNT Function in Excel

How to Use the AVERAGE Function in Excel

How to Use the ABS Function in Excel

This content is accurate and true to the best of the author’s knowledge and is not meant to substitute for formal and individualized advice from a qualified professional.

© 2020 Joshua Crowder