Statistical

COUNTBLANK Function in Excel

Counts the number of empty cells in a specified range.

Syntax

  • =COUNTBLANK(range)

Arguments

  • range (required): The range to count empty cells in

Examples

  • =COUNTBLANK(A1:A10) - Count empty cells in range - Result: Number of blanks
  • =ROWS(A1:A10)-COUNTBLANK(A1:A10) - Count non-empty cells - Result: Filled cell count

Excel COUNTBLANK function documentation

  • COUNTBLANK counts empty cells in a range — cells with no content and no formula.
  • Syntax: =COUNTBLANK(range). Part of the Statistical function family.
  • Ideal for checklist gaps, missing survey answers, and data-quality dashboards.
  • Related: [COUNTA](/functions/counta/), [ISBLANK](/functions/isblank/), [COUNTIF](/functions/countif/) with criteria "".

What counts as blank for COUNTBLANK

  • Truly empty cells — never edited or cleared to empty state.
  • Cells with formula ="" are usually not blank to COUNTBLANK (they contain a formula).
  • Cells with only spaces may not count as blank — use TRIM audit helpers.
  • Deleted content leaving empty cell counts as blank.

Step-by-step: find missing required fields

  • Step 1 — Required column B rows 2–500 in an intake form.
  • Step 2 — Missing entries: =COUNTBLANK(B2:B500).
  • Step 3 — Completed entries: =COUNTA(B2:B500) for comparison.
  • Step 4 — If COUNTBLANK seems low, check for formulas displaying blank text.
  • Step 5 — Flag rows: =IF(B2="", "Missing", "OK") for row-level review.

COUNTBLANK vs COUNTA vs ISBLANK

  • COUNTBLANK — range-level count of empty cells.
  • COUNTA — inverse perspective on filled cells.
  • ISBLANK — single-cell TRUE/FALSE test; does not treat "" formula as blank.
  • Data validation: combine ISBLANK with conditional formatting to highlight gaps.

Worked examples to copy

  • Missing phone numbers: =COUNTBLANK(E2:E1000).
  • Completion KPI: =1-COUNTBLANK(B2:B101)/100 for percent complete.
  • Multi-field gap: =COUNTBLANK(B2)+COUNTBLANK(C2)+COUNTBLANK(D2) on one row.
  • Dashboard tile: =COUNTBLANK(Tasks[Owner]) on an Excel Table column.

People also ask

  • COUNTBLANK and formulas? — Formulas in the cell mean it is usually not counted as blank.
  • COUNTBLANK vs empty string? — ="" displays blank but may not increment COUNTBLANK.
  • Count blank rows in a table? — Apply COUNTBLANK per column or use FILTER for full empty rows.
  • COUNTBLANK on merged cells? — Merged layout can skew counts; prefer Tables without merges.

Common errors

  • Cells with formulas returning empty string are counted as blank
  • Cells with spaces are NOT blank

Use cases

  • Data completeness check
  • Missing data analysis
  • Form validation

Frequently asked questions

  • What is COUNTBLANK in Excel? COUNTBLANK counts empty cells in a range. It's useful for data quality checks, finding missing entries, and validating form completeness. A cell is blank if it's truly empty or contains a formula returning empty string ("").
  • What is the difference between COUNTBLANK and COUNTA? COUNTBLANK counts empty cells. COUNTA counts non-empty cells. They're complementary: COUNTBLANK(A1:A10) + COUNTA(A1:A10) = total cells in range. Use COUNTA for filled cells, COUNTBLANK for missing data.
  • Why does COUNTBLANK not count cells with spaces? Cells containing only spaces are not truly empty - they contain space characters. Use =SUMPRODUCT((TRIM(A1:A10)="")*1) to count cells that are empty or contain only spaces. Or clean data first with TRIM().

Editorial review

  • Reviewed by Excel.Directory Editorial Team. Updated May 2026.

When to use COUNTBLANK

  • Data completeness check — common Statistical scenario for COUNTBLANK.
  • Missing data analysis — common Statistical scenario for COUNTBLANK.
  • Form validation — common Statistical scenario for COUNTBLANK.

COUNTBLANK in the Statistical category

  • Browse all Statistical functions at /categories/statistical/ for related formulas.
  • COUNTBLANK syntax: =COUNTBLANK(range)
  • range (required): The range to count empty cells in
  • Confirm COUNTBLANK arguments match the syntax shown above before filling down.
  • Lock table and range references with $ when copying formulas across rows or sheets.

Formula checklist before you copy down

  • Confirm COUNTBLANK arguments match the syntax shown above before filling down.
  • Lock table and range references with $ when copying formulas across rows or sheets.
  • If results look wrong, check for text stored as numbers and invisible spaces with TRIM.
  • Spot-check three known input rows manually against expected output.

Related Excel functions

  • COUNT (/functions/count/): Counts the number of cells that contain numbers.
  • COUNTA (/functions/counta/): Counts the number of cells that are not empty.
  • ISBLANK (/functions/isblank/): Returns TRUE if the cell is empty.

Errors to watch for

  • Cells with formulas returning empty string are counted as blank — review causes on linked error pages in the directory.
  • Cells with spaces are NOT blank — review causes on linked error pages in the directory.

Copy-paste audit workflow

  • Enter COUNTBLANK on three test rows with known expected output documented on a QA tab.
  • Fill down only after absolute references are locked on lookup tables and rate tables.
  • Compare against manual calculation or a calculator for financial and statistical functions.
  • Search this directory for comparison guides when choosing between similar functions in the same category.

COUNTBLANK worked examples to copy

  • =COUNTBLANK(A1:A10) — Count empty cells in range. Expected result: Number of blanks.
  • =ROWS(A1:A10)-COUNTBLANK(A1:A10) — Count non-empty cells. Expected result: Filled cell count.

COUNTBLANK reference summary for crawlers and offline review

  • COUNTBLANK belongs to the Statistical category in Excel. Counts the number of empty cells in a specified range.
  • Full syntax: =COUNTBLANK(range). Open /functions/countblank/ for parameters, FAQs, and related pages.
  • Common mistakes: Cells with formulas returning empty string are counted as blank; Cells with spaces are NOT blank
  • Pair this function with comparison guides when another Excel formula might fit the same task better.
  • Review fix-excel-formula-errors when unexpected errors appear after upgrading Excel or sharing across locales.