Count Blank Cells in Excel

Add to Favorites

How to count blank cells, including formulas that output a blank.

Sections:

Count Blank Cells

Notes

Count Blank Cells

A blank cell is an empty cell and also a cell that contains a formula that outputs a blank. Note that a blank looking cell, one that contains only a space in it, will not be counted as blank.

=COUNTBLANK(A1:A6)

3c18d7331b1479610ba70082ac9d1016.png

Result:

334afb1c9086168875423acd4a6e249f.png

Cells with Formulas

Note that cell A4 does have a formula in it; however, the formula outputs nothing, just a blank. As such, that cell is counted as a blank.

7da4e7a543370d6f597d3fed4450a455.png

As you can see, 1 is not greater than 2 so "" is returned and output, which is a blank.

If you change the formula to result in TRUE or just put something between "" the blank count would be 1 because this cell would no longer be considered blank.

Notes

This is a simple function; just remember that it counts formulas that output nothing as blanks.

Download the spreadsheet for this tutorial to get this example in Excel.


Excel Function: COUNTA(), COUNTBLANK()
Downloadable Files: Excel File

Similar Content on TeachExcel
Count The Number of Words in a Cell or Range of Cells in Excel - With User-Specified Delimiter / Separator - UDF
Macro: UDF to count the number of words in a cell or range with a user-specified delimiter. ...
Count The Number of Words in a Cell or Range of Cells in Excel - UDF
Macro: Count words in cells with this user defined function (UDF). This UDF allows you to count t...
Delete Blank Rows in Excel
Macro: This is a macro which will delete blank rows in excel. This version will delete an entire ...
Center Titles Across Multiple Cells in Excel
Tutorial: How to center a title across multiple cells in Excel in order to make good looking titles...
How to Arrange Data within Cells in Excel
Tutorial: In this tutorial I am going to look at cell alignment / arrangement. These features allow ...
Select Ranges of Cells in Excel using Macros and VBA
Tutorial: This Excel VBA tutorial focuses specifically on selecting ranges of cells in Excel.  This ...