If your task is to get Excel count blank cells in your worksheet, read this article to find 3 ways to accomplish it. Learn how to search and select empty cells with the Go to Special option, use Find and Replace to count blanks or enter a formula in Excel 2019 - 2010.
In my previous post How to count non-empty cells in Excel, I showed 3 ways to get the number of filled cells in a range. Today, you'll learn how find and count blanks in your table.
Suppose you supply goods to multiple stores. You have a worksheet in Excel with the shops' names and the quantity of items they sold. Some cells in the Items sold column are empty.
You need to make Excel count blank cells in your sheet or find and select them to see how many stores didn't provide the necessary details. Doing it manually would take too much time, so feel free to use one of the options I show in this post:
If you need to quickly find all blank cells in Excel, use the Go to Special functionality. Though it will not show you the number of blanks, the tool will quickly highlight all empty cells so that you can easily locate them in your sheet.
Tip. You can use the Fill Color option under the Home tab to change the background of empty cells and keep the selection.
Please note that Go to Special will not find pseudo-blank cells with spaces or those with formulas that return blanks.
You can use the standard Excel Find and Replace dialog to count empty cells in your table. This tool will display the list with all blanks next to their addresses in your sheet. It also lets you navigate to any empty cell by clicking on its link in the list.
Note. If you select one cell Find and Replace will search the entire table.
Tip. If you select the results on the add-in pane, it's possible to fill the empty cells with the same value, like 0 or the words "no information". To learn more, please check the article Fill empty cells with 0 or another specific value.
This part is for the formula-oriented users. Though you will not see the found items highlighted, it's possible to get the number of blanks in any cell you select to compare to the next search.
Follow the steps below to apply them:
=ROWS(A2:A5) * COLUMNS(A2:A5) - COUNTA(A2:A5)
You will get the result in the selected cell.
On the below picture, I show the summary of how these 2 formulas work with constants and pseudo-blank cells. In my sample, I have 4 cells selected. A2 has a value, A3 has a formula that returns an empty string, A4 is empty and A5 contains two spaces. Below the range, you can see the number of the found cells next to the formula I employed.
You can also use the COUNTIF formula for counting empty cells in Excel, please check out this tutorial for full details - COUNTIF for blanks and non-blanks.
Now you know how to find and count blank cells in your Excel table. Use a formula to paste the number of empty cells, turn on Find and Replace to highlight blanks, navigate to them and see their number, or choose the Go To Special feature to quickly select all blank ranges in your table. Feel free to share any other hints you may have. Be happy and excel in Excel!
Table of contents