Finds the number of empty cells in a given range of cells.

Author: Christy Grimste
Christy Grimste
Christy Grimste
Real Estate | Investment Property Sales

Christy currently works as a senior associate for EdR Trust, a publicly traded multi-family REIT. Prior to joining EdR Trust, Christy works for CBRE in investment property sales. Before completing her MBA and breaking into finance, Christy founded and education startup in which she actively pursued for seven years and works as an internal auditor for the U.S. Department of State and CIA.

Christy has a Bachelor of Arts from the University of Maryland and a Master of Business Administrations from the University of London.

Reviewed By: Rohan Arora
Rohan Arora
Rohan Arora
Investment Banking | Private Equity

Mr. Arora is an experienced private equity investment professional, with experience working across multiple markets. Rohan has a focus in particular on consumer and business services transactions and operational growth. Rohan has also worked at Evercore, where he also spent time in private equity advisory.

Rohan holds a BA (Hons., Scholar) in Economics and Management from Oxford University.

Last Updated:January 13, 2024

What is the COUNTBLANK Function?

The COUNTBLANK function in Excel helps to find the number of empty cells in a given range of cells. The function will return an integer value acting as a count of all the empty cells in the referenced range of cells.

Financial Analysts work extensively on a large amount of data daily, which involves reconciling, reporting, analyzing, and other number-crunching tasks in Excel. 

It is pretty normal that you might miss out on inserting values in specific cells in the spreadsheet. In such cases, COUNTBLANK works as a scan that lets you know if there are still any blank cells that require the input of values.

The COUNTBLANK is categorized as a statistical function that returns the count for the blank cells in the user-specified range of cells.

The function helps identify how many blank cells exist in a given range. The range could be a single column, a single row, or the entire spreadsheet. For example, if range A1:A3 are empty cells, the function would return the result for COUNTBLANK as 3.

The function will ignore cells containing text, numerical values, errors, space, date, time, and formulas. However, if the result obtained from a procedure equals an empty cell, the cell will be counted as a blank cell.

COUNTBLANK Function Formula

The syntax for the COUNTBLANK function is



range = reference to the cell or range of cells for which we need to find the count for blank cells.

The function takes in only a single argument for range. You cannot reference multiple separate rows or columns. For example, A1:A3 and C1:C3 cannot be your range for the function. 

However, you can use A1:C3 as the range to count blanks, which will inevitably also include B1:B3.

How to use the COUNTBLANK Function in Excel?

The function's name says a lot about what it will do. To understand better, let us consider an example:

Example 1

Suppose that you have a column consisting of the first ten numbers. Some are represented as text, while others are described as numbers.

Numbers Graph

To find the number of blank cells in the range, we will use the formula =COUNTBLANK(C3:C12), which will give us the count as 2.

Chart 2

If you see closely, the cells C7 and C10 are both empty, so we get the result as 2. It doesn't matter to Excel whether the cell contains a text value or a number. It solely focuses on empty cells.

Example 2

In the previous example, the function completely ignored the cells containing text and numerical values in the result. Next, we will see other formats that may or may not be affected by the function. For example, suppose that you have the data illustrated below:

Chart type value

If we use the formula


for the values in column C, we will get a result of 2.

Chart type value 2

Well, getting the result for cell C11 is entirely acceptable since it is blank, right? But what the heck is with the formula in cell C12? 

We can infer another thing from this little experiment. If the formula is set up to return a blank cell, the COUNTBLANK function will include it in its final result.

'Formula A' does not return the result as an empty cell; hence, it is ignored for counting blanks. 

“Not” so empty cells

When you are working on big data, there could be instances when the cells 'seem' to look empty but aren't. These are the results of automated data processing, which may or may not give rise to invisible characters in your spreadsheet.

If you use the COUNTBLANK function on such cells, you will get a misleading result since Excel considers these cells containing invisible characters to be 'non-empty.'

To avoid any errors while working on such automatically generated files or web files

  • Press the keyboard shortcut of F5 to open Go To dialog box and click on ‘Special’
  • Select the radio box for 'Blanks'
  • Click on ‘Ok’

This method will highlight only the purely blank cells; not even a formula that gives empty cells would be included in the final result.

COUNTBLANK Function Notes

Some important things to remember are:

  • The COUNTBLANK function will only process empty cells in the result while ignoring all the cells that contain text, numerical values, logical formulas, errors, etc.
  • The only exception is the formulas that return empty cells as a result and will be counted as blank cells.
  • Cells that contain a zero or formulas returning zero, as a result, will not be counted as blank.
  • If you need to count cells containing all the types of values, use the COUNTA function.
  • The COUNT function can be used when you want to count the cells containing only numerical values.

Researched and authored by Akash Bagul | LinkedIn

Reviewed and edited by James Fazeli-Sinaki | LinkedIn

Free Resources

To continue learning and advancing your career, check out these additional helpful WSO resources: