5 Ways to Count Blank Cells in Microsoft Excel

Wondering how to count blank cells in Excel? Tag along as I explain the tried and tested methods here!

Counting blank cells in Excel might seem like a minor task, but it can feel as frustrating as trying to find missing documents in a stack of files. If you’ve ever sifted through a large spreadsheet, you know how easy it is to overlook gaps in data.

These blank cells can mess with reports, skew results, and create errors. The good news is that once you know how, it’s surprisingly simple to count them. In this tutorial, I’ll walk you through the process step-by-step. Stick with me, and by the end, you’ll be able to use Excel to count blank cells like a pro.

Using the Find and Replace Tool

The Find and Replace tool is the simplest way to count blank cells in an Excel worksheet or workbook. It focuses on counting blank cells within the data range, ignoring empty cells outside that area. This keeps the results relevant and avoids unnecessary distractions. Plus, since this tool has been part of Excel since version 4.0, it’s one of the most reliable and widely used features.

Let me show you below how effortless this method can be:

Bring up Find and Replace
Bring up Find and Replace

Navigate to the source worksheet and press Ctrl + F to launch the Find and Replace tool.

If you only see the Find what field, click Options.

Find All

Click on the Look in drop-down arrow and select Values.

Hit the Find All button.

Count blank cells using Find and Replace
Count blank cells using Find and Replace

The Find and Replace tool will show all the blank cells along with a count of those cells in the bottom left corner.

Select all blank cells
Select all blank cells

You can click on any of the results below the Find and Replace tool and press Ctrl + A to select all the blank cells.

Excel will automatically highlight cells within the source worksheet.

Using the FILTER and ROWS Functions

If you need to use an Excel formula to calculate the blank cells you can use a combination of the ROWS and FILTER functions.

=ROWS(FILTER(B2:B11,B2:B11=""))+
Using ROWS and FILTER functions

Select the cell where you want the blank cell count and type in the following formula:

=ROWS(FILTER(B2:B11,B2:B11=""))

Don’t forget to change the source data cell range in the above formula, like B2:B11.

Calculating ROWS and FILTER formula
Calculating ROWS and FILTER formula

Hit Enter to calculate the cell.

Excel will derive the total blank cells in the referenced cell range.

A downside of this method is it can only count empty cells in one column at a time.

Calculating multiple columns using ROWS and FILTER
Calculating multiple columns using ROWS and FILTER

If you wish to get a consolidated count of blank cells from multi-column data, use the following formula instead:

=ROWS(FILTER(B2:B11,B2:B11=""))+ROWS(FILTER(D2:D11,D2:D11=""))

Make sure you edit the reference cell ranges according to your own worksheet.

Using the COUNTBLANK Function

The Excel statistical function COUNTBLANK lets you count all empty cells within a specified range, whether in one or multiple columns. If you apply it to a single-column range, it counts only the blank cells in that column. However, if you use it on a range spanning multiple columns, it provides a total count of blank cells across the entire selection.

Using COUNTBLANK function
Using COUNTBLANK function

To use this function, select the cell where you wish to get the blank cell count.

Copy and paste the following formula into the cell:

=COUNTBLANK(B2:B11)

Don’t forget to customize the cell range according to your own worksheet.

Calculate COUNTBLANK
Calculate COUNTBLANK

Hit Enter to get the blank cell count.

Counting blanks in multiple columns
Counting blanks in multiple columns

To include more than one column, simply enter a cell range spanning multiple data columns as shown in the following formula:

 =COUNTBLANK(A1:E11)

Using the SUM and IF Functions

The combination of IF and COUNTA provides more control and flexibility. You can add additional conditions or logic within the IF function. This method is useful if you need to count blanks based on complex conditions or criteria. For example, you might want to count blanks only if another condition in a different column is met.

It’s quite easy to create and implement the formula where IF will determine the number of blank cells in the given cell range and the SUM function will aggregate those into a consolidated figure.

SUM and IF formula
SUM and IF formula

Select a cell where you want the count of blanks and enter the following formula:

=SUM(IF(B2:B11="",1,0))

Ensure you change the reference cell range according to your own worksheet and dataset.

