Return Blank if? Meaning in Excel & Google Sheets

This article explains what “return blank if” means in spreadsheet formulas, how to make a formula output an empty cell instead of a zero or an error, and the practical differences between a truly blank cell, an empty string (“”), and a zero. You will learn the exact formulas to use in Excel and Google Sheets, along with common pitfalls and how to troubleshoot them.

What “Return Blank If” Means

In spreadsheet software, “return blank if” refers to a formula that checks a condition (like a cell being zero, empty, or an error) and, if that condition is true, outputs a blank cell rather than a number or text. The most common use is to prevent zeros from cluttering a report: =IF(A1=0, "", A1) shows nothing when A1 is zero, and shows the value otherwise. This is different from hiding zeros with number formatting, which only changes the display; a formula that returns “” actually changes the cell’s content.

How It Works: The IF Function

The core of “return blank if” is the IF function, which has three parts: IF(condition, value_if_true, value_if_false). The condition is any logical test, such as A1=0, ISBLANK(A1), or ISERROR(A1). When the condition is true, the formula returns the second argument; when false, it returns the third. To return a blank, you use an empty string "" as the value. For example, =IF(B2="", "", B2*C2) leaves the cell blank if B2 is empty, otherwise it calculates the product.

Return Blank Instead of Zero

The most frequent request is to show a blank instead of a zero when a referenced cell is empty. A simple =A1*B1 returns 0 if either A1 or B1 is empty, because empty cells are treated as 0 in arithmetic. To return a blank instead, use: =IF(OR(A1="", B1=""), "", A1*B1). This checks if either cell is empty and, if so, returns “”; otherwise it multiplies. For a single cell, =IF(A1="", "", A1) works. You can also use =IF(A1=0, "", A1) to blank out actual zeros, though that may hide legitimate zero values.

Return Blank Instead of an Error

Formulas like VLOOKUP or division can produce errors (#N/A, #DIV/0!) that you might want to hide. Use =IFERROR(your_formula, "") to return a blank when any error occurs. For example, =IFERROR(VLOOKUP(D2, A:B, 2, FALSE), "") shows a blank if the lookup value isn’t found. If you want to handle specific errors differently, use =IF(ISERROR(your_formula), "", your_formula), but IFERROR is simpler and covers all error types.

Blank vs. Empty String vs. Zero

A truly blank cell is completely empty: no value, no formula. An empty string ("") is a text value of zero length; it looks blank but is not empty. A zero is a number. These differences matter:

• Counting: COUNTA counts cells that are not empty, so it counts empty strings but not truly blank cells.
• Arithmetic: Empty strings are treated as 0 in most arithmetic, but can cause type errors in some functions.
• Formatting: Conditional formatting that checks ISBLANK will not trigger on an empty string.
• Charts: Empty strings may create gaps or zero points in charts, while truly blank cells are often ignored.

If you need a truly blank cell, you cannot achieve it with a formula; you must use a macro or manually clear the cell. For most purposes, an empty string is sufficient.

How to Choose the Right Formula

Select the approach based on what you want to blank out:

• Blank if source is empty: =IF(A1="", "", A1)
• Blank if result is zero: =IF(A1=0, "", A1)
• Blank if error: =IFERROR(formula, "")
• Blank if any of multiple cells are empty: =IF(OR(A1="",B1=""), "", formula)
• Blank if a condition is met (e.g., date past due): =IF(A1

Always test with a few examples to ensure the condition matches your intent.

Common Problems and Solutions

Users often encounter these issues:

• Formula returns 0 instead of blank: Check that you used "" not 0 in the true part of IF.
• Blank cells are counted in COUNTA: This happens because "" is not truly blank. Use COUNTBLANK to count truly blank cells, or adjust your formula to return a truly blank cell (not possible with a formula).
• Charts show gaps: Empty strings create gaps; use =NA() to make charts skip points, or filter the data.
• IFERROR hides real problems: It catches all errors, including ones you might want to fix. Use it only when you expect errors.
• Copy-paste values brings back “”: If you paste values, the empty strings become actual text. Use Paste Special > Values, then find and replace “” with nothing.

Alternatives to IF with “”

In newer Excel versions, you can use the IFS function for multiple conditions, but the result is the same. For hiding zeros, you can also use custom number formats like 0;-0;;@ which display nothing for zero without changing the cell value. However, this only affects display, not the underlying value. In Google Sheets, the IF approach is identical. There is no built-in “return blank” function; the empty string is the standard method.

Frequently Asked Questions

Why does my formula return 0 instead of a blank?

You likely used 0 in the true part of IF, or you didn’t use IF at all. For example, =A1*B1 returns 0 if either cell is empty. Wrap it with =IF(OR(A1="",B1=""), "", A1*B1).

How do I return a truly blank cell with a formula?

You can’t. A formula always returns a value, and "" is a text value. To get a truly blank cell, you would need to use a macro to clear the cell or manually delete the formula. For most purposes, "" is visually blank and works fine.

What’s the difference between ISBLANK and =""?

ISBLANK(A1) returns TRUE only if A1 is truly empty (no value, no formula). A1="" returns TRUE if A1 contains an empty string (a formula that returns “”) or is truly blank, because blank cells are treated as empty strings in comparisons. So =IF(ISBLANK(A1), "", A1) will not blank out cells that contain an empty string, while =IF(A1="", "", A1) will.

How do I make a VLOOKUP return a blank instead of #N/A?

Wrap the VLOOKUP in IFERROR: =IFERROR(VLOOKUP(...), ""). This returns an empty string if the lookup fails, instead of the error. If you need to distinguish between not found and other errors, use =IF(ISNA(VLOOKUP(...)), "", VLOOKUP(...)).

Will an empty string affect my SUM or AVERAGE?

No. SUM and AVERAGE ignore text values, including empty strings. However, COUNTA will count them, and some functions like CONCATENATE will include them (as nothing). For most calculations, empty strings are treated as 0, which is usually what you want.

How do I remove empty strings from a column?

Select the column, press Ctrl+H (Find & Select > Replace), find what: "" (without quotes, just two double quotes), replace with: leave blank, and click Replace All. This will replace the text “” with nothing, making the cells truly blank. Be careful not to replace legitimate double quotes in your data.

Related

• RAR ZIP Cannot Be Extracted? Fixes That Work
• 750 kW & Megawatt EV Charging: Can Today’s Cars Use It?
• Relationship Vocabulary Words: 50+ Terms Explained

in value | Input value | - | 0 | -1000000 | 1000000
out result | Returned value | - | IF(value=0, "", value) | 2