Please note javascript is required for full website functionality.
MVP

Blog

A to Z of Excel Functions: the ADDRESS Function

8 July 2016

Welcome back to our regular A to Z of Excel Functions Blog. Today we look at the ADDRESS Function. 

The ADDRESS function

The ADDRESS function is used to obtain the address of a cell in a worksheet, given specified row and column numbers.  For example, =ADDRESS(3,4) returns $D$3.  This is easier than deriving the address using a convoluted formula with the COLUMN and ROW functions.

The syntax is as follows:

=ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])

Before you start playing around with this function, it is worth noting how the arguments are defined:

  • row_num: a numeric value that specifies the row number to use in the reference
  • column_num:  a numeric value that specifies the column number to use in the cell reference
  • abs_num: a numeric value that specifies the type of reference to return
  • a1: this is a logical value that specifies the A1 or R1C1 reference style.  Columns are alphabetically labelled and rows are numerically labelled.  In the R1C1 reference style, both columns and rows are labelled numerically.  If the A1 argument is TRUE or omitted, the ADDRESS function returns an A1-style reference; if FALSE, the ADDRESS function returns an R1C1-style reference
  • sheet_text: this is optional, and is a text value that specifies the name of the worksheet to be used as the external reference.  For example, the formula =ADDRESS(1,1,,,"Sheet2") returns Sheet2!$A$1.  If the sheet_text argument is omitted, no sheet name is used and the address returned by the function refers to a cell on the current sheet

Please see my example below:

 

We’ll continue our A to Z of Excel Functions soon.  Keep checking back – there’s a new blog post every other business day.

Newsletter