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

Many times, the database might contain empty cells. One would like to count the empty cells. Microsoft Excel has some amazing formulas and tools to do it easily for you. The article will include four different ways to count empty cells in Excel.


Download Practice Workbook

You can download the practice workbook from here.


4 Fruitful Ways to Count Empty Cells in Excel

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

Excel count empty cells: Sample dataset

The dataset contains the name of tech products and the number of sales that occurred in a technology-based company. You can notice the dataset has some empty cells. We will be counting the empty cells using formulas and tools available in Excel.


1. Count Empty Cells by Inserting Excel Formulas with COUNTIF, COUNTBLANK, SUMPRODUCT, etc. Functions

Excel has some useful formulas to count the blank cells in a dataset. Functions like COUNTBLANK, COUNTIF, SUM, SUMPRODUCT, and so forth are used to form such formulas. Let us see the formulas one by one.

i. Inserting COUNTBLANK to Count Empty Cells

The COUNTBLANK function itself explains what it can do. It can count blanks or empty cells in a row for a given range of data.

The formula for the given dataset:

=COUNTBLANK(B5:C5)

Result of inserting COUNTBLANK formula

Using the Fill handle we can find out the result for the rest of the rows in the dataset.

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

See the result in the picture below.

Inserting COUNTBLANK to Count Empty Cells

Formula Description:

The formula syntax:

=COUNTBLANK(range)

Here, 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 the row is fully blank or not.

The formula will be:

=IF(COUNTBLANK(B5:C5)=0,"Not Blank","Blank")

Follow the picture below to know how to do this.

Use of nested IF and COUNTBLANK formula

Formula Description:

The syntax of the nested formula:

=IF(logical_test,[value_if_true],[value_if_false])

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


ii. Inserting COUNTIF or COUNTIFS to Count Empty Cells

You can also use the COUNTIF or COUNTIFS function. Both will give the same result.

The formula will be:

=COUNTIF(B5:C5,"")

or,

=COUNTIFS(B5:C5,"")

Then, drag the plus (+) sign at the right bottom of the cell to find the count for the rest of the rows in the dataset.

Comparing result for COUNTIF and COUNTIFS

The first Blank Cells column in Column D uses the COUNTIF function while the second one in Column E uses the COUNTIFS function.

You can notice that the result for both formulas is the same.

Formula Explanation:

The syntax of the formulas:

=COUNTIF(range, criteria)
=COUNTIFS(criteria_range1, criteria1, [criteria_range2], [criteria2]..)

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

The COUNTIFS function can take multiple criteria and ranges while the COUNTIFS function takes only one range and criteria.


iii. Inserting SUM with ROWS and COLUMNS to Count Empty Cells

Moreover, there is another nested formula using SUM, ROWS, and COLUMNS functions, etc. to count empty rows in a dataset.

The formula is:

=SUM(--MMULT(--(B5:C11<>""),ROW(INDIRECT("B1:B"&COLUMNS(B5:C11))))=0)

Inserting SUM with ROWS and COLUMNS to Count Empty Cells

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

Note: The formula counts blank if the whole row is blank. That is why it has ignored cell B8.

Formula Explanation:

The individual syntax of the nested formula along with the explanations:

=SUM(number1, [number2],..)

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

=MMULT(array1,array2)

Here, it takes a number of arrays of the dataset.

=ROW([reference])

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

=INDIRECT(ref_text,[a1])

It takes reference text.

=COLUMNS(array)

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

Here, the double minus sign (–) is used to do the forced conversion of the Boolean value TRUE or FALSE to numerical values 1 or 0.


iv. Inserting SUMPRODUCT to Count Empty Cells

Furthermore, SUMPRODUCT is also a useful formula to count empty cells.

The formula for the given dataset will be:

=SUMPRODUCT(--B5:C11="")

Inserting SUMPRODUCT to Count Empty Cells

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

Note: It counts for empty cells and not rows, unlike method c.

Formula Explanation:

The syntax of the formula:

=SUMPRODUCT(array1, [array2],..)

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

In this case, we have only one set of arrays and the formula takes the range of the dataset only if it is equal to blank.

Then, using the double minus sign (–) we converted it into numerical value to get the result.

Read more: How to Count Blank Cells in Excel with Condition 


2. Count Empty Cells Using the Go To Special Command

On the other side, we can use Go To Special Command from the Home Tab to find empty cells.

Follow the steps to know how to count empty cells using Go To Special:

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

Count Empty Cells Using the Go To Special Command

  • A new box will appear. From the box, select Blanks and click OK.

Selecting blank from Go To Special

You will notice the blank cells are selected automatically.

Selected blanks in the dataset

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

Choosing color to highlight the selected cells

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

Result of using Go To Special

Note: This process is useful for small datasets. You can highlight the blank cells and count by yourself.

Read more: How to Count Filled Cells in Excel 


3. Count Empty Cells Using the Find & Replace Command

Besides, you can use another useful Excel tool to count the empty cells. This is called Find and Replace.

You need to follow the steps to use it.

  • Select the dataset.
  • Select Find from Find & Select. Follow the picture if you cannot find it.

Choosing Find from home tab

  • A new box will appear. Keep the place blank in Find what: option.
  • Then, click on Options >>.

Count Empty Cells Using the Find & Replace Command

  • New options will appear. From there,
    • 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. (We will select values since we do not have any formulas in our dataset). Anyway, both will work the same.

Selecting required options and clicking Find All

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

Result showing at the bottom

Read more: Count Cells that Contain Specific Text in Excel


4. Count Empty Cells Using Excel VBA Macros

Lastly, the VBA Macros can be used to count empty cells.

For this you must follow the steps:

  • Select the dataset.

Selecting dataset to apply VBA MACRO

  • Press ALT+F11 from the keyboard. The VBA window will open.

Opening the VBA window

  • Select the sheet where your selected dataset is present.

From Insert choose Module.

Selecting Module to open General window

  • The General window will open.

 The general window where code has to be written

  • Inside the General window write 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

Count Empty Cells Using Excel VBA Macros

  • Press F5 from the keyboard to run the code.
  • It will open a box named “Number of Blank Cells”.
  • Check the Range of your dataset and if it is alright then click OK.

Checking range of dataset

  • A new box will come up and it will show the result.

Result of using VBA MACRO


Things to Remember

  • Do not forget to select the range of data before starting to apply methods using Excel tools.
  • For formulas, write the formulas maintaining the syntax of the formula, and row and column of your datasets.

Conclusion

The article explains four fruitful ways to count empty cells in Excel using various Excel formulas and tools. The formulas include functions like COUNTBLANK, COUNTIF, SUMPRODUCT, ROWS, and so forth. The Excel tools used in the methods are Go To Special, Find & Replace commands from the Home tab, and the VBA Macros to execute codes there to count empty cells in Excel. You can check the related topic in the Related Reading section. I hope this article was helpful for you. If you have any further query you can ask in the comment section. Also don’t forget to visit our site for more informative articles.


Related Readings

Syeda Fahima Nazreen

Syeda Fahima Nazreen

Hello People! This is Syeda Fahima Nazreen. I have completed my Bachelors in Science in Electrical and Electronic Engineering. I love to do research and work anything related to technology which includes research and development. I feel great to share my knowledge with you people and your thoughts and opinions about my writing is highly appreciated by me.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo