Summary

To return a blank result (i.e. display nothing) based on a conditional test, you can use the IF function with an empty string (""). In the example shown, the formula in E5 is:

=IF(B5="a",C5,"")

As the formula is copied down, the IF function returns the value in column C when the value in column B is "A". Otherwise, IF returns an empty string ("") which looks like an empty cell in Excel.

Note: Excel is not case-sensitive by default, so B5="a" and B5="A" will return the same result.

Generic formula

=IF(A1="a",B1,"")

Explanation 

The goal is to display a blank cell based on a specific condition. In the worksheet shown, we want to return the value from column C, but only when the value in column B is "A". If the value in column B is anything else, we want to display nothing. The easiest way to solve this problem is with the IF function and an empty string ("").

IF function

The IF function runs a logical test and returns one value for a TRUE result, and another value for a FALSE result. For example, if cell A1 contains "Red", then:

=IF(A1="red",TRUE) // returns TRUE
=IF(A1="blue",TRUE) // returns FALSE

Notice the IF function automatically returns FALSE even though no value is provided for a false result. It is important to understand that the IF function is not case-sensitive. If cell A1 contains "Red", then:

=IF(A1="red",TRUE) // returns TRUE
=IF(A1="RED",TRUE) // returns TRUE
=IF(A1="Red",TRUE) // returns TRUE

Notice that text values inside IF must be enclosed in double quotes (""). However, numeric values should not appear in quotes. For example, if cell A1 contains 100, then do not use quotes to test for 100:

=IF(A1=100,TRUE) // returns TRUE

Enclosing a number in quotes ("100") causes Excel to interpret the value as text, which will cause the logical test to fail:

=IF(A1="100",TRUE) // returns FALSE

For more details about IF, see: How to use the IF function.

Empty strings in Excel

When the goal is to display nothing with a formula in Excel use two double quotes like this "". This is called an empty string ("") and it will display like an empty or blank cell on the worksheet. Note if you type "" directly into a cell in Excel, you will see the double quote characters. However, when you enter the quotes as a formula like this:

=""

You won't see anything, the cell will look empty.

IF with empty string

In the example shown, the formula in E5 is:

=IF(B5="a",C5,"") // returns 82

In this formula, the logical_test is B5="a", the value_if_true is C5, and the value_if_false is an empty string (""). As the formula is copied down, the IF function returns the value in column C when the value in column B is "A". If B5 contains any other value, IF returns an empty string ("") which looks like an empty cell in Excel. Although we are using a lowercase "a", an uppercase "A" produce the same result:

=IF(B5="A",C5,"") // returns 82

Testing for blank cells

There are many ways to check for blank cells in Excel, see this article for several options. If you need to check the result of a formula that returns an empty string (""), be aware that the ISBLANK function will return FALSE when checking a formula that returns "" as a final result. In other words, while you would expect ISBLANK to return TRUE, it actually returns FALSE.  For example, if cell A1 contains a formula that returns an empty string, then:

=ISBLANK(A1) // returns FALSE

One workaround is to use the COUNTBLANK function instead like this:

=COUNTBLANK(A1) // returns 1
=COUNTBLANK(A1)>0 // returns TRUE

You can use COUNTBLANK inside the IF function like this:

=IF(COUNTBLANK(A1)>0,true_result,false_result)

For more details on COUNTBLANK, see: How to use the COUNTBLANK function

Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.