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

 

Method 1 – Using the ISBLANK Function

Syntax:

ISBLANK(value)

Argument:

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

Steps:

  • Go to Cell C5.
  • Enter the ISBLANK function.
  • Select B5 as the argument. The formula will be:
=ISBLANK(B5)

ISBLANK Function to Check If a Cell is Empty in Excel

  • Press Enter.

  •  Drag the Fill Handle icon to the last cell.

ISBLANK Function to Check If a Cell is Empty in Excel

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.

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


Method 2 – Applying the Excel IF Function

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 you want to return if the result of logical_test is FALSE.

Steps:

  • Go to Cell C5.
  • Enter the following formula:
=IF(B5="","Blank","Not Blank")

IF Function to Check an Empty Cell

  • Press Enter.

  •  Drag the Fill Handle icon to the last cell.

IF Function to Check an Empty Cell

The result should look like the screenshot above.


Method 3 – Combining IF and ISBLANK Functions 

Steps:

  • Go to Cell C5.
  • Enter the following formula:
=IF(ISBLANK(B5),"Blank","Not Blank")

 

  • Press the Enter button.

check if a cell is empty in excel

  •  Drag the Fill Handle icon to the last cell.

check if a cell is empty in excel


Method 4 – Using the Find Command 

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.

Steps:

  •  Select the range of empty cells to check.

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

Use the Find Command to Check If a Cell is Blank

  • Press Find All.

Use the Find Command to Check If a Cell is Blank

You have successfully found the blank cells B7 and B9.


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

Steps:

  • Select the range of cells B5:B10.

  • Go to the Home tab.
  • From the Conditional Formatting command, select the Highlight Cells Rules.
  • Go to More Rules.

Check If a Cell is Empty with Excel Conditional Formatting

  • 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

  • Press OK.

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


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

6.1 Using the COUNTBLANK Function

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.

Steps:

  • Go to Cell C5 and write the COUNTBLANK function.
  • Enter the following formula:
=COUNTBLANK(B5:B10)

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

  • Press Enter.

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


6.2 Using the COUNTIF Function 

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 in the formula. It will check from the given range.

Use COUNTIFS if we want to use multiple criteria.

Steps:

  • Enter the COUNTIF function.
  • The range is B5:B10 and compared with blank.
  • If blanks are found, then show TRUE; otherwise, FALSE. The formula is:
=COUNTIF(B5:B10,"")

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

  • Press Enter.

Only one cell is empty, and the result is showing.


6.3 Using SUMPRODUCT 

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.

Steps:

  • Go to Cell C5.
  • Enter the following formula:
=SUMPRODUCT(--(B5:B10=""))>0

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

  • Press OK.

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


Method 7 – Applying Excel VBA Macros 

Steps:

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

VBA & Macros to Check Empty Cell

  • Name the MACRO as Check_Empty_Cells.
  • Press Create.

  • Enter the following 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

  • Press F5 to run the code.

VBA & Macros to Check Empty Cell

The data has 2 empty cells, and those cells are colored red.

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


Download the Practice Workbook

Download this workbook to practice.


Related Articles


<< Go Back to If Cell is Blank Then | Blank Cells | Excel Cells | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Alok Paul
Alok Paul

Alok Paul has completed his B.Sc. in Electronics and Telecommunication Engineering from East West University. He has been working on the ExcelDemy project for more than 2 years. He has written 220+ articles and replied to numerous comments. He is experienced in Microsoft Office, especially in Excel. He also led some teams on Excel and VBA content development. He has a keen interest in Advanced Excel, Data analysis, Excel Pivot Table, Charts, and Dashboard. He loves to research... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo