The only thing worse than wrong data in your spreadsheet is Empty Cells. If these blanks aren’t filled, they can cause problems when using formulas. I usually fill these empty cells with 0 or “NA” (Not Available).
In large datasets, doing this manually is almost impossible and very time-consuming. Luckily, Excel has a way to select all blank cells at once, making it much easier to fill them!
Method 1:- Count Blank Cells in Excel Using COUNTBLANK Function
The COUNT function in Excel is useful in many situations. To count empty cells specifically, you can use a similar function called COUNTBLANK. Here’s how it works!
- Click on cell D13
- Type =COUNTBLANK(
- Enter the range you want to check, like A2:D10 (selected all data)
- Type the closing parenthesis ) and press Enter.
This will show the count of blank cells in the range D13
Now, you’ll see the total count of blank cells for the range in that one cell.
Method 2 :- Count Blank Cells in Excel Using Find Feature
Select the cells where you want to find blanks.
Go to the Home tab, click Find & Select in the Editing section, and choose Find.
In the Find and Replace window, leave the Find What box empty. Then click Options.
Adjust these options on the left:
- Within: Sheet
- Search: By Rows or By Columns (whichever you prefer)
- Look In: Values
Click Find All. You’ll see the number of blank cells at the bottom left.
You can view the list of blank cells. Click one to go to it, or use Find Next to move through each result.
When done, click Close.
This makes it easy to count blank cells. And if you want to highlight them, Excel has a feature for that too!
Method 3 :- Count Blank Cells in Excel Using COUNTIF Function
- Click on cell D12 and type =COUNTIF(
- Enter the range you want, like B2 : D10
- For the criteria, type “=” to count cells with no value.
- Close the parentheses ) and press Enter.
This will count the blank cells in the range D12