Calculate SUM and IF formula
Calculate SUM and IF formula

Hit Enter to calculate the formula.

Calculate blank cells in multiple columns
Calculate blank cells in multiple columns

To use the formula for a cell range containing multiple data columns, simply enter the entire cell reference address, like A1:E11 in the dataset shown above.

Execute the formula to count empty cells only in columns B2:B11, and D2:D11.

Using an Excel VBA Macro

If you need an automated way to count blank cells you can use Excel VBA. This programming tool allows you to create macros using VBA scripts. When you execute one such macro, Excel performs all the assigned tasks in the background and shows the result in a flash.

You heard it right! This method involves writing VBA scripts. If you don’t know how nothing to worry about. I’ll write and provide the scripts along with the steps so you can simply copy the scripts and use them in your own workbook.

Before moving further with the script and steps, you must learn how to create a VBA macro using scripts by going through this quick Excel tutorial:

📒 Read More: How To Use The VBA Code You Find Online

Now that you’re ready to create a VBA macro, use the following script:

VBA script 1
Sub CountBlankCells()
    Dim rng As Range
    Dim col As Range
    Dim cell As Range
    Dim colCount As Integer
    Dim colBlanks() As Long
    Dim totalBlanks As Long
    Dim msg As String
    Dim i As Integer
    
    ' Show input dialog box to select cell range
    On Error Resume Next
    Set rng = Application.InputBox("Select the cell range", Type:=8)
    On Error GoTo 0
    
    If rng Is Nothing Then Exit Sub
    
    ' Initialize variables
    colCount = rng.Columns.Count
    ReDim colBlanks(1 To colCount)
    totalBlanks = 0
    
    ' Count blanks in each column
    For Each col In rng.Columns
        For Each cell In col.Cells
            If IsEmpty(cell.Value) Then
                colBlanks(col.Column - rng.Column + 1) = colBlanks(col.Column - rng.Column + 1) + 1
                totalBlanks = totalBlanks + 1
            End If
        Next cell
    Next col
    
    ' Prepare message
    msg = "Total count of blank cells: " & totalBlanks & vbCrLf
    For i = 1 To colCount
        msg = msg & "Count of blank cells in column " & i & ": " & colBlanks(i) & vbCrLf
    Next i
    
    ' Show result in a message box
    MsgBox msg, vbInformation, "Blank Cells Analysis"
End Sub
Running a macro
Running a macro

Once you have saved the VBA macro in your workbook, press Alt + F8 to launch the Macro dialog box.

Select the CountBlankCells macro in the list of available macros and hit Run.

Excel will execute the VBA script in the active worksheet.

Select cell range
Select cell range

You’ll see a dialog box asking you to select the input cell range. You can either use your mouse or manually type the reference address.

How to count blank cells in Excel using VBA
How to count blank cells in Excel using VBA

As soon as you click okay, the macro scans the given cell range and shows a floating dialog box showing the overall count of blank cells in the input cell range along with a column-wise count of empty cells.

📚 Read more: If you liked this Microsoft Excel tutorial, you’ll also find the following highly beneficial to master Excel:

Conclusions

Now, you know how to count blank cells in Excel using various commands and functions in Excel.

Also, you’ve learned how to automate the Excel workbook to count blank cells using an Excel VBA script.

If you’re just checking out or still learning Excel, the Find and Replace tool is your best bet. Contrarily, if you wish to use a formula-based approach or include the empty cell calculation in another Excel formula, use any methods, like FILTER and Rows, COUNTBLANK, and SUM and IF-based methods.

Did you learn any new skills today? If yes, share your feedback in the comment box below. Also, if you know of a better method to count empty cells or wish me to cover any different Excel skill, drop a comment below.

About the Author

Bipasha Nath

Bipasha Nath

Bipasha is a technical content writer with 10+ years of experience in the technology industry. She previously worked in a SaaS software development company focused on SharePoint, OneDrive, Word, Excel, Word solutions.

Related Posts

Comments

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Get the Latest Microsoft Excel Tips

Follow Us

Follow us to stay up to date with the latest in Microsoft Excel!

Subscribe for awesome Microsoft Excel videos 😃