COUNTBLANK Function in Excel

Home >> Excel Tutorials from Compute Expert >> Excel Formulas List >> COUNTBLANK Function in Excel

In this tutorial, you will learn the usage of the COUNTBLANK excel formula. COUNTBLANK function in excel is useful to count how many blank cells that you have in a range.

Why do We Need to Learn COUNTBLANK Excel Formula?

When using spreadsheets, sometimes we want to check whether there is a blank cell in our range and if there is, then how many are the blank cells. The checking process can be needed for some reasons such as making sure there is no wrong or error processing result if we use a formula to process every data entry in the range or check whether there is still incomplete data if we get the data from external sources.

As an example of the need in our work, imagine we are doing a lookup process for a data variable of our company’s products to complete the analysis that we do by using the reference of the company’s products detail information table in the spreadsheet. If we want to use formulas like INDEX MATCH or VLOOKUP to help speeding up the process, then we must make sure that all the data which is made as the lookup value for the search process is there for each product and all the data that we want to find are there in the reference table. However, there might be some blank cells in our database or reference that can be undetected considering the very high amount of data entries that we must see in the database or reference. This is, of course, dangerous because it can give a negative impact on the result from our data analysis if the data of even just one product cannot be found so it will produce an error from the usage result of our formula.

If we need to see one by one manually to make sure there is no empty cell, then it can take a long time to do it, especially if there are many data entries to check.

Because of that, to help you in faster empty cells checking in a range on the spreadsheet significantly, you can use COUNTBLANK function in excel. COUNTBLANK excel formula can help you to count how many blank cells are there in your range. It is very useful to be learned if you often deal with things related to the counting of empty cells in your data processing as in the above case.

What is COUNTBLANK Function in Excel?

COUNTBLANK function in excel is a function with the usage objective to count the number of empty cells in a range. COUNTBLANK excel formula is one of the cell data counting formulas besides some other formulas in the spreadsheet with each of them has its own function uniqueness.

Generally, the explanation about the input of COUNTBLANK excel formula can be given as follows:


range = range to check how many blank cells are in it

How to Use the COUNTBLANK Excel Formula?

The following will explain how to write the COUNTBLANK function in excel. This function only needs one input which is the range where you want to count how many empty cells are inside of it.

Using COUNTBLANK Function in Excel

  1. Type equal sign ( = ) in the cell where you want to put the result in

    COUNTBLANK Function in Excel - Screenshot of Step 1

  2. Type COUNTBLANK (can be with large and small letters) and open bracket sign after =

    COUNTBLANK Function in Excel - Screenshot of Step 2

  3. Drag cursor from the most top left to the most bottom right on the cells where you want to calculate the number of empty cells are. You can also type comma sign ( , ) and add cell range in other places until all of them are in the formula

    COUNTBLANK Function in Excel - Screenshot of Step 3

  4. Type close bracket sign after all the ranges that you want to be processed have already been inputted

    COUNTBLANK Function in Excel - Screenshot of Step 4

  5. Press Enter
  6. The process is done!

    COUNTBLANK Function in Excel - Screenshot of Step 6


After you have understood how to write COUNTBLANK excel formula from the tutorial above, now is the time you do an exercise for the formula usage so you can understand more how to use it!

Download the exercise file and answer all of the questions below. Download the answer key file if you have done the exercise and you want to check your answers or if you are confused about how to do the questions!

Link of the exercise file:
Download here


Use COUNTBLANK function in excel to answer each question below!
  1. How many blanks are there in the product name column?
  2. How many blanks are there in the stock quantities and today sales columns?
  3. How many blanks are there in the product name, stock quantities, and today sales column?

Link of the answer key file:
Download here

Additional Note

COUNTBLANK excel formula will also count the cells filled with a formula that produces empty data. However, it will not count if the formula produces an error.

Want to Learn More About Excel?

Get updated excel info from Compute Expert by registering your email. It's free!

Want to Learn More About Excel?

Get updated excel info from Compute Expert by registering your email. It's free!

Follow our tutorial content also on

Hand-Picked CE Tutorials

Excel Calculation

How to Sum in Excel

Subtraction in Excel

Multiplication in Excel

Division Excel Calculation

Average Excel Calculation

Excel Formula

VLOOKUP Excel Formula

Excel IF Function

SUM Formula in Excel

COUNTIF Formula in Excel

COUNT Function in Excel

Excel Tips and Trick

How to Print in Excel

Convert Number to Text Excel

Excel Worksheet Definition

Excel Range Definition

How to Add Columns in Excel

Excel Consultation

Contact Us

Privacy Policy

Affiliate Disclosure

Terms & Condition

© 2021 Compute Expert