Google Sheets: How to Ignore Errors Safely

To make Google Sheets show a chosen value instead of a formula error, wrap the calculation in IFERROR; use IFNA when you only want to handle a missing-match error. For example, with a calculation in A2, =IFERROR(A2," ") displays a space if A2 evaluates to an error—but hiding an error does not fix its cause.

What “ignore error” means in Google Sheets

Google Sheets does not have a universal switch that turns off formula errors. Instead, a formula can catch an error and return something else, such as a blank cell, a message, or a fallback value. The underlying calculation still has a problem; the replacement merely changes what the cell displays.

Common spreadsheet errors include #DIV/0! for division by zero, #N/A when a lookup has no match, #VALUE! when a function receives an unsuitable value, and #REF! for an invalid reference. #NAME? can indicate an unrecognized function or name. These messages help identify the issue, so concealing them everywhere can make a sheet look tidy while making it harder to spot bad data or broken formulas.

People sometimes mean different things by “ignore error.” They might want a blank instead of an error, want a lookup to say “not found,” or want Sheets to stop warning about a cell. For formula results, error-handling functions are the relevant solution. They do not disable data validation, warnings, or errors caused by a formula that cannot be parsed.

How IFERROR and IFNA work

IFERROR evaluates its first argument. If that calculation returns an error, Sheets returns the replacement value in its second argument; otherwise, it returns the calculation’s result. It handles a broad range of formula errors, which makes it convenient but also easy to overuse.

IFNA follows the same general pattern but catches only #N/A. That makes it a more precise choice for lookups where a missing result is expected. If a lookup formula has a different problem, such as an invalid range reference, IFNA leaves that error visible rather than disguising it.

Both functions are available in Google Sheets and Excel. The examples here use Google Sheets in a web browser; the formulas work in Excel too, although menu locations and other spreadsheet features may differ. Formula argument separators can depend on a spreadsheet’s locale: if a comma-separated example produces a parse error, check the locale and use the separator expected by that file.

Examples: replace errors with a blank or message

To avoid showing a division error while a denominator is zero or unavailable, you can wrap the calculation. With the numerator in A2 and the denominator in B2, use:

=IFERROR(A2/B2,"")

If A2 contains 12 and B2 contains 0, the result is a visually blank cell. If B2 later contains 3, the formula returns 4. The blank is only a display choice: it does not mean the division was successfully calculated when the denominator was zero.

A blank is not always informative. If a user needs to know that a value is missing, return a message instead. With the numerator in A2 and the denominator in B2, use:

=IFERROR(A2/B2,"Check denominator")

For the same values of 12 and 0, the cell displays “Check denominator.” This can be clearer in a shared worksheet because it tells a reader that the result needs attention rather than suggesting the cell was intentionally left empty.

For a lookup, consider a table with product codes in A2:A10 and prices in B2:B10, and a code to search for in D2. To show a friendly message only when the code is not found, use:

=IFNA(VLOOKUP(D2,A2:B10,2,FALSE),"Not found")

If D2 contains a code in the table whose price is 8.50, the formula returns 8.50. If the code is absent, it displays “Not found.” Because the formula uses IFNA, other errors—such as a broken range—remain visible and can be investigated.

Choosing between a blank, a message, and a fallback

The right replacement depends on what the error means to the person using the sheet. Consider whether the error is an ordinary outcome of the workflow, whether someone must take action, and whether a blank could be mistaken for a valid result.

Use a blank when a result is genuinely optional or a report should remain uncluttered. Make sure readers know that blank cells may represent calculations that could not be completed.
Use a message when someone should check an input, add missing data, or correct a record. Messages such as “Not found” are often more useful than an unexplained empty cell.
Use a fallback value only when it is meaningful for the calculation. Returning zero for a missing price, for example, can change totals and averages as if the price were actually zero.
Leave the error visible when it signals an unexpected problem that should be fixed, especially in working or audit-sensitive data.

