Excel is a powerful tool to analyze and demonstrate data and is often used in statistics. One use of Excel in statistics is to count specific values, or in some scenarios, to count cells with no values.

Thanks to the COUNTBLANK function, counting blank cells in Excel is a breeze. COUNTBLANK is a built-in function designed specifically to do one thing, and that is to count blank cells. But how do you use COUNTBLANK in Excel to count empty cells? Read on to find out.

What Is the COUNTBLANK Function in Excel? How Does It Work?

COUNTBLANK is a built-in Excel function that takes in a range of cells and returns the count of cells that have no values. This means that cells that contain numbers, text, or any sort of value will be omitted from the count. The syntax for COUNTBLANK is as below: 

        =COUNTBLANK(range)

COUNTBLANK takes a single argument, and that is the range of cells it is supposed to look through. There are some finer details to what qualifies as a blank cell when you're using COUNTBLANK in Excel:

  • If a cell contains a formula that returns blank, then it is still counted as a blank cell.
  • If a cell contains blank text such as " ", it will be counted as blank.
  • If a cell is made to look blank through custom formatting in Excel, it will not be counted as a blank cell.

With these in mind, let's move on to using COUNTBLANK in Excel.

How to Count Blank Cells in Excel Using the COUNTBLANK Function

Now that you know what COUNTBLANK is all about, it's time to see this function in action on an Excel spreadsheet. To provide a practical example, in this sample spreadsheet, we have the list of some contestants in a charity race and the lap times they scored through two different laps. The goal is to get the number of contestants who only did one lap.

Sample spreadsheet

We can achieve this goal by counting the number of contestants who didn't do a second lap. Of course, since the data table in this example is a small one, you could also count them yourselves, but why do that when you could use COUNTBLANK? With COUNTBLANK, we'll only need to count the blank cells under the Second lap time column.

  1. Select the cell where you want to display the output of your formula.
  2. In the formula bar, enter the formula below: 
            =COUNTBLANK(C2:C11)
  3. In our example, we used C2:C11, since we only had to count the blank cells under the Second lap time column. You can enter cell references in the formula based on your range.
  4. Press Enter.
COUNTBLANK function results in Excel

Excel will now return a value which will be the number of contestants who didn't partake in the second lap. The formula we used summons the COUNTBLANK function to look through cells C2 and C11 (which are the lap times for the second lap) and returns the number of empty cells. Easy does it.

Do More With Excel

Even though Excel comes packed with sophisticated functions that take in numerous arguments, sometimes all you need is a simple function like COUNTBLANK to get what you want.

COUNTBLANK is a built-in Excel function that counts and returns the number of blank cells in a range. Although COUNTBLANK is a simple function and takes a single argument, it can be used in combination with other functions to accomplish complicated tasks. That statement is true about almost every function in Excel.