Excel COUNTBLANK Function (Count Empty Cells) – Examples & Practice
Practice the Excel COUNTBLANK function online with an interactive grid, instant feedback, and clear formula help.
Instruction
Count how many blank cells exist in B2:B6.
Formula Syntax
=COUNTBLANK(range)
- range: The range where you want to count empty cells.
What it does
COUNTBLANK counts empty cells in a range. It helps you find missing data quickly. Pair it with COUNTA to reconcile totals on rectangular ranges.
Excel COUNTBLANK Function Examples
Count blanks in a range
=COUNTBLANK(B2:B6)
Returns how many empty cells exist.
Compare filled vs empty
=COUNTA(A2:A6)+COUNTBLANK(A2:A6)
Should equal total cells in a rectangular range when used consistently.
text-cleanup.xlsx
| A | B | |
|---|---|---|
| 1 | Raw Text | Helper |
| 2 | ACME Corporation | ACME |
| 3 | Invoice-2026-0001 | 2026 |
| 4 | VIP_CLIENT | VIP |
| 5 | John Doe | Doe |
| 6 | Product Name | Product |
| 7 | Output |
Input Formula
Need Help?
Tips
- Clean stray spaces with TRIM before counting blanks.
- Name your validation ranges so COUNTBLANK formulas read clearly.
- Watch for formulas that return empty text.
COUNTBLANK Function Use Cases
- Find missing required fields before export
- Measure template completion
- Detect holes in time series data
- Audit spreadsheets after bulk deletes
- Balance row counts in validation checks
Common mistakes - COUNTBLANK function not working
- Treating cells with spaces as blank
- Including a larger range than intended (false blanks)
- Forgetting formulas that return ""
- Using COUNTBLANK when COUNTIFS on criteria is clearer
- Expecting it to count filtered-visible blanks only
FAQ
Does COUNTBLANK count formulas that show nothing?
Formulas that return empty strings may still occupy the cell; behavior can differ from truly empty cells.
Can I count blanks in a whole column?
You can, but whole-column references can be heavy. Prefer bounded ranges when possible.
Is zero a blank?
No. Zero is a value and is not counted as blank.
COUNTBLANK vs COUNTA?
They are complementary views: blanks vs non-blanks for the same range.
Does COUNTBLANK ignore hidden rows?
Standard COUNTBLANK does not ignore hidden rows unless you use different tools like SUBTOTAL with filters.
Comparison
| Function | Focus |
|---|---|
| COUNTBLANK | Empty cells |
| COUNTA | Non-empty cells |
| COUNT | Numeric cells |
Example
=COUNTBLANK(B2:B6)
Advanced examples
Filtered blanks
If you need blanks among visible rows only, consider FILTER views or SUBTOTAL patterns instead of raw COUNTBLANK.