Count Blank Cells in Excel

Add to Favorites
Author:

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

Question? Ask it in our Excel Forum


Our Excel Courses

  • Skill Level: All Levels
  • |
  • 5 hours+

How to make a fully featured professional form in Excel that is unbreakable. This includes how to use the form to store, view, edit, and delete data from a data storage worksheet.

  • Skill Level: All Levels
  • |
  • 2 hours

Send Emails from Excel using VBA and Macros. This course starts from the Basics and builds up to more advanced examples with attaching workbooks, worksheets, PDF's, automatically sending emails, including a signature, error handling, increasing speed, and more.

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 ...
How to Arrange Data within Cells in Excel
Tutorial: In this tutorial I am going to look at cell alignment / arrangement. These features allow ...
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...
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...
Tutorial Details
Excel Function: COUNTA(), COUNTBLANK()
Downloadable Files: Excel File
Similar Content
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 ...
Excel Forum