Google Sheets is an extremely user-friendly and reliable piece of spreadsheet software, but anyone who uses Google Sheets regularly will encounter an error from time to time. This can happen for a variety of reasons, which I’ll explain in this article. The IFERROR function helps you to clean up your spreadsheet and remove unwanted error messages.
Instead of displaying a bunch of red error messages, IFERROR enables you to “instruct” the spreadsheet what value to show if an error is found. This could be a blank cell, a number, or a relevant alert message that you and your colleagues can understand. Examples might include “Item out of stock” or “No results” or “N/A”.
Common errors in Google Sheets
The #VALUE! error
This is probably the most frequent type of error in Google Sheets. It occurs when you use an incorrect data type for the expected input arguments, as illustrated below.
#REF! error
Usually, this springs up when you intentionally or accidentally delete rows, columns, or sheets that are referenced in other cells.
If I remove the Operand A column from the above examples, this is what happens — as you can see below.
Notice that the #REF! error even appears inside the formula, indicating that it lost a reference in that placeholder. Learn more about this particular issue in How to solve the #REF! Error in spreadsheets.
#NAME! error
Google Sheets generates this error when it comes across a function that it doesn’t recognize.
To demonstrate that further, I’ll try using WHATIF (which is not an actual function) instead of IF.
#NUM! error
When the expected input arguments do not match with what you provide, the num error might show up. Take a look at the first example in the snapshot below.
A negative number is not expected in the SQRT function, hence the error.
This error can also appear when the number to display is out of range, as seen in the second example where the result is too large for the spreadsheet to compute.
#N/A! error
This is another common error usually associated with lookup functions when they cannot find a match for the value they are asked to find.
How IFERROR works
You can manage these errors and keep your spreadsheet working smoothly by using the IFERROR function.
If you are using a function and it keeps returning ugly errors, you can “wrap” the formula with IFERROR. This will give you a cleaner spreadsheet with data that’s easier to read.
It can be used with many common functions including Query and Vlookup.
IFERROR works by scanning a value for errors. If it finds one, it replaces the value with a blank cell — or whatever you specify. If there’s no error, it simply returns the original value.
Syntax
=IFERROR(value, [value_if_error])
- value – this is the argument that the function checks for errors. This can be any cell reference or a formula.
- value_if_error – this is the value the function returns if there is an error. It can be a number, word, or a text string such as “Item sold out”. This is an optional parameter so if you don’t specify anything, the IFERROR function will return a blank cell when it encounters an error.
How to use the IFERROR function
Example 1
In the first example below, you can see the effect of adding IFERROR to the beginning of the function.
I’m using the #N/A! error example that I showed previously. As you’ll recall, this appears when a lookup function can’t find the input it’s searching for.
Here, I’ve added the IFERROR function before the VLOOKUP.
If there is no error, the IFERROR function simply outputs the VLOOKUP output that it would normally generate.
If there is an error, however, instead of simply displaying an error message, I ask it to display the words “invalid input”. This enables you to avoid ugly and unexplained error messages in your spreadsheet.
In the example below, I’ve done this by typing =Iferror(Vlookup(“Stephen”,A2:B8,2,False),”Invalid input”).
Example 2
In the following example, you’ll see another type of error that can appear from time to time: the #DIV/0! error.
As you might have guessed, this occurs when you divide a number by 0.
This is quite common when you have a function that you drag down to apply to a group of cells, and some of the cells are missing data or contain zeros instead of larger numbers.
You can see this in the screenshot below:
Now, I’ll modify the formula to include the IFERROR function, by typing =Iferror(C6/B6).
As you can see below, this gets rid of the error message.
By adding IFERROR to the front of the formula, it simply returns a blank cell.
Using IFERROR with arrays
You can use IFERROR with the Arrayformula function but you must add it after the array formula, for example =Arrayformula(iferror(E4:E/B3:B.””)).
A note about hiding errors
As you’ve seen, the IFERROR function is great for masking error warnings and replacing them with clean, blank cells or a meaningful message or value.
Be careful, however, with when and where you apply this function. Hiding all errors can make it difficult to troubleshoot a spreadsheet when the error is affecting functionality. For instance, it doesn’t make sense to mask a #REF! error, because you need to know which cell reference is lost in order to fix the error and keep your spreadsheet working properly!
Did you like this post?
If you found this article helpful, please share it with your colleagues and fellow spreadsheet-users via the social media buttons on the left! For more Google Sheets tips, check out our other articles below or our 5 best tips for structuring your spreadsheet data.
Editor’s note: This is a revised version of a previous post that has been updated for accuracy and comprehensiveness.
