Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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.

check if a cell is empty in excel


Download Practice Workbook

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


7 Methods to Check if Cell is Empty in Excel

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.

check if a cell is empty in excel


1. 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:
=ISBLANK(B5)

ISBLANK Function to Check If a Cell is Empty in Excel

Step 2:

  •  Now, press Enter.

Step 3:

  •  Drag the Fill Handle icon to the last cell.

ISBLANK Function to Check If a Cell is Empty in Excel

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.

Note:

The ISBLANK function counts ="" cells as not blank and hence returns FALSE. Though ="" is an empty string and is blank in appearance.


2. IF Function to Check an Empty Cell in Excel

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:
=IF(B5="","Blank","Not Blank")

IF Function to Check an Empty Cell

Step 2:

  •  Then press Enter.

Step 3:

  •  Drag the Fill Handle icon to the last cell.

IF Function to Check an Empty Cell

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

Read More: How to Check If a Value is in List in Excel (10 Ways)


3. Combine IF with ISBLANK and Check If a Cell is Empty

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:
=IF(ISBLANK(B5),"Blank","Not Blank")

 

Step 2:

  • Press the Enter button.

check if a cell is empty in excel

Step 3:

  •  Drag the Fill Handle icon to the last cell.

check if a cell is empty in excel

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


4. Use 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.

Use the Find Command to Check If a Cell is Blank

Step 3:

  •  Now, press Find All.

Use the Find Command to Check If a Cell is Blank

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


5. Check If a Cell is Empty with Excel Conditional Formatting

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.

Check If a Cell is Empty with Excel Conditional Formatting

Step 3:

  • Now, choose Format only cells that contain.
  • Select Blanks.
  • Select the fill color from the Format option.

Check If a Cell is Empty with Excel Conditional Formatting

Step 4:

  • Now, press OK.

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


6. Check If Any Cell in a Range is Blank with Multiple 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:
=COUNTBLANK(B5:B10)

Check If Any Cell in a Range is Blank with Multiple Functions

Step 2:

  • Then press Enter.

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


6.2 COUNTIF Checks 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 compare with blank.
  • If found blanks then show TRUE otherwise FALSE. And the formula is
=COUNTIF(B5:B10,"")

Check If Any Cell in a Range is Blank with Multiple Functions

Step 2:

  • Now, press Enter.

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


6.3 SUMPRODUCT Checks Empty Cell in Excel

The SUMPRODUCT function 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 performs. 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:
=SUMPRODUCT(--(B5:B10=""))>0

check-if-a-cell-is-empty-in-excel

Step 2:

  • Now, press OK.

Check If Any Cell in a Range is Blank with Multiple Functions

Read More: How to Check If Value Exists in Range in Excel (8 Ways)


7. 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.

VBA & Macros to Check Empty Cell

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

VBA & Macros to Check Empty Cell

Step 4:

  • Press F5 to run the code.

VBA & Macros to Check Empty Cell

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


Conclusion

In this article, we described 7 methods to check if the cell is empty in Excel. I hope this will satisfy your needs. Please have a look at our website Exceldemy.com and give your suggestions in the comment box.


Related Articles

Alok

Alok

Hello, this is Alok. I am working as an Excel & VBA Content Developer at Exceldemy. I want to provide solutions to various Excel-based problems. I completed my study at East West University major in Telecommunications Engineering. I love traveling, reading books, playing cricket.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo