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 part, counting blank cells in Excel is a breeze. COUNTBLANK is a built-in office designed specifically to do one thing, and that is to count blank cells. Just 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 Information technology Piece of 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 ways that cells that contain numbers, text, or any sort of value will exist 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. In that location are some finer details to what qualifies as a blank prison 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 jail cell.
  • If a jail cell contains bare text such as " ", information technology will be counted as blank.
  • If a cell is fabricated to wait bare through custom formatting in Excel, it will not be counted as a bare cell.

With these in mind, permit'southward motion on to using COUNTBLANK in Excel.

How to Count Blank Cells in Excel Using the COUNTBLANK Function

At present that you know what COUNTBLANK is all about, it'southward fourth dimension to run into this function in action on an Excel spreadsheet. To provide a applied example, in this sample spreadsheet, we take 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 tin can achieve this goal by counting the number of contestants who didn't practise a second lap. Of course, since the data table in this example is a small one, you could too count them yourselves, only why practice that when you could utilize COUNTBLANK? With COUNTBLANK, we'll merely need to count the blank cells under the Second lap fourth dimension column.

  1. Select the cell where you want to brandish the output of your formula.
  2. In the formula bar, enter the formula below: 
                  =COUNTBLANK(C2:C11)
  3. In our example, nosotros used C2:C11, since nosotros only had to count the blank cells nether the 2nd lap time cavalcade. 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 expect through cells C2 and C11 (which are the lap times for the 2nd lap) and returns the number of empty cells. Easy does it.

Practise More than With Excel

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

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