Lookup & Reference
Get the Address of the First and Last Cell in a Range (ADDRESS + ROWS + COLUMNS)
Compute the exact A1 coordinates of a range's top-left anchor, bottom-right corner, and full "$B$3:$D$6" span text for audit logs and dynamic range documentation.
Why bottom-right row is ROW(rng) + ROWS(rng) - 1 (Fencepost Rule)
Given a rectangular range B3:D6, ROW(B3) returns the starting row (3) and ROWS(B3:D6) returns the height (4 rows: 3, 4, 5, 6). Adding them directly gives 7—one row past the bottom—because the starting row is counted twice.
Subtracting 1 on both axes—ROW(B3) + ROWS(B3:D6) - 1 and COLUMN(B3) + COLUMNS(B3:D6) - 1—pinpoints row 6, column 4, which ADDRESS(6, 4) formats as "$D$6" (or relative "D6" when abs_num = 4).
| Range Property | Coordinate Formula | Result on B3:D6 |
|---|---|---|
| Top-left anchor (absolute) | ADDRESS(ROW(B3), COLUMN(B3)) | "$B$3" |
| Bottom-right corner (absolute) | ADDRESS(ROW(B3)+ROWS(B3:D6)-1, COLUMN(B3)+COLUMNS(B3:D6)-1) | "$D$6" |
| Relative A1 span string | ADDRESS(...,4) & ":" & ADDRESS(...,4) | "B3:D6" |
| Total cell capacity | ROWS(B3:D6) * COLUMNS(B3:D6) | 12 |
Bottom-Right Cell Address & Full Span Formula
Use ADDRESS with abs_num = 1 (default) for locked "$B$3:$D$6" audit strings or abs_num = 4 for relative "B3:D6" references.
=ADDRESS(ROW(B3) + ROWS(B3:D6) - 1, COLUMN(B3) + COLUMNS(B3:D6) - 1)Try the example
Use the original values or change them to test your understanding.