If you break a formula in Google Sheets, an error message will appear. You might prefer to hide these error messages to get a clean spreadsheet, especially if the overall data isn’t affected, using the IFERROR function. Here’s how.
Hiding Error Messages in Google Sheets Using IFERROR
The IFERROR function checks whether a formula that you’re using results in an error. If it does, IFERROR allows you to return an alternative message or, if you’d prefer, no message at all. This hides any potential error messages that might appear when you’re performing calculations in Google Sheets.
There are a number of errors that can appear in Google Sheets that IFERROR can handle. For example, if you attempt to apply a mathematical function to a cell containing text (eg.
B2 contains text), Google Sheets will display a “#VALUE” error message.
No spreadsheet is perfect, so error messages might not necessarily indicate a problem, especially if you’re performing calculations on a data set that combines text and data. If you want to hide these messages completely, you’ll need to nest (combine) your initial formulas within an IFERROR formula.
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 a little like this:
The first argument is the formula that IFERROR is checking 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 into an IFERROR formula directly. For example:
=IFERROR(0/0, "This formula has an error!")
The second argument in 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 isn’t possible. Rather than display Google’s error message (#DIV/0!), a custom error message appears.
If you’d prefer to have no error message, you can use a blank text string as your second argument. For example:
Rather than showing an error, the empty text string is “displayed”—but, as this isn’t visible, the cell appears empty. Unlike Excel’s own IFERROR formula, IFERROR in Google Sheets will also hide error indicators—the little red arrows that appear above cells to warn you of an error.
The IFERROR function won’t fix 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 achieve that in Google Sheets.
- › How to Create Custom Functions in Google Sheets
- › How to Fix a Formula Parse Error in Google Sheets
- › How to Fix Common Formula Errors in Microsoft Excel
- › How Penetration Testing Keeps Systems Safe
- › Proton Mail and Calendar Are Getting Even Better
- › How to Add a Trendline in Google Sheets
- › How to Tune Your Guitar With Google Tuner
- › Tumblr and Flickr Might Join Mastodon’s “Fediverse” Network