Excel provides numerous methods to count blank or empty cells in a 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.
How to count blank or empty cells in Excel
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 an 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 a 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
This is also Excel’s built-in functionality to count blank or empty cells. The Find and Replace functionality can count the 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 the 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 a range. It’s a 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
The 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’s 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’s SUMPRODUCT function can also be used to sum the count of cells that meet the criteria of being blank or empty, such as;
=SUMPRODUCT(--(A1:A8=""))
When you apply this formula in this manner, it gives you the total count of blank cells. Double Hyphen symbol (–) converts the cells as 1s and 0s where the applied condition is TRUE or FALSE, and SUMPRODUCT sums these digits to give you the total count of Blank or Empty cells.
Still need some help with Excel formatting or have other questions about Excel? Connect with a live Excel expert here for some 1 on 1 help. Your first session is always free.
Leave a Comment