Question

The cell address for the last cell in MS Excell 2010is ____​

Answer 1

Explanation
To get the address of the last cell in a range, you can use the ADDRESS function together with ROW, COLUMN, and MAX functions. In the example shown, the formula in F5 is:

=ADDRESS(MAX(ROW(data)),MAX(COLUMN(data)))
where data is the named range B5:D14.

How this formula works

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