Count Blank (Empty) Cells

Excel Formulas › Count

All versionsCOUNTBLANK

To count the empty cells in a range — missing entries in a form, gaps in a dataset — use COUNTBLANK. It counts truly empty cells and formulas that return an empty string, which is often exactly what you want.

Quick formula: to count the blanks in A2:A10:
=COUNTBLANK(A2:A10)
It returns the number of empty cells, treating a formula result of "" as blank too.

Functions used (tap for the full reference guide):

COUNTBLANKCOUNTACOUNTIF

The example

A response column with two missing entries.

AB
1NameReply
2AnaYes
3Ben
4CyNo
5Dot
6Missing replies:2

The formula

The number of missing replies:

=COUNTBLANK(B2:B5) // two empty cells → 2

How it works

COUNTBLANK is purpose-built for empties:

  1. It scans the range and counts every cell with no value — the two empty reply cells.
  2. A formula that returns "" (empty string) is also counted as blank, which COUNTA would not do.
  3. To count blanks across multiple columns or with conditions, switch to COUNTIFS (see variations).

Percentage complete is one subtraction away: =1 - COUNTBLANK(B2:B5)/ROWS(B2:B5) tells you how much of the column is filled in.

Try it: interactive demo

Live demo

Edit the list (leave gaps with empty commas); see the blank count.

Blank:   Filled:

Variations

Count blanks with COUNTIF

Equivalent using the empty-string criterion:

=COUNTIF(B2:B5, "")

Count non-blank instead

COUNTA counts the filled cells:

=COUNTA(B2:B5)

Blanks meeting a condition

Empty replies for the West region only:

=COUNTIFS(C2:C5, "West", B2:B5, "")

Pitfalls & errors

“Blank” vs a space. A cell with a single space looks empty but isn’t — COUNTBLANK won’t count it. Clean stray spaces with TRIM first.

COUNTBLANK counts "" from formulas; COUNTA does not. If your numbers disagree, that’s usually why.

COUNTBLANK takes one range. For multiple separate ranges, add several COUNTBLANKs together.

Practice workbook

📊
Download the free Count Blank (Empty) Cells practice workbook
The responses column with live COUNTBLANK, the COUNTIF equivalent, percent-complete, and conditional blanks, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I count empty cells in Excel?
Use =COUNTBLANK(range). It counts truly empty cells and also formula results of "" as blank. =COUNTIF(range, "") gives the same count.
How do I count blank cells with a condition?
Use COUNTIFS with an empty-string criterion, e.g. =COUNTIFS(C2:C10, "West", B2:B10, "") counts blank replies in the West region.
Why doesn't COUNTBLANK count a cell that looks empty?
The cell probably contains a space or other invisible character. COUNTBLANK only counts genuinely empty cells, so clean stray spaces with TRIM.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: Count cells with text · Count cells that contain text · Flag duplicates

Function references: COUNTBLANK · COUNTA · COUNTIF