How to Count Blank Cells in Excel (5 Ways)

While working with a large number of data, you may find numerous empty cells. This is a common scenario. Sometimes, blank cells create some misinformation and misinterpretation of a dataset. In order to solve this, we need to count blank cells and perform some action regarding this. In this tutorial, we will show how to count blank cells in Excel.


Download Practice Workbook

You can download this practice workbook.


5 Easy Ways to Count Blank Cells in Excel

In the next sections, we are going to show 5 fruitful ways to count empty cells. Of them, three ways have used Excel functions, and in two, Excel commands.

Simply stated, you can use any of them in your workbook to count blank cells easily.

To demonstrate these methods, we are using this dataset:

dataset to count blank cells in excel

Here, we have a dataset consisting of some salespersons’ names, their birth dates, and their sales amounts. Also, there are some blank cells and our goal is to count them using these 5 methods.

1. Using the COUNTBLANK Function to Count Blank Cells in Excel

Undoubtedly, the COUNTBLANK function is the most used function to count blank cells in Excel. This is simple to use and can save a lot of time. Besides, you can easily implement this in any kind of dataset.

The Basic Syntax of COUNTBLANK:

=COUNTBLANK(range)

Steps: 

1. First, type the following formula in Cell F5:

=COUNTBLANK(B5:D10)

countblank formula to count blank cells

2. Secondly, press Enter.

result after typing countblank function

As you can see, our formula has successfully counted all the blank cells in the dataset.

Things to Remember:

1. This function does not count zero(0) as a blank cell.

2. This counts Empty String (“”) as a blank cell

Read more: Count Cells That Are Not Blank in Excel

2. The COUNTIF Function to Count Empty Cells

The COUNTIF function count cells based on criteria. It basically returns the count of cells that meets the condition or criteria. In this case, we can also use it to find blank cells.

The Basic Syntax of COUNTIF Function:

=COUNTIF(range, criteria)

Basically, this formula will return the count of empty cells of all value types. It will count the cells that have no data in them. So, if your goal is to count all empty cells in a given range then you can definitely use this formula.

Steps:

1. Firstly, type the following formula in Cell F5:

=COUNTIF(B5:D10,"")

countif formula to count blank cells in excel

2. Then, press Enter.

result of countif function in excel

As you can see, we have successfully counted all the empty cells in our given dataset.

Read more: How to Count Filled Cells in Excel

3. Using the SUMPRODUCT Function to Count Blank Cells

Now, you may not see this method used too often. But this method also works efficiently by counting blank cells in Excel. Similarly, this method will come in handy in a lot of situations.

The Basic Syntax of the SUMPRODUCT function:

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

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

Steps:

1.  First, type the following formula in Cell F5:

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

sumproduct formula to count empty cells

At this time, we have only one set of arrays and that is our range of cells. Here, the double minus sign (“–“) gives us the result in numerical form.

2. Next, press Enter.

result of sumproduct function

Finally, we are successful to count all the empty cells in our dataset.

Read more: Count Cells that Contain Specific Text in Excel

4. Excel’s Find and Replace Command to Count Empty Cells

Now, you can use Excel’s standard Find and Replace command to count empty cells in your worksheet. It will give you the cell’s address too so that you can click on this and go to the blank cell.

Steps:

1. First, select your range of cells B5:D10 from where you want to count blank cells.

select range of cells

2. Then, press Ctrl+F on your keyboard. You will find the Find and Replace dialog box.

3. Next, keep the Find what box empty. Then, click on Options.

Find & replace dialog box

 

4. After that, mark the Match entire cell contents checkbox.

FInd and replace dialog box tick mark

5. Now, in the drop-down menu Look in, select Values.

select values in find and replace dialog box

6. Then, click on Find All.

click find all option

7. After that, you will see this box.

find and replace to count blank cells

As you can see, at the bottom of the box, it is showing us all the blank cells it has found.

5. Count Blank Cells Using Go To Special Command

Unlike the other methods, we don’t use this method very often. Up to a point, it will give you results but only for a small dataset. It just highlights the cells that are blank. So, you have to count the cells manually in this case.

Steps:

1. Firstly, select the range of cells B5:D10.

select the range of cells

2. Then, go to the Home tab. Click on Editing > Find & Select > Go To Special.

go to special option in the home tab

3. After that, you will find Go To Special dialog box. Select the radio button Blanks. Click on OK.

Go to special command box

4. Finally, it will highlight all the blank cells in our dataset.

highlighted blank cells by go to special option

Now, you can format these cells with any color. You have to count these cells manually. For this reason, you can use it only for a small dataset.


Conclusion

To sum up, I hope these methods will help you efficiently to count blank cells in Excel. I think you should learn all of these methods to keep enriching your Excel knowledge. Download the practice workbook and try these yourself. Also, don’t forget to check our website Exceldemy.com for various Excel-related articles. Most importantly, write down your comment for any kind of confusion or your feedback. We will be grateful.


Related Articles

Shanto

Shanto

Hello! I am Shanto. An Excel & VBA Content Developer. My goal is to provide our readers with great tutorials on various Excel-related problems. I hope our easy but effective tutorials will enrich your knowledge. I have completed my BSc in Computer Science & Engineering from Daffodil International University. Working with data was always my passion. Love to work with data, analyze those, and find patterns. Also, love to research. Always look for challenges to keep me growing.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo