You might receive error messages in Google Sheets when using formulas with incorrect references, formula breaks, and many other reasons. When you receive such an error, your data sheets get messy. Therefore, you may want to hide errors in Google Sheets to maintain a neat appearance.
Fortunately, you can use the
IFERROR function to do so. It detects whether your formula is error-free and alerts you about the error. Instead of an error message in your Google Sheets, you will get an alternative text or no text if you use this function. Eventually, you may get a clear spreadsheet.
The entire article walks you through how to use
IFERROR to hide errors in Google Sheets to keep your sheet organized and clean. Therefore, you should not skip any words here if you are needful.
Hide Errors in Google Sheets Using IFERROR Formula
Hide errors in Google Sheets easily with the
IFERROR function. However, choosing how to see the error message is completely up to you. There are two options: you can either accept the error message that Google displays or customize the error message.
Errors such as #VALUE, #N/A, #DIV/0 in your Google Sheets can hide using the IFERROR formula. Moreover, you can use IFERROR in filtering errors too.
1. Hide #VALUE, #N/A Errors Using IFERROR Function
The syntax for the
IFERROR function is
=IFERROR (value, [value_if_error]). Firstly, the ‘value’ of the syntax indicates the return value if no error occurs in the value, and secondly, ‘value_if_error’ indicates the value that is returned when the first argument fails. As a default, this ‘value_if_error’ is blank on Google, but you can provide your own custom text here.
Well, here is an example of error messages below on different formulas. And I’m going to use the
IFERROR function to hide errors in Google Sheets.
Now, look! Row numbers 3,4 and 6 of the C column are now blank without showing any errors. The
IFERROR the function has been applied here, which you can see in column D.
Instead of a blank cell, why not have a text of your own? That’s also possible. Therefore, you must write your preferred text in the position of ‘value_if_error’. Refer to the example below. The ‘Error’ text is here, which I want to appear in each cell where an error occurs.
2. Hide Errors Using IFERROR Function Through Filtering
Last but not least, filtering can hide an entire row in your Google Sheets that contains a formula error. In order to do this, select your data range, hover over Data in the top menu bar, and then click Create a filter.
Alternatively, you can use the keyboard shortcut (Ctrl+Shift+L) to activate the filer for your selected range. Now select the filter button from the top of the column that has the formula error.
Now that you are here, you need to deselect Error (it may be a blank cell if you didn’t apply Error text to your error cell) and click on OK.
As you can see, the resulting page is as follows. You no longer see the row that had an error in your Google Sheets. As a result, you no longer have any error messages, so your Google Sheets look nice and organized.
3. Hide #DIV/0 Errors Using IFERROR Function
If you divide anything in your Google Sheets by 0, you will receive an error message (#DIV/0!). That’s because it’s mathematically impossible. Nevertheless, you can hide this error by using the
IFERROR formula as well.
As you can see, on cell C2, a divide by zero error is visible, but not on cell C3. That’s because the
IFFERROR function has been applied to cell C3. As a result, the cell is clear without any error messages.
In conclusion, Google Sheets looks messy and unpleasant when it has any error messages. Furthermore, you may have difficulty working with complex data sheets containing many formulas. In that case, follow the above steps to hide errors in Google Sheets.
I’d love to hear about your experience after you finish the post. Feel free to share it if you find it helpful. Furthermore, please let me know if you know of any other technique to hide formula errors from Google Sheets.