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;
- If you select Values option then it will count both Blank and Empty cells in selected range.
- 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;
- 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;
- 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;
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.