Think about later calculations too. A blank returned as an empty string is text, not a numeric zero. Some functions ignore text in certain contexts, while other formulas may treat it differently or produce another error. Test the replacement through the rest of the sheet instead of judging only by how one cell looks.

Common problems with hiding errors

A frequent mistake is wrapping an entire formula in IFERROR without knowing which part failed. If the original calculation has a typo, an incorrect reference, or an unexpected text value, the replacement can hide the evidence. During setup, it is often better to leave errors visible until the formula has been checked against normal inputs, missing inputs, and boundary cases.

Another problem is suppressing errors that should trigger action. For example, a missing product code may mean the source data needs updating. Replacing every lookup error with a blank can make that record disappear from a report without telling anyone why. A descriptive message or a separate review column may be safer.

Also distinguish a formula error from a formula parse error. Error handlers can return a replacement when a valid formula evaluates to an error. They cannot rescue malformed syntax that Sheets cannot interpret, such as unbalanced parentheses or a misspelled separator. Correct the formula structure first, then decide how to handle valid calculations that sometimes return errors.

Finally, be cautious with broad replacements such as zero. A zero may be counted in a total or average and can affect charts, conditional formatting, or downstream decisions. If zero is not truly the right business meaning, use a clear text label or preserve the error until the data issue is resolved.

How to find the cause before suppressing it

When a cell shows an error, inspect the formula and its inputs before adding error handling. Click the cell and review the formula in the formula bar; then check that referenced cells contain the expected values and that ranges include the intended rows. For a lookup, confirm that the search value actually matches the values in the lookup column, including spaces or differences in formatting.

A practical check is to test the formula with one known-good input and one problem input. If the known-good case also fails, the formula or range may be wrong. If only the problem case fails, decide whether that case is expected and what message or fallback makes sense. For a division, check whether the denominator is zero or blank. For a lookup, distinguish a genuinely absent key from a misspelled range or an incorrect lookup column.

Google Sheets displays formula errors in the affected cell, and selecting the cell can help reveal the error context. Use that information to correct the source issue where possible. Error handling is most useful after you know what failure it is intended to manage, not as a substitute for checking the calculation.

When to use other approaches

If you want to prevent an invalid entry rather than conceal its result, consider data validation. In Google Sheets, select the input cells and choose Data > Data validation to set rules such as a number range or a list of allowed values. Validation can reduce some input mistakes, though it does not guarantee every formula will be error-free.

For a report, it can also be useful to keep a visible status column alongside the calculation. One column can show the result, while another flags records that need review. That preserves useful information for the person maintaining the sheet and avoids making an empty output look like a completed calculation.

Conditional formatting can draw attention to values that need review, but it does not repair or suppress the formula error itself. Likewise, changing number formatting does not turn an error into a valid result. Choose the tool based on the goal: validation for input control, formula error handling for display behavior, and review indicators for workflow.

Frequently Asked Questions

Can Google Sheets ignore all errors automatically?

No. There is no general setting that disables formula errors across a sheet. You can handle errors in individual formulas with functions such as IFERROR or IFNA, but each formula needs an intentional replacement choice.

What is the difference between IFERROR and IFNA?

IFERROR catches many kinds of formula errors, while IFNA catches only #N/A. Use IFNA for expected missing lookup results when you want other problems to remain visible.

How do I show a blank instead of an error?

Wrap the calculation in IFERROR and use an empty string as the replacement value. Remember that the cell may look blank even though its formula still encountered an error.

Does IFERROR fix the formula?

No. It changes the displayed result when a formula evaluates to an error; it does not correct the source data, reference, or calculation. Find and fix the cause when the error indicates an unexpected problem.

Can I use these functions in Excel too?

Yes. IFERROR and IFNA are also available in Excel, and the examples use compatible formula syntax. Some interface steps and other features differ between Excel and Google Sheets.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.