When you work in Excel, you may find that certain datasets contain blank values. When you’re processing data, especially in large volumes, it’s useful to quickly count the number of blank cells present within a given range. But with large volumes of data, it can take forever to count these manually. Fortunately, Excel has a formula-based option ready to help. Let’s learn how to use COUNTBLANK in Microsoft Excel.
How to Use COUNTBLANK in Excel
Imagine that you have a range of data in Excel, spanning cells A1:C10. At a glance, you can see that some cells within the range are blank, containing no data. Again, for small datasets, this is easy to count manually. But imagine if the range had thousands of cells inside. It pays to have an automated way to perform the count. Plus, using a formula-driven option helps boost accuracy and eliminate mistakes in the count.
This is where Excel’s COUNTBLANK function comes in. COUNTBLANK does exactly what the name suggests. It counts the total number of blanks within a selected range.
To use COUNTBLANK in Microsoft Excel, begin by clicking into an empty cell in your workbook. Then, type an = sign. This tells Excel that you’ll be inputting a formula in the cell where you’re typing.
Then, insert the COUNTBLANK function. Now, your formula reads:
=COUNTBLANK(
Then, click and drag your cursor to select the range from which you want the total number of blanks. Here, the range is A1:C10. Close the parentheses, completing your formula as:
=COUNTBLANK(A1:C10)
Finally, hit Enter on your keyboard. Excel will return the total count of the number of blanks in the selected range. The count in this example is 5.
As you can see, COUNTBLANK is a quick, easy-to-use Excel function that instantly delivers the total number of blanks in a range of data that you have selected.