Google Sheets IFERROR: Show a Blank Instead of Errors

Yes. In Google Sheets, wrap a formula in IFERROR and use an empty string as its fallback: =IFERROR(your_formula, ""). For example, this displays a blank when a division formula returns an error, while keeping the calculation in the cell.

What IFERROR does in Google Sheets

IFERROR checks the result of an expression. If the expression succeeds, Sheets displays its result; if it produces an error, Sheets displays the fallback you specify. The function’s general form is IFERROR(value, value_if_error). The second argument is optional: leaving it out also makes an error display as a blank.

This is useful when a sheet is designed for other people to read, or when an expected error would otherwise clutter a report. It does not repair the underlying formula or data, however. It changes what the cell displays when an error occurs, which is why it is important to decide whether hiding the error is appropriate.

#DIV/0! can appear when a formula divides by zero or an empty cell.
#N/A often means a lookup did not find a match.
#VALUE! can indicate that a formula received a value of the wrong type.
#REF! indicates an invalid reference, such as one that points to a deleted cell.

IFERROR catches errors broadly, not just one particular type. That makes it convenient, but it can also conceal a mistake that you would rather investigate.

How to make a formula return blank on error

Click the cell where you want the result, type the formula with your existing calculation in the first argument, and put two straight double quotes in the second argument. The empty pair of quotes is an empty text value. Press Enter, then fill or copy the formula down if other rows need the same treatment.

For example, suppose A2 contains an amount and B2 contains a quantity. The formula in C2 should divide the amount by the quantity. With the amount in A2 and quantity in B2, enter =IFERROR(A2/B2, "") in C2. If B2 is 4 and A2 is 20, the cell displays 5. If B2 is 0, the division error is replaced by a blank display.

When copying a formula, check that its cell references adjust as expected. A relative reference such as A2 normally changes to A3 in the next row. If you need a reference to stay fixed when copying, use dollar signs, as in $A$2. The dollar signs affect the reference; they do not change how IFERROR handles an error.

Using IFERROR for division and other calculations

Division by zero is one of the most common reasons to use the function. For example, a percentage-change formula may divide by a previous-period value that is zero or not yet entered. If the numerator is in B2 and the previous value is in A2, put =IFERROR((B2-A2)/A2, "") in C2. A change from 50 to 60 returns 0.2, which can be formatted as 20%; a zero or blank denominator produces the chosen blank fallback instead.

Think about whether every error in the calculation should be hidden. If the formula also contains a broken cell reference or an unexpected text value, the same blank fallback may hide that problem too. During setup, it can be helpful to leave errors visible until you confirm the formula is correct, then add IFERROR if a blank is genuinely the desired presentation.

You can also choose a message rather than an empty string. With the amount in A2 and quantity in B2, =IFERROR(A2/B2, "Check inputs") displays “Check inputs” when the calculation fails. A message can be clearer for a working sheet where someone needs to correct the source data; a blank may be better for a polished summary.

Handling missing lookup results

People often use IFERROR to hide the error from a lookup that finds no match. Suppose E2 contains a product code and A2:C100 is a table whose first column contains product codes and whose third column contains the price. With that layout, =IFERROR(VLOOKUP(E2, A2:C100, 3, FALSE), "") returns the matching price or displays blank if the lookup errors.

That broad catch can hide more than a missing product. For example, a misspelled range, an incorrect column index, or an unexpected issue in the lookup can also be concealed. If the only error you want to treat as an ordinary “not found” result is #N/A, consider IFNA instead. With the same lookup key and table layout, =IFNA(VLOOKUP(E2, A2:C100, 3, FALSE), "") replaces an unavailable match with a blank while allowing other error types to remain visible.

This distinction is useful when a missing match is expected but a damaged formula is not. A blank result can make a report cleaner, but leaving unexpected errors exposed makes the sheet easier to troubleshoot.

A blank display is not an empty cell

The empty string in "" is text returned by a formula. The cell is not truly empty: the formula remains in it, and other formulas may treat the result differently from a cell that contains nothing at all. This matters when you count cells, test for blanks, build charts, or export data.

For example, functions that count numeric values generally do not count an empty text result as a number, but a cell-counting function may still count the cell because it contains a formula. A test that checks whether a cell is blank can also distinguish a formula returning an empty string from a genuinely unused cell, depending on the function and test used. If downstream calculations rely on the difference, check the result in your actual sheet rather than assuming the cell has been cleared.

Similarly, a blank-looking result can affect charts or filters differently from a genuinely empty cell. If a chart should omit missing values, review its treatment of empty cells and formulas returning text. IFERROR controls the formula result; it does not erase the formula, remove the row, or guarantee that every other feature treats the cell as absent.

Applying the pattern to a range

For a list of row-by-row calculations, add the wrapper to each row’s formula and fill it down. If A2:A10 contains numerators and B2:B10 contains denominators, one array formula can calculate the range and return a blank for each division error: =ARRAYFORMULA(IFERROR(A2:A10/B2:B10, "")). Enter it in an output cell with the rows below it clear so the results have room to expand.

In this array example, the division is evaluated across the corresponding rows. A valid division returns its numeric result, while a division error in a row is replaced with an empty text result. This can be more convenient than maintaining a separate formula in every row, but array formulas occupy the cells where their results expand. Existing data in the output range can prevent the results from appearing, so clear that area first.

If you already have a formula copied down and only some rows should be blank, a row-by-row IFERROR formula may be easier to understand and maintain. Choose the approach that fits the sheet’s structure, especially if collaborators may need to edit the calculations later.

Common problems and how to avoid them

The formula still shows an error: Check that the original calculation is inside IFERROR’s first argument and that the fallback is separated by a comma. Parentheses and quotation marks must be balanced.
The cell looks blank but causes unexpected results: It contains a formula returning text, not an empty cell. Review how dependent formulas handle empty strings.
A lookup error disappears even though the formula is wrong: IFERROR may be hiding a reference or argument problem. Use IFNA when only a missing match should be suppressed, or temporarily remove the wrapper while diagnosing the formula.
Sheets rejects the commas: Formula argument separators can depend on the spreadsheet’s locale. If your sheet expects semicolons, replace the commas between arguments with semicolons; do not change the commas that are part of text or other formula content.
The range formula does not expand: Make sure the cells where the array results should appear are empty. Existing values in the way can block an array result.

Google Sheets is browser-based, so enter the formula directly in a cell or in the formula bar. The specific spreadsheet locale can affect argument separators and number formatting. Excel also has an IFERROR function with a similar purpose, but this article’s examples and entry instructions are for Google Sheets; Excel menu and interface details can differ.

When to choose a different approach

Use IFERROR with an empty string when any error in the wrapped expression should appear blank. Use IFNA when the intended exception is specifically a value that was not found, such as an unmatched lookup. If users need to take action, a short fallback message can be more helpful than a blank. And if the error signals a problem that should be fixed, leave it visible rather than suppressing it.

Another option for a known condition is to test for that condition directly. For instance, if the only concern is a zero denominator, a conditional formula can return blank when the denominator is zero and perform the division otherwise. That approach documents the expected case, but it does not automatically catch unrelated errors. The best choice depends on whether you want to handle one known situation or suppress every error produced by the calculation.

Frequently Asked Questions

What is the Google Sheets formula for blank if error?
Use =IFERROR(your_formula, ""), replacing your_formula with the calculation you want to run. The empty string tells Sheets to display blank when that calculation returns an error.

Can I use IFERROR to hide #N/A?
Yes. IFERROR hides #N/A along with other errors. If you want to handle only #N/A, use IFNA so other errors remain visible.

Does IFERROR delete the error or clear the cell?
No. It returns the result of the formula or the fallback value. When the fallback is "", the cell looks blank but still contains a formula.

Can I show a message instead of a blank?
Yes. Put text in the fallback argument, such as =IFERROR(A2/B2, "Check inputs"). This displays a message when the division fails.

Why does my formula use semicolons instead of commas?
Google Sheets uses the spreadsheet’s locale to determine formula separators. If the sheet expects semicolons, use those between function arguments; the function and its purpose are the same.

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.