How to Return Value in Excel If Cell Is Blank

With Excel, we can manipulate numbers and data in any way possible. There are often cases when we have blank spaces in our range and we want to find out in which row these values can be found.

In the text below, we will explain how can you return the value when the cells are empty.

Using the IF Function

For our example, we will create a column that has a list of random numbers:

Now, we can see which one of these cells is blank, but let us pretend that our scope is a lot larger. One way in which we can find out which cells are blank is to use the IF function.

IF function is pretty simple. It has three arguments:

• logical_test: Expression that we determine and that can be either true or false.
• value_if_true: [optional] The value that will be returned if our logical test is true.
• value_if_false: [optional] The value that will be returned if our logical test is false.

We will create an IF function in column B. The function in cell B1 will be:

Our logical test is to search if the value in cell A2 is blank (“” stands for blank). If this test turns out to be true, then we will return the value “Blank”. If not, i.e. if we have the value in the cell, “Not blank” will be returned.

We will drag the function till the end of our range (till the cell B17) and our table will look like this:

Using the ISBLANK Function

Perhaps the easiest way to find out which cells in our range are blank, and which are not, is to use the ISBLANK function.

This function has only one argument, and that is the „value“ that we want to check. It will return value TRUE if the cell is empty, and FALSE if it is not.

We will input the following formula in cell C2:

We will drag this formula till the end of our range, and our table will look like this:

Using Count Function with Blank Cells

A couple of useful formulas that can help us out with blank cells are COUNTA and COUNTBLANK.

COUNTA is the formula that counts all the cells in a range that are not empty.

COUNTBLANK is the formula that counts all the cells in a range that are empty.

We will input the following formulas in cells E2 and F2:

We will have the following results:

With this formula, we can simply count all the rows in the desired range.