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.

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.

Syntax 

=ADDRESS(row_num, col_num, [abs_num], [a1], [sheet])

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
Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.