How to hide errors in Google Sheets

The Google Sheets logo.

If you break a formula in Google Sheets, an error message will appear. You may prefer to hide these error messages to get a clean worksheet, especially if the global data is not affected, using the IFERROR function. Here’s how.

Hide error messages in Google Sheets using IFERROR

The IFERROR function checks to see if a formula that you use results in an error. If so, IFERROR allows you to resend an alternate message or, if you prefer, no message at all. This hides any potential error messages that may appear when performing calculations in Google Sheets.

An example of various Google Sheets formula errors.

There are a number of errors that can appear in Google Sheets that IFERROR can handle. For example, if you try to apply a mathematical function to a cell containing text (eg. =C2*B2, or B2 contains text), Google Sheets will display a “#VALUE” error message.

No worksheet is perfect, so error messages don’t necessarily indicate a problem, especially if you are performing calculations on a dataset that combines text and data. If you want to completely hide these messages, you will need to nest (combine) your initial formulas into an IFERROR formula.

RELATED: How to restrict data in Google Sheets with data validation

How to use an IFERROR formula in Google Sheets

IFERROR is a simple function with only two arguments. The syntax of a formula containing IFERROR is something like this:

=IFERROR(A2,"Message")

An example of an IFERROR formula in Google Sheets using a reference to another cell.

The first argument is the formula that IFERROR checks for errors. As the example above shows, this can be used to refer to other cells (cell A2 in this example) to hide formula error messages that appear elsewhere.

These formulas can also be nested directly in an IFERROR formula. For example:

=IFERROR(0/0, "This formula has an error!")

An example of IFERROR formula in Google Sheets with a nested function.

The second argument of an IFERROR formula is the custom error message that replaces Google Sheets’ own message. For example, the illustration above shows that dividing zero by zero is not possible. Rather than displaying the error message from Google (# DIV / 0!), A custom error message appears.

If you prefer not to have an error message, you can use an empty text string as the second argument. For example:

=IFERROR(0/0,"")

Example of IFERROR formula in Google Sheets, showing empty error message using empty text string.

Rather than displaying an error, the empty text string is “displayed”, but because it is not visible, the cell appears empty. Unlike Excel’s IFERROR formula, IFERROR in Google Sheets will also hide error indicators, the little red arrows that appear above cells to alert you to an error.

An example of a Google Sheets formula error indicator, successfully hidden by an IFERROR formula.

The IFERROR function won’t fix the problems with your calculations, but if you need to clean up your spreadsheet and don’t mind missing a few error messages, IFERROR is the best way to do it in Google Sheets. .

RELATED: How to hide error values ​​and flags in Microsoft Excel

Leave a Reply

Your email address will not be published. Required fields are marked *