Exceljet

Quick, clean, and to the point

All cells in range are blank

Excel formula: All cells in range are blank
Generic formula 
=SUMPRODUCT(--(range<>""))=0
Explanation 

To return TRUE when all cells in a range are blank or empty, you can use a formula based on SUMPRODUCT and a logical test that suits the use case.

In the example shown, the formula in E5 is:

=SUMPRODUCT(--(B5:D5<>""))=0

How this formula works

Working from the inside out, this formula contains an expression inside SUMPRODUCT that tests each cell in a range like so:

--(B5:D5<>"")

Inside the parentheses, the result of B5:D5<>"" looks like this:

{TRUE,FALSE,TRUE}

The double hyphen then converts the TRUE FALSE values to one's and zeros:

{1,0,1}

Note that the 1's in this array correspond to cells that are not blank or empty. Then, with only one array to work with, SUMPRODUCT simply multiples these values together and returns the result.

Any time the result is greater than zero, we know that not every cell in the range is blank. To force the formula to return TRUE if every cell is blank, and FALSE if not, we simply add =0 to the end of the formula.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.