All cells in range are blank

When working with Excel, there are times when you need to determine if a range of cells is empty. This can be useful in various scenarios, such as data validation, error checking, or report preparation. In this article, we'll explore a couple of formulas that can help you check if all cells in a given range are empty. The first and simplest formula is based on the COUNTA function, which will count numbers and text values in cells. The second formula is based on the SUMPRODUCT with the LEN function. This option is useful when you need to treat formulas that return empty strings ("") as empty.

COUNTA option

The simplest way to test if a range is empty is to use the COUNTA function. The COUNTA function is designed to count the number of cells in a range that are not empty. It considers any cell with content, including text, numbers, errors, and formulas, as non-empty. In the worksheet shown, we use COUNTA to test if all cells in a range are empty with a formula like this in cell I5:

=COUNTA(C5:G5)=0

If the count is zero, the formula will return TRUE. If the count is any other number, the formula will return FALSE. In other words, the result will be TRUE only when all cells in C5:G5 are empty.

The screen below shows how you can apply the formula above inside the IF function. The idea here is clearly mark rows where all 5 cells are blank. The formula in cell I5 is:

=IF(COUNTA(C5:G5)=0,"*","")

Note: COUNTA counts the number of cells in a range that are not empty. However, it won't work correctly for cells that contain formulas resulting in empty strings (""). Such cells will be considered non-empty by the COUNTA function even though they visually appear blank. If you need to treat formulas that return "" as empty, see the SUMPRODUCT options below.

SUMPRODUCT option

Another way to test for an empty range is to use the SUMPRODUCT with LEN  like this:

=SUMPRODUCT(LEN(range))=0

The LEN function calculates the length of the content in each cell in a given and returns the length as a number. The SUMPRODUCT function then returns the sum of all lengths. If the combined length is zero, the formula will return TRUE. Otherwise, the formula will return FALSE.

This is a more robust solution to the empty vs. blank dilemma. If a cell is truly empty, its length will be zero, and LEN will return zero. If a cell contains a formula that results in an empty string, it is technically not empty, but LEN will still return zero since an empty string ("") has no length. In other words, LEN will treat formulas that return ="" the same as truly empty cells. In cell I5, the formula evaluates like this:

=SUMPRODUCT(LEN(C5:G5))=0
=SUMPRODUCT({2,2,2,0,2})=0
=8=0
=FALSE

In cell I10, the formula evaluates like this:

=SUMPRODUCT(LEN(C10:G10))=0
=SUMPRODUCT({0,0,0,0,0})=0
=0=0
=TRUE

Alternative Formula: Another approach using SUMPRODUCT is to directly check if the cells are not equal to an empty string:

=SUMPRODUCT(--(range<>""))=0

This formula is a bit more literal because it uses the "not equals to" operator (<>). The expression <>"" means "no equal to nothing" or, more concisely, "not empty". In cell I5, the formula evaluates like this:

=SUMPRODUCT(--(C5:G5<>""))=0
=SUMPRODUCT(--({TRUE,TRUE,TRUE,FALSE,TRUE}))=0
=SUMPRODUCT({1,1,1,0,1})=0
=4=0
=FALSE

Notice in the second line above the expression C5:G5<>"" returns an array of five values, one for each cell in the range, where TRUE means not empty, and FALSE means empty. The double negative (--) is then used to convert the TRUE and FALSE values to 1s and 0s and SUMPRODUCT returns a sum. Only when the sum is zero will the formula return TRUE, as in cell I10, where the formula evaluates like this:

=SUMPRODUCT(--(C10:G10<>""))=0
=SUMPRODUCT(--({FALSE,FALSE,FALSE,FALSE,FALSE}))=0
=SUMPRODUCT({0,0,0,0,0})=0
=0=0
=TRUE

Conclusion

There are different ways in Excel to check if all cells in a range are empty, but it's important to understand the difference between blank and empty cells. If you are checking a range that does not contain formulas, the COUNTA function will work fine. If however, you are checking a range that does contain formulas, the SUMPRODUCT options above are more robust.