A.N.M. Mohaimen Shanto
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:
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)
2. Secondly, press Enter.
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,"")
2. Then, press Enter.
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=""))
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.
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.
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.
4. After that, mark the Match entire cell contents checkbox.
5. Now, in the drop-down menu Look in, select Values.
6. Then, click on Find All.
7. After that, you will see this box.
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.
2. Then, go to the Home tab. Click on Editing > Find & Select > Go To Special.
3. After that, you will find Go To Special dialog box. Select the radio button Blanks. Click on OK.
4. Finally, it will highlight all the blank cells in our dataset.
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.
