Google Sheets IFERROR: Leave Cells Blank on Errors

To make a Google Sheets formula show a blank when it returns an error, wrap it in IFERROR and use an empty text string as the fallback. For example, with a value in A2 and a divisor in B2, enter =IFERROR(A2/B2,"") in C2; if B2 is zero, C2 appears blank instead of showing an error.

How IFERROR leaves a cell blank

IFERROR checks the result of an expression. If that expression evaluates normally, Sheets displays the result; if it produces an error, Sheets displays the alternative you provide. The function syntax is IFERROR(value, value_if_error). The second argument is optional, but leaving it out displays a default blank when the first argument errors. Supplying an empty string explicitly makes the intended display behavior clear to anyone reading the formula.

An empty string is written as two straight double quotation marks with nothing between them. It is text with zero characters, not a space. A space between quotation marks would still occupy a character and could affect searches, comparisons, and cleanup. Use the empty string when the goal is to keep an error message out of view without showing a substitute such as zero or a note.

IFERROR works with errors produced by the formula it evaluates, including common errors such as #DIV/0!, #N/A, #VALUE!, #REF!, and #NAME?. It does not remove the underlying cause. The formula is still being evaluated, and the error is simply replaced in the displayed result.

Using IFERROR in a real calculation

For a basic division example, assume A2 contains sales and B2 contains the number of units. Put the formula in C2. If A2 is 240 and B2 is 6, the result is 40; if B2 is 0, the result displays as blank:

=IFERROR(A2/B2,"")

This approach is useful when a sheet contains incomplete rows. A calculation may be valid once someone enters the missing quantity, but it would otherwise display an error while the row is unfinished. You can also fill the formula down a column to apply the same behavior to more rows. In Google Sheets, dragging the fill handle or copying and pasting adjusts ordinary cell references for each row.

For example, if a price is in A2 and a quantity is in B2, the same pattern can calculate a line total while hiding errors caused by an incomplete or invalid entry. Before copying formulas broadly, confirm that blanking every error is appropriate: a typo or broken reference should not necessarily be hidden along with an expected missing value.

Hiding lookup errors

A lookup that does not find a match commonly returns #N/A. To leave the result cell blank in that case, assume product codes are in A2:A100, product names are in B2:B100, and the code to search for is in E2. Place this formula in F2. If E2 is not found in the product-code list, F2 displays blank; if it matches a row, the formula returns the corresponding product name:

=IFERROR(VLOOKUP(E2,$A$2:$B$100,2,FALSE),"")

The dollar signs keep the lookup range fixed when the formula is filled down. The final argument FALSE asks VLOOKUP for an exact match. Without exact-match behavior, an approximate lookup can return a result that is not the intended code, depending on the data and lookup settings. Hiding errors does not make an inaccurate lookup accurate, so check the range, match type, and consistency of the codes.

If the only error you want to suppress is a missing match, IFNA is a more targeted choice. With the same product-code layout and search value in E2, enter this in F2; an unmatched code displays blank, but a different error in the lookup is not deliberately concealed:

=IFNA(VLOOKUP(E2,$A$2:$B$100,2,FALSE),"")

A blank is not the same as zero or an empty cell

The empty-string fallback makes a formula cell look blank, but the cell still contains a formula. That distinction matters when you sort, count, test, or export data. A true empty cell has no formula or entered value; a cell displaying an empty string has a formula whose result is zero-length text.

For example, a visually blank formula result is not a numeric zero. It will not display as zero in a total, and it is not the same thing as a cell containing a single space. However, spreadsheet functions do not all treat these cases alike. In Google Sheets, COUNTA counts values including zero-length strings, while COUNTBLANK can treat a formula result of an empty string as blank. If counts matter to a report, test the exact range and choose a counting method that matches what you mean by “blank.”

Use a blank fallback when the result is genuinely not ready or should not be displayed. Use zero when zero is a meaningful numeric result, such as no units sold. Showing a blank for zero can make a table look cleaner but can also hide meaningful information from readers or charts. Formatting cannot change that underlying distinction: number formats can alter how values appear, but they do not turn an error into a truly empty cell.

When to use IFERROR and when to use IFNA

IFERROR is convenient when any error in a calculation should result in the same fallback. It is often reasonable for a presentation layer or a temporary calculation where errors are expected until the user fills in data. Its broad coverage is also its main risk: it can mask several unrelated problems, not just the one you had in mind.

IFNA is narrower. It replaces #N/A, which often signals that a lookup found no match, while allowing other error types to remain visible. That can make it easier to spot a broken reference, an invalid input, or a function-name problem. Choose based on the cause you expect, rather than automatically wrapping every formula in the broadest error handler.

Use IFERROR when any error in the expression should be hidden and a blank is a safe outcome.
Use IFNA when the expected issue is “no match,” especially in a lookup, and other errors should still be investigated.
Use neither as a substitute for correcting a formula that consistently produces an unexpected error.

Avoid hiding errors you need to fix

A blank can make a sheet look polished, but it can also make a problem harder to notice. If a reference was deleted or a lookup range was mistyped, a broad error handler may turn the warning into an apparently ordinary empty result. That is risky in a workbook used for decisions, because a missing result can be mistaken for “nothing happened.”

One alternative is to prevent a predictable error condition explicitly. For instance, if the only concern is dividing by zero, assume the numerator is in A2 and the divisor is in B2; put this in C2. With B2 equal to zero it displays blank, and with A2 equal to 240 and B2 equal to 6 it returns 40:

=IF(B2=0,"",A2/B2)

This makes the zero-divisor rule visible, but it does not suppress other errors in the division. That can be an advantage: a reference problem or invalid value remains easier to detect. If an empty divisor and a zero divisor need different treatment, make that distinction explicit rather than treating both as interchangeable.

For important reports, consider adding a status column or a separate error check during development. You might temporarily remove the fallback, inspect the errors, and restore it only after confirming they are expected. You can also use conditional formatting to flag missing inputs or suspicious values. The right approach depends on whether users need a clean display, a visible diagnostic, or both.

Common problems and troubleshooting

The cell still shows an error: Check that the formula itself is wrapped, that both quotation marks are straight quotes, and that the parentheses are balanced. Also confirm the formula is in the cell you are viewing and that the error is produced by the expression inside IFERROR.
The result looks blank but is counted: The cell may contain a formula returning an empty string. This is different from deleting the formula or clearing the cell. Check whether your count needs to include formula cells or only visible results.
A lookup is blank even though a record exists: Check for extra spaces, different data types such as text versus numbers, a lookup range that omits the record, and the selected match mode. Hiding #N/A can conceal these problems rather than resolve them.
Blank rows appear in a chart or export: A formula returning an empty string may be handled differently from a genuinely empty cell by downstream tools. Review the chart or export behavior with representative rows before relying on the display as a data-cleaning step.
Every kind of problem disappears: The formula may be using IFERROR too broadly. Consider switching to IFNA for missing lookup results, or test the expected condition directly so unrelated errors remain visible.

Google Sheets and Excel differences

For the core use case, Google Sheets and Microsoft Excel use the same function name and the same basic pattern: IFERROR takes a value or expression and a fallback value. An empty text string is written with two double quotation marks in both programs. A formula written for Sheets can therefore often be understood in Excel, although surrounding features, separators under regional settings, and workbook behavior can vary.

This answer uses Google Sheets in a web browser. Enter or edit the formula directly in a cell or in the formula bar. There is no need to open a special error-handling menu. Excel also supports IFERROR, but its menus and interface are different; the formula itself is the relevant part here. If a spreadsheet uses a locale that expects semicolons rather than commas between function arguments, follow the separator convention shown by that sheet. Do not replace the empty-string quotes with curly typographic quotation marks.

Frequently Asked Questions

How do I make a Google Sheets error cell blank?
Wrap the expression in IFERROR and set its fallback to an empty string, written as two straight double quotation marks. This changes the displayed result, not the formula’s underlying cause.

Can I hide only #N/A and show other errors?
Yes. Use IFNA with an empty-string fallback. It is suitable when a missing lookup match is expected but other errors should remain visible for troubleshooting.

Does IFERROR delete the error or fix the formula?
No. IFERROR returns an alternative display value when the expression produces an error. The original issue remains in the formula or its input data and may need to be corrected.

Is a formula returning an empty string a truly empty cell?
No. The cell still contains a formula, even though it looks blank. This can matter for counting, sorting, charts, and data exports, so check how the functions or tools you use treat empty-string results.

Should I use a blank or zero as the fallback?
Use a blank when the result is unavailable or not relevant yet. Use zero when zero is the accurate, meaningful value. Hiding a real zero can make a table cleaner but may mislead someone interpreting the data.

Choose a fallback that preserves meaning

For the direct question, IFERROR with an empty string is the simple answer: it makes an error result appear blank in Google Sheets. The important choice is whether every error should be hidden. Use IFNA for missing matches, handle predictable conditions directly when that is clearer, and leave unexpected errors visible when they could signal a real problem. That way a tidy sheet remains useful as a source of information rather than merely looking error-free.

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.