ADDRESS functions
ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])
Returns a reference as text to a single cell in a worksheet
To create a cell reference in Excel, you can type the column and row coordinates manually. Alternatively, you can get an Excel cell address from the row and column numbers supplied to the ADDRESS function. Almost pointless on its own, in combination with other functions this technique can be the only solution in situations when it is not possible to refer to a cell directly.
Excel ADDRESS function - syntax and basic uses
The ADDRESS function is designed to get a cell address in Excel based on the specified row and column numbers. A cell address is returned as a text string, not an actual reference.
The function is available in all versions of Excel for Microsoft 365 - Excel 2007.
The syntax of the ADDRESS function is as follows:
ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])
The first two arguments are required:
row_num - the row number to use in the cell reference.
column_num - the column number to build the cell reference.
The last three arguments, which specify the cell reference format, are optional:
abs_num - the reference type, absolute or relative. It can take any of the below numbers; the default is absolute.
- 1 or omitted - absolute cell reference like $A$1
- 2 - mixed reference: relative column and absolute row like A$1
- 3 - mixed reference: absolute column and relative row like $A1
- 4 - relative cell reference such as A1
a1 - the reference style, A1 or R1C1. If omitted, the default A1 style is used.
- 1 or TRUE or omitted - returns the cell address in the A1 reference style where columns are letters and rows are numbers.
- 0 or FALSE - returns the cell address in the R1C1 reference style where rows and columns are represented by numbers.
sheet_text - the name of the worksheet to include in the external reference. The sheet name should be supplied as a text string and enclosed in quotation marks, e.g. "Sheet2". If omitted, no worksheet name is used, and the address defaults to the current sheet.
For example:
=ADDRESS(1,1) - returns the address of the first cell (i.e. the cell at the intersection of the first row and first column) as an absolute cell reference $A$1.
=ADDRESS(1,1,4) - returns the address of the first cell as a relative cell reference A1.
In the following table, you will find a few more reference types that can be returned by ADDRESS formulas.
Formula | Result | Description |
=ADDRESS(1,2) | $B$1 | Absolute cell reference |
=ADDRESS(1,2,4) | B1 | Relative cell reference |
=ADDRESS(1,2,2) | B$1 | Relative column and absolute row |
=ADDRESS(1,2,3) | $B1 | Absolute column and relative row |
=ADDRESS(1,2,1,FALSE) | R1C2 | Absolute reference in the R1C1 style |
=ADDRESS(1,2,4,FALSE) | R[1]C[2] | Relative reference in the R1C1 style |
=ADDRESS(1,2,1,,"Sheet2") | Sheet2!$B$1 | Absolute reference to another sheet |
=ADDRESS(1,2,4,,"Sheet2") | Sheet2!B1 | Relative reference to another sheet |
How to use ADDRESS function in Excel - formula examples
The below examples show how to use the ADDRESS function inside bigger formulas to accomplish more difficult tasks.