Excel Office

Excel How Tos, Tutorials, Tips & Tricks, Shortcuts

How to get address of first cell in range in Excel

To get the address of the first cell in a named range, you can use the ADDRESS function together with the ROW and COLUMN functions. See example below;

Note: The ADDRESS function builds an address based on a row and column number.




In the example shown, the formula in G5 is:


How this formula works

Working from the inside out, the ROW function returns an array of absolute* row numbers for a given range, and the COLUMN function returns an array of column numbers.

Also See:   Get nth match in Excel

The ADDRESS function then assembles and returns an array of addresses. When entered in a single cell, just the item from the array is displayed, which is the address corresponding to the first cell in a range.

* By absolute, I mean numbers that track to the entire worksheet, not numbers relative to the range itself.

Also See:   Shade alternating groups of n rows in Excel

CELL function alternative

Note that the the CELL function can be¬†volatile. If you add it to a worksheet, you’ll notice that you are prompted to save even when you’ve made no changes.

You can also use the CELL function to get the address of the first cell in a range with this formula:


In this case, we just pass the range into the CELL function with “address”, which returns the address of the first cell in the range.

Also See:   With vs Without Array Formula in Excel

Leave a Reply

Your email address will not be published. Required fields are marked *