While working in Excel, it’s common that you may need to count values, cells, rows and columns. It’s easy to count manually only when your spreadsheet contains minimal data. If you have a large data set and wish to count blank cells in Excel, then it can be a daunting task. Luckily, Excel comes with an Inbuilt function that counts blank cells.
In this article, I will guide you on how to count blank cells in Excel. Additionally, I will also show you how to apply the same formulas to count empty rows and columns.
Use COUNTBLANK function
COUNTBLANK() function belongs to the statistical functions category. It is available in Excel Versions from 2013 to the latest version of Microsoft Excel 365. It has a very straightforward syntax which is.
=COUNTBLANK(range)
Range refers to the cluster of cells that you want to see the number of blanks it contains.
Assuming you have the data below
1. To count all the empty cells in the first row containing data, type the following formula in cell G2
=COUNTBLANK(A2:F2)
As you can see from the image above, the formula returns 1 as the number of empty cells in the first row.
2. Apply the formula to the rest of the cells by dragging down or double-clicking the fill handles
If you want to count the number of empty cells in a column containing data, just use the same formula. The only thing you need to change is the range. Take a look at the example below.
I want to count the empty cells in Column A, which starts at A2 and Ends at A8, so the formula will be.
=COUNTBLANK(A2:A8)
Drag the fill handle to the right to automatically calculate the number of blank cells in all columns.
Using the COUNTIF function to count blank cells in Excel
As the name suggests, COUNTIF is a combination of the COUNT function and the IF conditional function. The count is responsible for counting values, cells, rows, columns, and so on. Conversely, the IF function creates a logic so that the COUNT function can only count when a condition is met. Follow these easy steps.
1. Type the following formula in cell G2
=COUNTIF(A2:F2,”=”)
As you can see from the image above, the formula returns 1 as the empty cell, just like the COUNTBLANKS function.
Adding an equal sign inside the “” helps to look for cells with no value since you have not added anything after the equal sign.
2. Double-click the fill handle or drag it down to automatically count the rest of the blank cells.
Using Find and Replace to count Excel blank cells
Find and Replace is one of the most used features, especially in Office apps. You can easily count the number of blank cells by using Find and Replace. Follow the following steps:
1. Use the mouse to select your data range or table containing blank cells
2. Press ctrl+F on your keyboard (press and hold ctrl, then press the F key)
3. Under the Find What section, leave the section blank
4. Now click on options and tick the checkbox that says match entire cell contents
5. Click the dropdown to look in and select values
6. Now click on find all buttons. The number of blank cells will be displayed in the bottom left corner
As you can see in the image above, the empty cells have been indicated.
Use COUNTIFS function
You can also use the COUNTIFS function to count the number of empty cells with additional criteria.
Let’s assume you have a row containing car models and their positions in the adjacent column. However, one brand is missing the data in the adjacent column for several cells. You want to know the number of blanks without counting the cells manually. The most appropriate function to use in this case is COUNTIFS()
The double quote at the end specifies that you are searching for blank cells.
Download Practice Template
How to count blank cells in Excel Using VBA
1. Open Excel and press ALT + F11 to open the VBA editor.
2. Insert a new module by clicking on “Insert” -> “Module.”
3. Copy and paste the code below into the module.
Sub CountBlankCells()
Dim rng As Range
Dim blankCount As Long
' Prompt the user to enter the range
On Error Resume Next
Set rng = Application.InputBox("Enter the range:", Type:=8)
On Error GoTo 0
' Check if a valid range was selected
If rng Is Nothing Then
MsgBox "Invalid range selected.", vbExclamation
Exit Sub
End If
' Loop through each cell in the range and count the blank cells
For Each cell In rng
If IsEmpty(cell) Then
blankCount = blankCount + 1
End If
Next cell
' Display the count of blank cells
MsgBox "Number of blank cells: " & blankCount, vbInformation
End Sub
4. Click on Run
5. Excel will prompt you to select a range. Remember this is the range of your data containing blank cells. Use the mouse to select the range and select ok.
6. Excel will display the total number of blank cells found in that range.
Final Thoughts
That’s how to count blank cells in Excel within a range, column or row. All the methods discussed in this article are accurate and easy to use. Whether you choose, Find and Replace, VBA, or COUNTIF method you will still get the same answer.
Similar Reads