IFERROR in Google Sheets: Explained with Steps & Example

Sometimes you change a value somewhere in one cell in the sheet, and it results in multiple errors. The only solution at that point is to keep hitting Ctrl + Z to undo changes till those errors disappear and you restore your original values.

The good thing about Google Sheets is that you can customize the appearance and function to any level. For instance, there is a function that replaces the error messages from Google Sheets, and that’s none other than IFERROR.

It helps you replace the robotic error messages with a slightly elaborated customized error message. It basically puts a condition on whether a cell has encountered an error or not. If it encounters, it then performs the function where you replace the message text.

Using the IFERROR Function in Google Sheets

You can write any error message you want in the syntax. The only downside of this function is that it cannot specify which error your cell is encountering.

To make sure that the message depicts the specific error, you have to use different syntaxes for all the cells where you put the IFERROR.

The syntax of the IFERROR function consists of mainly two things: the reference and the error.

• The reference denotes either the value or the applied formula in a cell.
• The error denotes the error message or a particular value that you want to display in case Google Sheets triggers an error.

The syntax of the IFERROR function looks something like this:

=IFERROR(reference,[value_if_error])

Example of Google Sheets’ IFERROR Function

If someone enters an alphabet instead of a number in a cell that is formatted as a number or currency then instead of the standard error message, you can replace it with a more precise and uniform error message with the following syntax:

=IFERROR(B24, “Incorrect Value Entered”)

This means if there is an error with the cell B24 then Google Sheets is supposed to come up with the message “Incorrect Value Entered”.

We will get to the use cases later in this article but first, let us take a look at a few basic steps to use the IFERROR formula.

1. Open the Google Sheets file.
2. Select the cell where you want to replace an error message.
3. After selecting the cell, go to the function bar and write =IFERROR(B24, “Incorrect Value Entered”).
4. Now, drag the corner of the cell to apply the same function to the rest of the cells.

Now if someone enters an alphabetical value to the cell number B24, it will return with the error message “Incorrect Value Entered”.

Using IFERROR with Arrays

Arrays are functions that club a range of cells together and perform the same formula on all of them. You can use the IFERROR function to the same range.

For every cell that triggers the error, the IFERROR function will replace the standard error with the custom message.

For instance, if your array formula is about division like ArrayFormula(A1:A5/B1:B5) then you must apply the IFERROR formula above it.

The formula to wrap the standard function with the IFERROR function will be something like this:

=IFERROR(ARRAYFORMULA(A1:A5/B1:B5))

With this, every value that is invalid (like division by 0) will show a blank cell instead of a default error message from Google Sheets.

Use Cases for IFERROR

The one mentioned above is a pretty straightforward example. You can use the IFERROR function for failures or incompatibilities of many complex formulas as well. Here are some common use cases for the IFERROR function.

• Blank: You can put up a blank message where you believe the cell may encounter an incompatible entry like division by 0 or unavailability of data search through VLOOKUP or HLOOKUP.
• Message: You can also put up an error message with a custom text for each cell or range of cells instead of the standard Google Sheets error message, which could be difficult for non-professionals to decode.

Common Errors in Google Sheets: Explained

The commonality is defined by popular practices by us, the users. Here are some of the most common errors you could encounter in Google Sheets.

• #ERROR!: This is the most generic error message you may encounter. This message occurs when Google Sheets cannot comprehend the type of incompatibility it encounters.
• #N/A: When you tell Google Sheets to access a cell that it is not able to, then it simply comes out with the “Not Available” error. This error most commonly occurs when you are using the VLOOKUP or HLOOKUP functions and the cells are not accessible.
• #DIV/0!: As the name gives away, this is an error message when a formula that you wrote ends up dividing a value by 0. Since dividing any number by 0 is not possible mathematically, Google Sheets prompts this message straight away.
• #REF: This error occurs when your function refers to an address (a cell or a value) that is not traceable for Google Sheets. It could be a missing reference in your formula or a circular reference that does not make any sense.
• #VALUE: This error message is prompted when the value you entered in a cell does not match its formatting. For instance, if you enter an alphabet in a cell that is formatted as currency, then it will show this error.
• #NAME?: This error message comes when you wrongly put up a formula. It could be a wrong syntax of a function, an irregular selection of range, or a spelling mistake.
• #NUM!: This error message concerns incompatible numerical values. If you try to perform an incompatible numeric formula, put up a large number or a decimal or fractional number instead of full, then you are likely to be prompted with this error.

The key is to distinguish errors and display custom messages for each error. Here are some tips to make the most of this function:

• Customize: Instead of putting up a blank cell or a standard “Error” text, try to customize each message based on each error type.
• Suggest: Instead of just identifying the problem through the error message, try to include suggestions in the error message as well.
• Mix: Mix the IFERROR function with other functions like the ArrayFormula or the SUM function. Don’t limit the IFERROR function to a mere replacement message for incompatible data.

Not only IFERROR but several other functions in Google Sheets help organize data better. These include transpose, not equal, and count if.

Frequently Asked Questions

Wrap Up!

IFERROR is great when used with arrays. It applies where it is supposed to and bypasses through if there are no errors. But the key is in the replacement message. It helps if your replacement message text is clear, and elaborate, and also suggests the troubleshooting steps.