COUNTBLANK Function in Excel: Count Empty Cells

📎 This article includes 1 downloadable practice file ↓

Excel Core FunctionsLevel: Foundational → IntermediateCompatibility: All Excel Versions

In this article

1. Syntax & Parameter Breakdown
2. Where You Will Use It
3. Practical Worked Examples
4. Mistakes People Make & How to Avoid Them
5. Frequently Asked Questions
6. Does COUNTBLANK work across multiple non-contiguous ranges?
7. Does a cell with a space count as blank?
8. How do you find and highlight blank cells visually?
9. COUNTBLANK in practice
10. Where you will use it
11. Worked example
12. Mistakes people make
13. Continue learning

The COUNTBLANK function in Excel is an essential spreadsheet tool designed to Count the number of empty cells in a range, essential for identifying missing records, incomplete audit checklists, and data hygiene gaps.

Syntax & Parameter Breakdown

=COUNTBLANK(range)

Argument	Requirement & Description
range	Required. The range of cells from which you want to count blank cells.

Where You Will Use It

Used by compliance, payroll, and HR teams to flag missing PAN/Aadhaar numbers, unsubmitted timesheets, and blank survey responses.

Practical Worked Examples

Here are common real-world formula scenarios utilizing COUNTBLANK:

Formula	Expected Behavior & Purpose
=COUNTBLANK(B2:B500)	Counts how many rows in column B have missing employee ID numbers.
=IF(COUNTBLANK(A2:G2) > 0, "Missing Info", "Ready")	Checks if any mandatory field in a 7-column form is blank.
=COUNTBLANK(C2:C100) / ROWS(C2:C100)	Calculates the percentage of missing values in a data column.

Mistakes People Make & How to Avoid Them

Cells containing formula empty strings (“”) are counted as blank by COUNTBLANK, whereas cells containing spacebar characters are not.

Frequently Asked Questions

Does COUNTBLANK work across multiple non-contiguous ranges?

No, COUNTBLANK requires a single contiguous range. To count across multiple ranges, add them: =COUNTBLANK(A2:A50) + COUNTBLANK(C2:C50).

Does a cell with a space count as blank?

No, a spacebar press is a valid text character (ASCII 32), so COUNTBLANK will not count it as blank.

How do you find and highlight blank cells visually?

Select your range, press F5 (Go To) > Special > Blanks, and apply a fill color.

📚 Master spreadsheet formulas with our Free Excel Course or explore related functions in the Formula Lab.

COUNTBLANK in practice

Where you will use it

• Find missing entries in a checklist
• Count unanswered survey questions
• Data-quality checks before a report

Worked example

Entry	Formula	Result
120	=COUNTBLANK($A$2:A2)	0
Absent	=COUNTBLANK($A$2:A3)	0
(blank)	=COUNTBLANK($A$2:A4)	1
95	=COUNTBLANK($A$2:A5)	1

Results calculated in Excel.

Mistakes people make

Mistake	What to do instead
Cells with spaces look blank but aren't	Use TRIM/clean data; COUNTBLANK doesn't count a space
Expecting zeros to count as blank	Zeros are values; they are not counted

Continue learning

All Statistical guides →

GAMMAINV Function in Excel: Inverse Gamma (Older Name)GAMMAINV returns the value at a gamma probability. It is a compatibility function: Excel…GAMMADIST Function in Excel: Gamma Distribution (Older Name)GAMMADIST returns the gamma probability. It is a compatibility function: Excel keeps it so…TTEST Function in Excel: t-Test (Older Name)TTEST returns the p-value of a t-test between two samples. It is a compatibility…GAMMA.INV Function in Excel: Value for a Gamma ProbabilityStatisticsLevel: ExpertAvailable in: Excel 2010+GAMMA.INV returns the value at a given gamma probability.Syntax=GAMMA.INV(probability, alpha.

📎 Practice files for this article

• 📗
  Practice workbook: Statistics basics22 tasks on realistic sales data — type your formula, the Check column turns green when it is right. Answers sheet included.
  ⬇ XLSX · 34 KB

Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.