Explanation
The ADDRESS function creates a reference based on a given a row and column number. In this case, we want to get the last row and the last column used by the named range data (B5:D14).
To get the last row used, we use the ROW function together with the MAX function like this:
MAX(ROW(data))
Because data contains more than one row, ROW returns an array of row numbers:
{5;6;7;8;9;10;11;12;13;14}
This array goes directly to the MAX function, which returns the largest number:
MAX({5;6;7;8;9;10;11;12;13;14}) // returns 14
To get the last column, we use the COLUMN function in the same way:
MAX(COLUMN(data))
Since data contains three rows, COLUMN returns an array with three column numbers:
{2,3,4}
and the MAX function again returns the largest number:
MAX({2,3,4}) // returns 4
Both results are returned directly to the ADDRESS function, which constructs a reference to the cell at row 14, column 4:
=ADDRESS(14,4) // returns $D$14
If you want a relative address instead of an absolute reference , you can supply 4 for the third argument like this:
=ADDRESS(MAX(ROW(data)),MAX(COLUMN(data)),4) // returns D14
CELL function alternative
Although it’s not obvious, the INDEX function returns a reference, so we can use the CELL function with INDEX to get the address of the last cell in a range like this:
=CELL("address",INDEX(data,ROWS(data),COLUMNS(data)))
In this case, we use the INDEX function to get a reference to the last cell in the range, which we determine by passing total rows and total columns for the range data into INDEX. We get total rows with the ROWS function , and total columns with the COLUMNS function :
ROWS(data) // returns 10
COLUMNS(data) // returns 3
With the array provided as data, INDEX then returns a reference to cell D14:
INDEX(data,10,3) // returns reference to D14
We then use the CELL function with “address”, to display the address.
Note: The CELL function is a volatile function which can cause performance problems in large or complex workbooks.
Explanation
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.
Blank vs. Empty Cells - In Excel, there is a distinction between blank and empty cells. A cell can visually appear blank but might contain a formula that returns an empty string (""). Such cells are technically not empty, as they contain a formula. If you need to treat formulas that return "" as empty, see the SUMPRODUCT option below.
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.
