If cell is blank

In the example worksheet, column D contains a date when a task is completed. If the task is not yet complete, the cell in column D will be empty (blank). In column E, the goal is to display the word "Open" when there is no date in column D. If there is a date in column D, the formula in column E should display nothing. The most common way to solve a problem like this is with the IF function.

IF function

The IF function runs a logical test and returns one value for a TRUE result, and another value for a FALSE result. The IF function can return a hard-coded value, a cell reference, or another formula. The generic syntax for IF looks like this:

=IF(logical_test,value_if_true,value_if_false)

To display "Open" when there is no completed date, the formula in cell E5 is:

=IF(D5="","Open","")

The logical expression D5="" means "is empty". As the formula is copied down, it will return "Open" when the date in column D is blank because the logical test will return TRUE. If there is a value in column D, the logical test will return FALSE and IF will return an empty string ("") as a result. In Excel, an empty string will not display anything. The values returned by the IF function can be customized as needed.

ISBLANK function

You can also use the ISBLANK function to test for an empty cell. To use ISBLANK instead of the expression D5="" revise the formula as follows:

=IF(ISBLANK(D5),"Open","")

The result is the same as the original formula above. There is no practical difference between the formulas, the choice depends on personal preference.

LEN function

There are times when you may need to use the LEN function to check for a blank cell instead of the methods above, because when a formula in a cell returns an empty string, Excel will not consider the cell blank even though it looks blank. For example, if cell A1 contains a formula, the formulas below will always return FALSE, even if the formula returns an empty string (""):

=A1="" // returns FALSE
=ISBLANK(A1) // returns FALSE

This happens because Excel considers A1 as not blank since it contains a formula. If you need to test for values in cells that contain formulas, you can use the LEN function to check for a character count of zero:

=LEN(A1)=0 // returns TRUE

The LEN function will return zero if a cell is truly empty or contains an empty string generated by a formula. The expression above can be used as the logical test in the IF function

=IF(LEN(A1)=0,"Open","")

This is a more literal formula. We are not asking Excel if A1 is blank, we are literally counting the characters in A1. The LEN function will return a non-zero number only when a cell contains actual characters. Using the LEN function this way works for cells containing formulas as well as cells without formulas.

Conditional formatting

Another way to highlight open tasks is to use conditional formatting. In the screen below, this formula is used to highlight rows that do not contain a completion date:

=$D5=""

This is an example of applying conditional formatting with a formula. When a date is entered in column D, the formatting will disappear. More examples here.