Count Blank Cells in Excel and Google Sheets
Purpose
Counting blank cells is one of those checks I run on almost every dataset I open. How many rows are…
Counting blank cells is one of those checks I run on almost every dataset I open. How many rows are missing a status? How many customer records do not have a phone number filled in? How many entries in the required field have been skipped? Before I start analysing data, I want to know how clean it is, and that starts with counting what is missing. The dedicated COUNTBLANK function gives you the answer in one argument — just the range — and it counts every truly empty cell plus every cell that shows as empty because a formula returned an empty string. That last detail is the one subtlety worth knowing. COUNTBLANK is the inverse of the count non-blank cells formula: together, the two totals always add up to the number of cells in the range, which is a useful invariant when you are auditing data quality.
Syntax
✓ All versions
✓ Google Sheets
= Same syntax
=COUNTBLANK(range)
Arguments
| Argument | Required | Description |
|---|---|---|
| range | Required | The cells to check for blanks. Can be a single cell, a range like A2:A100, a multi-column range like A2:D100, or a named range. COUNTBLANK counts every empty cell and every cell containing a formula that returns an empty string. There are no other arguments. |
How it works
COUNTBLANK walks through every cell in the range and counts the cell if it is empty or if it contains a formula that evaluates to an empty string. Truly empty cells — cells that have never been touched or have been cleared with the Delete key — are counted. Cells containing a formula like =IF(A1>10,"High","") that evaluates to "" are also counted because from a display perspective they look empty even though technically they contain a formula. This matches what most people mean when they say "blank". The function accepts any range, including multi-column ranges like A2:D100 — it simply totals all the empty cells in that rectangular area. There is no criteria argument; COUNTBLANK is hard-wired to count blanks specifically. An equivalent alternative is =COUNTIF(range,"") which returns the same count in almost every scenario. The COUNTBLANK function is cleaner and more self-documenting, so I use it by default. For the inverse count — cells that do have a value — use the count non-blank cells formula with COUNTA instead.
Examples
1
Count how many status cells in a column are blank to identify rows with missing information.
| A | B | C | |
|---|---|---|---|
| 1 | Row | Status | Count Blank |
| 2 | 1 | Open | =COUNTBLANK(B2:B11) |
| 3 | 2 | ||
| 4 | 3 | Closed | 4 |
| 5 | 4 | ||
| 6 | 5 | Pending | |
| 7 | 6 | Closed | |
| 8 | 7 | ||
| 9 | 8 | Open | |
| 10 | 9 | ||
| 11 | 10 | Closed |
Row 2: 4
— Four cells are blank: rows 2, 4, 7, and 9. The remaining six rows have a status filled in (Open, Closed, Pending).
COUNTBLANK requires only the range argument. There is no criteria to specify because the function is dedicated to counting blanks. This makes the formula self-documenting — anyone reading it understands the intent immediately.
2
Count how many customer records have a blank name to identify incomplete entries in a CRM.
| A | B | C | |
|---|---|---|---|
| 1 | Row | Customer Name | Count Blank |
| 2 | 1 | Alice | =COUNTBLANK(B2:B11) |
| 3 | 2 | ||
| 4 | 3 | Carla | 4 |
| 5 | 4 | Dan | |
| 6 | 5 | ||
| 7 | 6 | Finn | |
| 8 | 7 | ||
| 9 | 8 | Hugo | |
| 10 | 9 | Iris | |
| 11 | 10 |
Row 2: 4
— Four rows have no customer name: rows 2, 5, 7, and 10. The other six rows have names filled in (Alice, Carla, Dan, Finn, Hugo, Iris).
This is the classic data-quality pattern for required fields. A count of 4 missing out of 10 is a 40% gap — a signal that the data needs cleaning before any downstream analysis.
3
Use COUNTIF with an empty-string criteria as an alternative to COUNTBLANK, giving the same result on the same dataset.
| A | B | C | |
|---|---|---|---|
| 1 | Row | Status | Count Blank |
| 2 | 1 | Open | =COUNTIF(B2:B11,"") |
| 3 | 2 | ||
| 4 | 3 | Closed | 4 |
| 5 | 4 | ||
| 6 | 5 | Pending | |
| 7 | 6 | Closed | |
| 8 | 7 | ||
| 9 | 8 | Open | |
| 10 | 9 | ||
| 11 | 10 | Closed |
Row 2: 4
— COUNTIF with empty-string criteria returns 4 — the same count of blank cells as COUNTBLANK produced in Example 1 on the identical dataset.
Both COUNTBLANK and COUNTIF(range,"") produce the same result in almost every scenario. I default to COUNTBLANK because it is shorter, self-documenting, and does not require remembering the quotation-mark criteria.
Common use cases
1. Count rows with missing status entries in an order tracking sheet for data-quality checks
=COUNTBLANK(B2:B500)
2. Count customers without a phone number recorded in a CRM export
=COUNTBLANK(D2:D1000)
3. Count incomplete task assignments where the assignee field is empty
=COUNTBLANK(E2:E300)
4. Count missing values across multiple required fields in a submission form
=COUNTBLANK(A2:E200)
5. Count skipped cells in a survey response column to measure question completion rate
=COUNTBLANK(F2:F500)
Common errors
Count is higher than expected
The range contains formulas that return empty strings (""). COUNTBLANK treats those as blank even though the cells contain formulas.
Fix: Check IF formulas with "" as a false branch. If you need to exclude those from the blank count, test with =COUNTIF(range,"")-COUNTA(range) patterns or switch to COUNTA and subtract from total rows.
Count is lower than expected
The cells contain a space character or a non-printing character such as a trailing newline. A cell with " " (a space) is not blank.
Fix: Use TRIM and clean the data. Cells with only spaces are not considered blank by COUNTBLANK.
Formula returns #NAME?
COUNTBLANK is spelled incorrectly or the function is not recognised — some older spreadsheet apps use COUNTEMPTY instead.
Fix: Spell the function COUNTBLANK. It is available in every version of Excel and Google Sheets.
Tips and variations
Use across multiple columns to audit a whole form or table
COUNTBLANK accepts 2D ranges. Passing A2:E200 gives you the total number of blank cells across all five columns. This is my go-to formula for quick data-quality reports on submission sheets — a direct complement to the count non-blank cells total.
=COUNTBLANK(A2:E200)
Combine with total rows to show a completion percentage
Divide non-blank cells by total cells to get a completion rate. The COUNTA function counts non-blanks and ROWS gives the total — the ratio is a handy progress metric. Use the count non-blank cells formula for the complementary count.
=COUNTA(A2:A100)/ROWS(A2:A100)
Use with conditional formatting to visualise sparse columns
Conditional formatting rules that highlight blank cells combined with a COUNTBLANK formula above the column give you both a visual and a numeric summary in one place. This catches missing data without having to scroll through hundreds of rows.
=COUNTBLANK(A2:A100)
Excel vs Google Sheets
Excel vs Google Sheets
COUNTBLANK works identically in Excel 2003 and newer, Excel 365, and every version of Google Sheets. The behaviour — counting truly empty cells and cells with formula-returned empty strings — is the same in both applications. Formulas copy between the two without any changes.
Related reading
Frequently asked questions
Use =COUNTBLANK(range). For example, =COUNTBLANK(A2:A100) returns the number of empty cells in A2:A100. COUNTBLANK also counts cells that contain formulas returning an empty string (""). It is available in every version of Excel and Google Sheets.
COUNTBLANK counts truly empty cells plus cells with formulas that return "". COUNTIF(range,"") counts the same two types. Both give identical results in almost every case. COUNTBLANK is more readable and shorter, so it is the preferred choice.
Yes. If a cell contains the formula =IF(A1>10,"High","") and the condition is false, the cell displays nothing but contains an empty string. COUNTBLANK includes such cells in its count. If you want only truly empty cells, the simplest workaround is to avoid empty-string formulas or use a slightly more advanced pattern with LEN.
Pass a multi-column range to COUNTBLANK: =COUNTBLANK(A2:C100) counts every blank cell in the 3-column, 99-row range. The function handles two-dimensional ranges transparently, giving you the total number of empty cells across the whole area.
The range almost certainly contains formulas that return an empty string. From a human perspective the cell looks empty, but it technically contains a formula result, and COUNTBLANK counts it as blank. Check formulas with IF conditions that have "" as their false branch — those are the usual culprits.
Related Functions