How to Check If Cell Is Empty in Excel (7 Methods)

Microsoft Excel is one of the most popular applications used for official and business purposes. Further, as an analyst, we look out whether a cell is null in our Excel workbooks with Excel functions. In this article, we are going to discuss 7 suitable methods to check if a cell is empty in Excel. Let’s have a look at the overview, before diving into the methods in the upcoming section.

In the following section, we will access 4 functions, 2 features, and one VBA approach to check if the cell is empty in Excel. Moreover, we will learn to use the ISBLANK, IF, COUNTIF, and COUNTBLANK functions in order to implement the methods. Let’s go through the methods to get the number of empty cells of the following dataset where we take column B with employee names and one empty cell. Also, take a Result header in column C.

1. Using the ISBLANK Function to Check If a Cell is Empty in Excel

The ISBLANK function delivers TRUE or FALSE based on two states. If the argument is empty show TRUE, otherwise FALSE.

Syntax:

ISBLANK(value)

Argument:

value – This value will be tested. This may be empty or filled with text or logical value, etc.

Step 1:

•  Go to Cell C5 first.
• Write the ISBLANK function.
• Select B5 as the argument. So, the formula will be:

Step 2:

•  Now, press Enter.

Step 3:

•  Drag the Fill Handle icon to the last cell.

Now, we see that only one cell is empty and the result to that cell is showing TRUE. But the rest of the cells are showing False because these are not empty.

Read More: How to Find If Cell is Blank in Excel

2. Applying Excel IF Function to Check an Empty Cell

The IF function allows us to make logical comparisons between a value and what we expect.

So, an IF statement can have two results. The first result is if our comparison is True, and the second is if our comparison is False.

Syntax:

IF(logical_test, value_if_true, [value_if_false])

Argument:

logical_test – The condition we want to test.

value_if_true – The value that we want to return if the result of logical_test is TRUE.

Value_if_false – The value that you want to return if the result of logical_test is FALSE.

Step 1:

• Go to Cell C5.
• Type the following formula:

Step 2:

•  Then press Enter.

Step 3:

•  Drag the Fill Handle icon to the last cell.

Finally, we have got the output perfectly as shown in the screenshot above.

3. Combining IF and ISBLANK Functions to Check If a Cell is Empty in Excel

In this section, we will use the combination of IF and ISBLANK functions to check if the cell is empty.

Step 1:

• Go to Cell C5.
• Type the following formula:

Step 2:

• Press the Enter button.

Step 3:

•  Drag the Fill Handle icon to the last cell.

Here, it shows Blank for the empty cell and the rest are Not Blank.

4. Using the Find Command to Check If a Cell is Blank

We can also use the Find command to check if a cell in the worksheet is empty. Before doing this, we will modify the previous dataset a bit.

Let’s see how to do the task then.

Step 1:

•  Select the range from where to check the empty cells.

Step 2:

• Press Ctrl+F.
• Keep the Find what box empty.

Step 3:

•  Now, press Find All.

Here it is. We have successfully found the blank cells B7 and B9.

5. Checking If a Cell is Empty with Conditional Formatting in Excel

Conditional Formatting is a very useful tool in MS Excel. We can use this tool too to perform our tasks. Let’s see the steps one by one.

Step 1:

•  First, select the range of cells B5:B10 from where we will search the empty cells.

Step 2:

• Then, go to the Home tab.
• From the Conditional Formatting, command we select the Highlight Cells Rules.
• Now, go to More Rules.

Step 3:

• Now, choose Format only cells that contain.
• Select Blanks.
• Select the Fill Color from the Format option.

Step 4:

• Now, press OK.

In the result, we can see that the blank cells are filled with Red color as we selected the Red format.

Read More: How to Apply Conditional Formatting in Excel If Another Cell Is Blank

6. Checking If Any Cell in a Range is Blank with Multiple Excel Functions

6.1 Use of COUNTBLANK Function to Check Empty Cell

The COUNTBLANK function is one of the statistical functions. It is used to count the number of empty cells in a range of cells.

Syntax:

COUNTBLANK(range)

Arguments:

range – It is the range from which we want to count the blank cells.

Now, let’s see the steps one by one.

Step 1:

• Go to Cell C5 and write the COUNTBLANK function.
• Type the following formula:

Step 2:

• Then press Enter.

The result shows 1 as there is only an empty cell in that range.

6.2 Apply the COUNTIF Function to Check for Empty Cells

The COUNTIF function is one of the statistical functions. It calculates the number of cells that fulfill a condition.

Syntax:

COUNTIF(range, criteria)

Argument:

range – The operation will be applied to this cell range. This range contains multiple objects like numbers, arrays, etc. Empty and text values will not be considered for this function.

criteria – This condition will be on the formula. It will check from the given range.

Use COUNTIFS if we want to use multiple criteria.

Step 1:

• Write the COUNTIF function.
• The range is B5:B10 and compared with blank.
• If found blanks then show TRUE otherwise FALSE. The formula is

Step 2:

• Now, press Enter.

After applying this formula, we found only one empty cell and that number is showing.

6.3 Use of SUMPRODUCT to Check for Empty Cell in Excel

The SUMPRODUCT function originally performs a sum operation. It produces the sum of the products of given ranges or arrays. It also includes subtraction, and division with multiplication.

Syntax:

=SUMPRODUCT(array1, [array2], [array3], …)

Argument:

array1 – This is the first array or range where the first multiplication is performed. Then sum the multiplied returns.

array2, array3,… – These are optional arguments. We can add up to 2 to 255 arguments in the formula.

Let’s see the steps one by one.

Step 1:

• Now, go to Cell C5.
• Then type the following formula:

Step 2:

• Now, press OK.

7. Applying Excel VBA Macros to Check If a Cell is Empty

We can also use the VBA Macros code to check if the cell is empty.

Step 1:

• First, go to the Home tab.
• Select the Developer option from the main tab.
• From the commands select Marcos.
• We will get a Dialog box.

Step 2:

• Now, name the MACRO as Check_Empty_Cells.
• Then press Create.

Step 3:

• Now, type the below code in the VBA command module.

Sub Check_Empty_Cells()
Dim i As Long
Dim c As Long
Dim MRange As Range
Dim MCell As Range
Set MRange = Range("B5:B10")
For Each MCell In MRange
c = c + 1
If IsEmpty(MCell) Then
MCell.Interior.Color = RGB(255, 87, 87)
i = i + 1
End If
Next MCell
MsgBox "No. of Empty Cells are " & i & "."
End Sub

Step 4:

• Press F5 to run the code.

We can see that our data has 2 empty cells, and those cells are colored red.

Read More: Excel VBA: Check If Multiple Cells Are Empty

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Conclusion

In this article, we described 7 methods to check if the cell is empty in Excel. I hope this will satisfy your needs. Feel free to give your suggestions in the comment box. And, keep learning interesting Excel features.

Related Articles

• If Cell is Blank Then Show 0 in Excel
• How to Find & Count If a Cell Is Not Blank
• How to Calculate in Excel If Cells are Not Blank
• How to Return Value If Cell is Blank
• If a Cell Is Blank then Copy Another Cell in Excel
• Excel If Two Cells Are Blank Then Return Value