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

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:

• Press Enter.

• Drag the Fill Handle icon to the last cell.

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.

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:

• Press Enter.

• Drag the Fill Handle icon to the last 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:

• Press the Enter button.

• Drag the Fill Handle icon to the last cell.

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.

• Press Find All.

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.

• Choose Format only cells that contain.
• Select Blanks.
• Select the Fill Color from the Format option.

• 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:

• 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:

• 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:

• Press OK.

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.

• 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 LongDim c As LongDim MRange As RangeDim MCell As RangeSet MRange = Range("B5:B10")For Each MCell In MRangec = c + 1If IsEmpty(MCell) ThenMCell.Interior.Color = RGB(255, 87, 87)i = i + 1End IfNext MCellMsgBox "No. of Empty Cells are " & i & "."End Sub

• Press F5 to run the code.

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

• If the Cell is Blank, Then Show 0 in Excel
• 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

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