How to Count Empty Cells in Excel (4 Suitable Ways)

We will use the following dataset to explain how to count empty cells in Excel.

Method 1 – Count Empty Cells by Inserting Functions

Case 1 – Using COUNTBLANK to Count Empty Cells

• The formula for D5:

• Drag the plus (+) sign at the bottom-right of the cell (B5).

Formula Description:

The formula syntax:

The range indicates the dataset from where you want to count the empty cells.

You can also use the nested IF and COUNTBLANK formulas to find whether the row is fully blank or not.

The formula will be:

Formula Description:

logical_test takes the COUNTBLANK function and checks whether it is equal to zero or not.

value_if_true takes a text to display if the test is true.

value_if_false takes a text to display if the test is false.

Case 2 – Using COUNTIF or COUNTIFS to Count Empty Cells

The formula in D5 will be:

OR

Drag the fill handle down to find the count for the rest of the rows in the dataset.

Formula Explanation:

Both of the formulas take a range of the dataset and criteria based on which result will be displayed.

Case 3 – Using SUM with ROWS and COLUMNS to Count Empty Rows

• The formula is:

• The result shows there are two blank rows in the dataset.

Formula Explanation:

The formula takes the numbers as arguments and gives the sum as a result.

It takes a number of arrays of the dataset.

The formula with the ROW function takes the reference of rows in the dataset.

This makes a reference from text.

The formula with the COLUMNS function takes an array of the dataset.

The double minus (–) sign is used for a forced conversion of the Boolean value TRUE or FALSE to numerical values 1 or 0.

Case 4 – Inserting SUMPRODUCT to Count All Empty Cells

• The formula for the given dataset will be:

• The result shows there are 5 empty cells in the given dataset.

Formula Explanation:

The function is used to take multiple arrays and provide the sum of the arrays.

We have only one set of arrays and the formula takes the range of the dataset only if it is equal to blank.

The double minus (–) converts it into numerical value to get the result.

Method 2 – Count Empty Cells with the Go To Special Command

• Select the dataset.
• Select Go To Special from Find & Select. You will find Find & Select from the Editing options present in the Home tab.

You can also press F5 on your keyboard to find Special from there.

• A new box will appear. Select Blanks and click OK.

The blank cells are selected automatically.

• To highlight the blank cells from the Home tab, select Fill Color and choose the color you like from the drop-down menu.

The color you chose will fill the selected blank cells. We chose blue for now. The result will look like this.

Method 3 – Count Empty Cells Using the Find & Replace Command

• Select the dataset.
• Select Find from Find & Select.

• A new box will appear. Keep the box Find what: blank.
• Click on Options >>.

• Tick the option Match entire cell contents.
• From Within: drop-down options, select Sheet.
• In Search: drop-down options select By Columns.
• From Look in drop-down options, select Values or Formulas.

• The Find and Replace box should look like the below picture.
• Click Find All and the result will be shown at the bottom of the box.

Method 4 – Count Empty Cells Using Excel VBA

• Select the dataset.

• Press Alt + F11. The VBA window will open.

• Select the sheet where your selected dataset is present.
• From Insert, choose Module.

• The General window will open.

• Insert the code given below.

Code:

Sub CountBlanks()
'Updateby20140310
Dim rng As Range
Dim WorkRng As Range
Dim total As Long
On Error Resume Next
xTitleId = "Number of Blank Cells"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
For Each rng In WorkRng
    If IsEmpty(rng.Value) Then
        total = total + 1
    End If
Next
MsgBox "There are " & total & " blank cells in this range."
End Sub

• Press F5 from the keyboard to run the code.
• This will open a box named “Number of Blank Cells”.
• Check or insert the Range of your dataset and click OK.

• A new box will show the result.

Download the Practice Workbook

<< Go Back to Count Cells | Formula List | Learn Excel