ADDRESS Function Excel

This Excel Tutorial demonstrates how to use the Excel ADDRESS Function in Excel to return a cell address as text, with formula examples.

ADDRESS Function Description:

The ADDRESS Function Returns a cell address as text.

Formula Examples:

address function examples

Example Formula Result
Absolute =ADDRESS(C5,D5,E5,F5) $B$4
Absolute row/relative column =ADDRESS(C6,D6,E6,F6) B$4
Relative row/absolute column =ADDRESS(C7,D7,E7,F7) $B4
Relative =ADDRESS(C8,D8,E8,F8) B4
R1C1 Mode =ADDRESS(C9,D9,E9,F9) R4C2
Alternate Sheet =ADDRESS(C10,D10,E10,F10,G10) sheet1!B4
Use Indirect To Return Cell Value =INDIRECT(ADDRESS(C9,D9,E9,F9)) Example

Syntax and Arguments:

The Syntax for the ADDRESS Formula is:

Function Arguments ( Inputs ):

row_num – The row number for the reference. Example: 5 for row 5.

col_num – The column number for the reference. Example: 5 for Column E. You can not enter “E” for column E

abs_num – OPTiONAL. A number representing if the reference should have absolute or relative row/column references. 1 for absolute. 2 for absolute row/relative column. 3 for relative row/absolute column. 4 for relative.

a1 – OPTiONAL. A number indicating whether to use standard (A1) cell reference format or R1C1 format. 1/TRUE for Standard (Default). 0/FALSE for R1C1.


Additional Notes

Use the ADDRESS Function to generate an address from a given row and column number. Important: You must enter the column number. Entering the column letter will generate an error. If needed, you can use the Column Function to calculate the column number for a cell reference.

Abs_num allows you to toggle the absolute and relative cell references.
<> 1,2,3,4 a1, $a$2… relative/absolute etc…

Then indicate whether to use a1 or R1C1. a1 mode is the standard mode where cells are referenced by their column letter & row number (ex. a4). R1C1 mode is where cells are referenced by their row and column numbers (ex. R4C1). a1 is the default mode. Use this unless you have a good reason not to.

In the last arguement you can enter a sheet name if the cell reference will be on another worksheet. Type the sheet name surrounded by paranthesis (ex “sheet3”).

Return to the List of all Functions in Excel

How to use the ADDRESS Function in Excel:

To use the AND Excel Worksheet Function, type the following into a cell:
After entering it in the cell, notice how the AND formula inputs appear below the cell:
address formula syntax
You will need to enter these inputs into the function. The function inputs are covered in more detail in the next section. However, if you ever need more help with the function, after typing “=ADDRESS(” into a cell, without leaving the cell, use the shortcut CTRL + A (A for Arguments) to open the “Insert Function Dialog Box” for detailed instructions:
how to use the address function in excel

For more information about the ADDRESS Formula visit the
Microsoft Website.

Posted in