How to check if a range of cells is empty in Excel
Formula (Excel and Google Sheets)
=IF(COUNTA(B2:E2)=0,"No response","Answered")Count what is filled in and test for zero: =IF(COUNTA(B2:E2)=0,"No response","Answered").
- Result on the example
- one result per row in
F2, filled down toF6 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in F2, filled down to F6. Change any input and the result updates, or edit the formula to experiment.
How it works
COUNTA counts the cells that contain anything. Zero means every cell in the range is empty. R-3 answered only one question, which is enough to count as answered.
=IF(COUNTA(B2:E2)=0,"No response","Answered")Result"No response"
B2:E21 row × 4 cols: (empty), (empty), (empty), (empty)A range: 4 cells in one row.
COUNTA(B2:E2)0COUNTA: Counts cells that are not empty.- value1
B2:E21 row × 4 cols: (empty), (empty), (empty), (empty)
- value1
COUNTA(B2:E2)=0TRUEThe = operator tests whether the two sides are equal.
IF(COUNTA(B2:E2)=0,"No response","Answered")"No response"IF: Returns one value if a test is true, another if false.- logical_test
COUNTA(B2:E2)=0TRUE - value_if_true
"No response" - value_if_false
"Answered"not used
- logical_test
In Google Sheets
The same formula works in Google Sheets: =IF(COUNTA(B2:E2)=0,"No response","Answered"). We ran it in Google Sheets mode on the same data and it returns the same results.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =IF(COUNTBLANK(B2:E2)=COLUMNS(B2:E2),"No response","Answered") | No response, Answered, Answered… | All cells blank means the blank count equals the number of columns. Unlike COUNTA, it treats formulas that return "" as blank. |
| =IF(SUMPRODUCT(--(B2:E2<>""))=0,"No response","Answered") | No response, Answered, Answered… | Counts cells that aren't empty text, so formulas returning "" count as empty too. |
Common mistakes
- ISBLANK checks one cell. Given a range, it returns one TRUE or FALSE per cell rather than a single answer, so count instead.
- COUNTA counts cells holding a space or a formula that returns "". Use the COUNTBLANK version when the row contains formulas.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Check if a range of cells is empty
F2, show "No response" when all of B2:E2 are empty, otherwise "Answered". Fill down to F6.Questions
How do I label partly completed rows?
=IF(COUNTA(B2:E2)=0,"None",IF(COUNTA(B2:E2)<4,"Partial","Complete")).
How do I count the empty rows?
Count the labels: =COUNTIF(F2:F6,"No response").
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.