Microsoft Excel Functions & Formulas

Use the ADDRESS, MAX, and ROW functions to

determine the last used cell

With this tip, we learn the definition of an array formula. Here, we

want to determine the last used cell in a range and shade it. Com-

bine the ADDRESS, MAX, and ROW functions as described below

to get the desired result.

4

To determine the last used cell in a range and shade it:

1. In column A list any kind of numbers.

2. Select cell B2 and type the following array formula:

=ADDRESS(MAX((A2:A100<>"")*ROW(A2:A100)),1).

3. Press <Ctrl+Shift+Enter>.

4. Select cells A2:A11.

5. From the Format menu, select Conditional Formatting.

6. Select Formula Is and type the following formula:

=ADDRESS(ROW(),1)=$B$2 ...