IFERROR Google Sheets: Everything you Need to Know
If you need help hiding errors, this article is for you! Learn the different types of errors in Google Sheets and how to hide them using IFERROR. Learn the advantages and disadvantages of using it in your spreadsheets and dashboards!
Google Sheets IFERROR Function
IFERROR is used to replace the error code with a statement, so that if one occurs, it makes troubleshooting easier.
IFERROR Syntax
=IFERROR(reference,[value_if_error])
Where:
reference
Can either be the reference to a cell or include a whole formula inside, hiding the errors.
[value_if_error]
If an error occurs, this will be displayed as the value. If you leave this blank, the output will be blank as well.
If the reference does not produce an error, the function will display the value as is.
IF ERROR EXAMPLES
Interpret #N/A from VLOOKUP as “Value not found”
If you place VLOOKUP formula inside IFERROR, the error won’t even show up in the sheet.
Indicate #VALUE as improper data type
You can also wrap it around the original formula to hide the error and save space in your sheet:
IFERROR Disadvantages
Cannot distinguish between different types of Google Sheets errors
The formula will still display the same message even if a different error appears:
Here, the formula is programmed to interpret #VALUE! (improper data type) but the real error code is #DIV/O! (division by zero).
If you need to be able to know which is which, you can combine ERROR.TYPE with IF and SWITCH , as in this example:
This determines the type of error and displays the appropriate message. Click here to learn more about this technique.
Hiding errors
If you need to hide the error, you can use this function by only adding the reference inside it:
=IFERROR(reference)
This is not recommended; there are functions and formulas that also set blank values as valid output for a specific input value. This will hinder your ability to spot issues in your Google Sheets dashboard.
Related IF Functions
IF: Allows you to check for specific conditions across a dataset
SUMIF: Use to sum numbers if they meet a certain condition..
SUMIFS: Sums data from cells that meet multiple criteria
COUNTIF: count data if it fulfils certain criteria
COUNTIFS: Count data that fulfils two or more criteria.
COUNT IF Not Null: Count cells if they contain data
IFS: Allows you to check for multiple if conditions in a single statement
IF THEN: Evaluates data against a condition and take a corresponding action when the result is TRUE.
IF AND: Combining the IF and AND functions allows you to include multiple conditions that must be met to return a TRUE result.
IF ELSE: Evaluates a condition, and takes one action when the result is TRUE and a different one when the result is FALSE.
Multiple IF: Learn how to use multiple if statements in a single formula
IF OR: Evaluates multiple conditions and produces a TRUE result when any of them are met.
IF Contains: Returns cells that contain a particular text.
AVERAGEIF: Calculate an average for numbers within a data range if they meet the provided criteria.
