Finding Empty Cells With COUNTBLANK

Sometimes the useful question isn't "how many cells are filled?" but "how many are empty?"

Maybe you want to know how many survey questions were skipped, or how many days have no sales logged yet.

The COUNTBLANK function answers this directly: it counts the empty cells in a range. Let's see how it works.

COUNTBLANK returns the number of empty cells in a single range.

Its shape is:

• =COUNTBLANK(range)

Unlike COUNT and COUNTA, COUNTBLANK takes just one range. It looks inside that range and tallies the cells with nothing in them.

Suppose A1:A5 holds 120, (blank), 200, (blank), 80.

The formula =COUNTBLANK(A1:A5) returns 2, because two cells are empty.

Notice the pattern: COUNT here returns 3 (the numbers) and COUNTBLANK returns 2 (the gaps). Together they add up to the 5 cells in the range.

Here's an important contrast with COUNTA. A cell holding an empty text string, like the output of =IF(B1>0, B1, ""), looks empty.

COUNTA treats that cell as filled. But COUNTBLANK treats it as blank and counts it.

So COUNTBLANK matches what your eyes see: if the cell appears empty, it's counted.

Be careful with cells that contain just a space typed by hand. A space is a real character, so the cell is not empty.

COUNTBLANK will not count a cell holding a space, even though it looks empty.

If your blank count seems too low, hidden spaces may be the culprit. Cleaning them with TRIM can help, which you'll see in a later course.

You sent a survey, and column C holds answers to one question, from C2 to C101 (100 respondents).

To find how many people skipped that question, use:

• =COUNTBLANK(C2:C101)

The result is your count of missing answers, an instant measure of how complete your data is.

COUNTBLANK and COUNTA make a great team. One counts the gaps, the other counts the filled cells.

For a survey range C2:C101, you can describe completeness with two formulas:

• =COUNTA(C2:C101) for answered.
• =COUNTBLANK(C2:C101) for skipped.

Together they always add up to the total number of cells in the range.

You can turn blank counts into a percentage. To show what fraction of a range is filled in, subtract the blanks from the total and divide.

For C2:C101 (100 cells):

• =(100 - COUNTBLANK(C2:C101)) / 100

Format the result as a percentage and you have a live completion rate that updates as answers come in.

Remember the key limitation: COUNTBLANK accepts a single range, not a list of ranges.

Writing =COUNTBLANK(A1:A10, C1:C10) will not work the way COUNT or COUNTA would.

If you need blanks across two separate areas, run COUNTBLANK on each and add the results together: =COUNTBLANK(A1:A10) + COUNTBLANK(C1:C10).

COUNTBLANK works the same in both Microsoft Excel and Google Sheets. The same formula gives the same answer in either tool.

So whether you're tracking missing survey answers in Excel or in Sheets, =COUNTBLANK(range) reliably tells you how many cells are still empty.

COUNTBLANK can scan a rectangular block, not just a single column. This is handy for spotting missing data across a whole table at once.

For a data table in A2:E50, the formula =COUNTBLANK(A2:E50) counts every empty cell in those five columns and many rows combined.

A high number is a quick warning that your table has gaps worth filling before you analyze it.

Test your understanding of COUNTBLANK.

You now know how to measure the empty cells in your data. Key points:

• =COUNTBLANK(range) counts the empty cells in one range.
• It treats empty strings ("") from formulas as blank.
• A cell with a space is not blank, so it isn't counted.
• It takes a single range; add separate calls for multiple areas.
• Pair it with COUNTA to track data completeness or completion rates.

Next, we'll find the biggest and smallest values with MAX and MIN.