All Cells in Range Are Blank? Check & Fix
The calculator for this page is switched off while it is checked. The formula is below, and the worked example in the article shows how to use it by hand.
This article explains how to check whether all cells in a given range are blank in spreadsheet applications like Microsoft Excel and Google Sheets, and how to build a formula that returns TRUE only when every cell is empty. You’ll learn the core functions, see a worked example, and get fixes for common mistakes.
Formula
The simplest way to test if all cells in a range are blank is to compare the number of blank cells to the total number of cells in that range. If they are equal, every cell is blank.
In Excel and Google Sheets, the formula is:
=COUNTBLANK(range) = ROWS(range) * COLUMNS(range)
Where:
- range – the rectangular block of cells you want to test (e.g., A1:C3).
- COUNTBLANK(range) – counts empty cells (including cells with only an empty string
""). - ROWS(range) – number of rows in the range.
- COLUMNS(range) – number of columns in the range.
The product ROWS * COLUMNS gives the total cell count. If the count of blanks equals that total, then no cell has content.
Worked Example
Suppose you want to check the range A1:C3 (3 rows × 3 columns = 9 cells).
- Enter a few values, say A1 = 5, B2 = “text”, and leave the other 7 cells empty.
- Apply the formula:
=COUNTBLANK(A1:C3) = ROWS(A1:C3) * COLUMNS(A1:C3) - COUNTBLANK returns 7 (the empty cells).
- ROWS returns 3, COLUMNS returns 3, so 3 × 3 = 9.
- The comparison becomes 7 = 9, which is FALSE.
If you delete the two values, COUNTBLANK becomes 9, and 9 = 9 gives TRUE. That is the result you want.
When to Use This Test
You might need to know if a range is completely empty for several reasons:
- To conditionally format a row only when no data has been entered.
- To skip calculations or charts when input data is missing.
- To validate that a user has filled in a required block of cells.
- To trigger a message or a different formula when a section is unused.
It is especially common in dashboards and templates where you want to hide or gray out sections that have no data yet.
How to Read the Result
The formula returns a Boolean value: TRUE means every cell in the range is blank; FALSE means at least one cell contains something. That something could be a number, text, a formula that returns an empty string, or even an apostrophe (which Excel treats as text).
If you want to use this in conditional formatting, you can put the formula directly in the rule. For example, to highlight a row when all its cells are blank, select the row and use a formula rule like =COUNTBLANK($A1:$C1)=3.
Common Mistakes and Fixes
- Counting formula blanks: COUNTBLANK counts cells with formulas that return
""as blank. If you want to treat those as non-blank, useSUMPRODUCT(--(LEN(range)>0))instead. - Wrong total cell count: If you hardcode the number of cells (e.g., 9 for A1:C3), you must update it when the range changes. Using ROWS and COLUMNS avoids that error.
- Using COUNTA: COUNTA counts non-empty cells. A range with all blanks would give COUNTA = 0, but a range with one space character would give COUNTA = 1. That is not the same as “all blank”.
- Spaces and invisible characters: A cell that contains a space looks blank but is not. Use
=TRIM(A1)=""to check for truly empty cells if spaces are a concern.
Limitations and Alternatives
The formula works for any rectangular range, but it does not handle non-contiguous ranges directly. For non-contiguous ranges, you would need to test each area separately or use a more complex array formula.
In Google Sheets, you can also use =ARRAYFORMULA(SUM(--(range<>"")))=0 to count non-blank cells and see if the sum is zero. In Excel, =SUMPRODUCT(--(range<>""))=0 works similarly.
If you need to check entire rows or columns, you can use whole-row references like A:A, but be careful because those include millions of cells, and a single stray entry far down the column will break the test.
Frequently Asked Questions
Does COUNTBLANK count cells with formulas that return “”?
Yes. In both Excel and Google Sheets, a formula that returns an empty string is considered blank by COUNTBLANK. If you want to treat those as non-blank, use =SUMPRODUCT(--(LEN(range)>0))=0 instead.
What if my range contains spaces or non-printing characters?
Those cells are not truly blank. To detect them, you can use =SUMPRODUCT(--(TRIM(range)<>""))=0 to ignore spaces, but non-printing characters like line breaks may still cause issues. A VBA or Apps Script solution can clean them if needed.
Can I use this formula in conditional formatting?
Yes. In Excel, select the range, go to Home → Conditional Formatting → New Rule → Use a formula, and enter a formula like =COUNTBLANK($A1:$C1)=3 for each row. In Google Sheets, use the “Custom formula is” option with the same logic.
Why does my formula return FALSE when I can see no data?
There may be invisible content: spaces, apostrophes, or formulas that return an empty string. Also, check if the range includes more cells than you think (e.g., you typed A1:C3 but meant A1:C2). Use the Go To Special → Blanks feature in Excel to see which cells are considered blank.
Is there a way to check if a range is blank without COUNTBLANK?
Yes. You can use =COUNTA(range)=0 in Excel and Google Sheets. COUNTA counts all non-empty cells, so a result of 0 means no cell has any content. However, COUNTA does not count cells with formulas that return “”, so the two methods can give different results.