The IFERROR function in Google Sheets is used to effectively manage errors.
Instead of showing an error message, it assigns a value to show whenever an error is found.
Table of Contents
IFERROR function is a variation of the IF function that instructs the spreadsheet what to display when an error is encountered. It is also a more general version of the IFNA function, which is used when the #N/A error pops up.
Consider this example.
Say you were given the task of determining the average sales generated for each of the account managers by dividing the total sales by the quantity sold. The given data are as follows:
For the information that you do not have, you can choose to leave those blank and finish making the spreadsheet. After applying the formula for all the employees, you will notice a strange value pop up for those that have missing data. Upon selecting the cell containing this strange value, a note pops up that says, “Error Function DIVIDE parameter 2 cannot be zero.”
The reason for this error to show up is that the spreadsheet is trying to divide a value by zero. You still want to keep the formula present for when the missing data is inputted but would like to remove the strange value.
This is an instance where the
IFERROR function is exactly what you are looking for. Instead of showing an error, it allows you to show a blank cell, a number, or even a custom message instead!
Encountering errors in Google Sheets is completely normal that even veteran users still receive these strange and cryptic-looking values from time to time! It seems intimidating and even discouraging for these strange combinations of symbols and letters to pop up when you’re unfamiliar with what they actually mean.
These prompts are not a random assortment of characters, but they actually provide the user a decent idea of what these errors are. Let me walk you through the different types of errors that you could encounter whenever you use Google Sheets.
Different Types of Errors in Google Sheets
- #DIV/0 Error – this type of error shows up when a number is divided by zero
- #VALUE! Error – this error occurs when incorrect data types are used in a formula (i.e. having text involved in mathematical formulas)
- #N/A Error – called the “not available” error, this error occurs when a lookup formula is used, and the spreadsheet cannot find the value
- #REF! Error – called the reference error, this type of error shows up when the reference cell used in the formula is no longer valid or does not exist, usually a result of accidentally deleting rows or columns referenced in other cells
- #NAME? Error – this error shows up whenever Google Sheets does not recognize a function used in the formula, usually a result of misspelled functions (using IFEROR instead of IFERROR)
- #NUM! Error – this type of error occurs when the value calculated is either too large or is an invalid number (taking the square root of a negative number)
Still confused? Don’t worry! Examples for each type of error will be shown later. After a bit more exposure, these error messages will not seem as daunting as they are now.
No matter which kind of error you encounter, the
IFERROR function is capable of replacing the error value with any value that you specify. Now, let’s finally learn how to use the
IFERROR function to clean up those errors!
The Anatomy of the IFERROR Function
So the syntax (the way we write) the
IFERROR function is as follows:
Let’s dissect this thing and understand what each of these terms means:
=the equal sign is just how we start any function in Google Sheets.
IFERROR()this is our
IFERRORwill determine if the
valuereturns any of the errors presented earlier. If it does, the function will show the value placed in the
value_if_errorargument and will keep the
valueargument if it does not.
valueis the first argument. This is the value that the function checks for errors. A cell reference or a formula may be used. If no error is detected, the function will return this value.
value_if_erroris the second argument. This is the value that the function returns if there is an error. A number, word, or text string may be used. The square brackets ‘’ indicate that this is an optional parameter. If left blank, the cell will display nothing (i.e., a blank cell).
A Real Example of using the IFERROR Function
Take a look at the example below to see how
IFERROR functions are used in Google Sheets.
As you can see, the
IFERROR function returns the original value in the first argument if no error is detected. For any kind of error, the value shown can be a blank, number, word, text string, cell reference, or even a formula.
You may make a copy of the spreadsheet using the link I have attached below: 🙂
How to Use IFERROR Function in Google Sheets
Let’s try to fix the example for calculating the average sales earlier so that the error message will not pop up while waiting for the missing data to be obtained.
- Simply click on any cell to make it the active cell. For this guide, I will be selecting E3, where I want to show my result.
- Next, simply type the equal sign ‘=’ to begin the function and then followed by the name of the function, which is our ‘iferror’ (or ’IFERROR’, whichever works). You should find that as you are typing, an auto-suggest box appears with the names of the functions that contain the text that you have typed.
- The one we want is the IFERROR function, so make sure to click on the IFERROR function. Alternatively, you may select the function by pressing the arrow down keys then pressing Enter or Tab to use the function (the currently selected function will be highlighted in grey and have a brief description below).
- Upon selecting a function, a large text box appears that gives details about the function, and how to use the function. In some instances, a blue question mark will appear on the left side of the cell. If you want this text box to appear, simply click this question mark to show the large text box. Clicking on the arrow at the top right-hand corner will minimize the box while clicking on the x mark close the text box, and the blue question mark will appear.
- The average sales shall be computed by dividing the total sales by the quantity sold. For now, let’s focus on calculating this value for row 3. Select cell D3, then press the forward slash ‘/’ key, and finally select C3. This is the formula for dividing cell D3 by C3. (Note: cell references are color-coded so you can easily distinguish which is being referenced in the formula)
- Now, we can select what value will return if there is an error. I choose to leave a message saying “incomplete data” so that even a viewer can easily understand what is happening. To input this message, enter a comma ‘,’ after C3 to indicate that we are finished giving the
valueargument and would like to input the
value_if_errorargument. Then type “incomplete data”. For text, make sure that the message is enclosed in quotation marks and is highlighted in green as shown.
- Finally, just hit the Enter key. You’ll find that the text “incomplete data” shows up in cell E3 instead of the #DIV/0 error.
- To complete the table, simply copy the formula to the other rows.
IFERROR function is a powerful tool in cleaning up errors that present themselves whenever working with Google Sheets, it does not give you an opportunity to determine the reasons why these errors occur. It simply hides the error, not address nor correct it.
I personally recommend only using it for the #DIV/0 error and some #N/A errors. For the other types, it would still be better to troubleshoot and fix the error to ensure that your spreadsheet will still work properly.
That’s pretty much it. You can now use IFERROR function in Google Sheets together with the other numerous Google Sheets formulas to create even more powerful formulas that can make your life much easier. 😀