The Excel ADDRESS Function

Basic Description

The Excel Address function takes a row and a column number and returns a cell reference as a text string.

The syntax of the function is:

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

where the arguments are as follows:

row_num-The row number to use in the reference.
column_num-The column number to use in the reference.
[abs_num]-

An optional argument, which specifies whether the reference should contain absolute or relative references. This can be any the following values:

1   -   absolute row & absolute column reference;

2   -   absolute row & relative column reference;

3   -   relative row & absolute column reference;

4   -   relative row & relative column reference.

If the [abs_num] argument is omitted, it takes on the default value 1 (absolute references).

[a1]-

An optional logical argument that defines the reference style of the returned reference.

This can be either:

True   -   the reference should be in A1 style;

False   -   the reference should be in R1C1 style.

If this argument is omitted, it takes on the default value "True" (A1 style).

[sheet_text]-

An optional text argument that specifies the sheet name.

If this argument is omitted, the sheet name is blank (i.e. the reference relates to the current worksheet)



Address Function Examples

The following spreadsheets show simple examples of the Excel Address function.

 Formulas:
 A
1=ADDRESS( 1, 1 )
2=ADDRESS( 1, 1, 3 )
3=ADDRESS( 1, 1, 1, FALSE )
4=ADDRESS( 1, 1, 4, , "Sheet2" )
5=ADDRESS( ROW(), COLUMN() )
 Results:
 A
1$A$1
2$A1
3R1C1
4Sheet2!A1
5$A$5

Note that, in the above spreadsheet:


Further details and examples of the Excel Address function are provided on the Microsoft Office website.


Address Function Error

If you get an error from the Excel Address function, this is likely to be the #VALUE! error:

Common Error
#VALUE!-

Occurs if the supplied arguments are invalid.

I.e. if either:

  • The row_num is less than 1 or greater than the number of rows in the spreadsheet;
  • The column_num is less than 1 or greater than the number of columns in the spreadsheet;
  • Any of the supplied row_num, column_num or [abs_num] arguments are non-numeric or the supplied [a1] argument is not recognised as a logical value.