Exceljet

Quick, clean, and to the point

Excel ADDRESS Function

Excel ADDRESS function
Summary 

The Excel ADDRESS function returns the address for a cell based on a given row and column number. For example, =ADDRESS(1,1) returns $A$1. ADDRESS can return an address in relative, mixed, or absolute format, and can be used to construct a cell reference inside a formula.

Purpose 
Create a cell address from a row and column number
Return value 
A cell address in the current or given worksheet.
Syntax 
=ADDRESS (row_num, col_num, [abs_num], [a1], [sheet])
Arguments 
  • row_num - The row number to use in the cell address.
  • col_num - The column number to use in the cell address.
  • abs_num - [optional] The address type (i.e. absolute, relative). Defaults to absolute.
  • a1 - [optional] The reference style, A1 vs R1C1. Defaults to A1 style.
  • sheet - [optional] The name of the worksheet to use. Defaults to current sheet.
Version 
Usage notes 

The ADDRESS function returns the address for a cell based on a given row and column number. For example, =ADDRESS(1,1) returns $A$1. ADDRESS can return a relative, mixed, or absolute reference, and can be used to construct a cell reference inside a formula. It's important to understand that the ADDRESS returns a reference as a text value. If you want to use this text inside a formula reference, you will need to coerce the text to a proper reference with the INDIRECT function. If you want to provide a row and column number and get back the value at that address, use the INDEX function.

The ADDRESS function takes five arguments: row, column, abs_num, a1, and sheet_text. Row and column are required, other arguments are optional. The abs_num argument controls whether the address returned is relative, mixed, or absolute, with a default value of 1 for absolute. The a1 argument is a Boolean that toggles between A1 and R1C1 style references with a default value of TRUE for A1 style references. Finally, the sheet_text argument is meant to hold a sheet name that will be prepended to the address.

ABS options

The table below shows the options available for the abs_num argument for returning a relative, mixed, or absolute address. 

abs_num Result
1 (or omitted) Absolute ($A$1)
2 Absolute row, relative column (A$1)
3 Relative row, absolute column ($A1)
4 Relative (A1)

Examples

Use ADDRESS to create an address from a given row and column number. For example:

=ADDRESS(1,1) // returns $A$1
=ADDRESS(1,1,4) // returns A1
=ADDRESS(100,26,4) // returns Z100
=ADDRESS(1,1,1,FALSE) // R1C1
=ADDRESS(1,1,4,TRUE,"Sheet1") // returns Sheet1!A1

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.