< Go Back

How to Count Blank or Empty Cells in Excel

Excel provides numerous methods to count blank or empty cells in selected range of cells. In this article you need to learn how to apply these methods to count blank or empty cells in Excel. First you need to understand the difference between Blank and empty cells.

Blank cells are those cells which do not contain any number or text values, space(s), logical value or formula errors. If a cell contains a formula that returns empty text (“”) is also considered a Blank cell. Empty cells are those that are empty by all means, without having a value, space(s), error(s) and formula that returns empty text (“”).   

There are multiple features, functions and formulas to count Blank or empty cells in Excel that will be discussed in this article.

Using Go To Special functionality

It’s Excel built in functionality. Using this functionality you can highlight and manually count blank and empty cells as it only highlights blank or empty cells in selected range of data. To do this you need to select the range of cells where you want to count blank cells. On Home tab, go to Editing section and click on Find & Select drop down list and click on Go To Special….

Go To Special dialog box opens, containing multiple options and feature. You need to select Blanks radio button and press OK.

When you select OK, this functionality highlights all blank and empty cells in your selected range, as shown below;

Using Find and Replace functionality

It’s also Excel’s built in functionality to count blank or empty cells. Find and Replace functionality can count total number of blank or empty cells in your selected range of cells. To do this you need to select cells range where you need to count blank or empty cells and press Ctrl+F short keys on your keyboard. A Find and Replace dialog populates.

Keep the Find what field empty, press Options >> button, select Match entire cell contents checkbox and select one of options Formulas or Values from Look in: drop down list as per requirement as described below;

  1. If you select Values option then it will count both Blank and Empty cells in selected range.
  2. If you select Formulas option then it will count only Empty cells.

When you press Find All button, the dialog gives you count of all Blank cells along with the details of cell references of blank cells in range. It’s very handy and user friendly method to count these using this method.  

 

Using Formulas to count Blank or Empty cells

There are various Excel formulas or functions to count blank or empty cells in selected cells’ range, such as COUNTBLANK, COUNTIF, and SUMPRODUCT. Here we will discuss these formulas one by one.

  • COUNTBLANK formula

COUNTBLANK function is designed in Excel to count blank cells. You can easily count blank cells by entering reference of cells range in this function, such as;

=COUNTBLANK(A1:A8)

 

  • COUNTIF formula

Excel COUNTIF function is designed to count cells that meet supplied criteria or condition. If you need to count blank cells in given range then you need to supply empty text (“”) in criteria argument of COUNTIF function, such as;

=COUNTIF(A1:A8,"")

 

  • SUMPRODUCT formula

Excel SUMPRODUCT function can also be used to sum the count of cells that meet criteria of being blank or empty, such as;

=SUMPRODUCT(--(A1:A8=""))

When you apply this formula in this manner it gives you total count of blank cells. Double Hyphen symbol (–) converts the cells as 1s and 0s where applied condition is TRUE or FALSE, and SUMPRODUCT sums these digits to give you total count of Blank or empty cells.

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar