Skip to main content

Excel ADDRESS function

Author: Sun Last Modified: 2019-07-09

doc address function 1


Syntax and Arguments

Usage and Examples


The ADDRESS function returns the cell address reference as text, based on the given column number and row number. For example, the formula =ADDRESS(1,1) returns $A$1. The ADDRESS function can return relative or absolute address, and, return in A1 or R1C1 style, also, the sheet name can be included in the result.

syntax and arguments

Formula syntax

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


  • Array: Required, a range of cells or an array of constants you retrieve from. If the array argument is a column of cells, row_num is required, if array is a row of cells, col_num is required.
  • Row_num: Required. A value that indicates the row number to use in the cell reference.
  • Col_num: Required. A value that indicates the column number to use in the cell reference.
  • Abs_num: Optional. A numeric value that decides the type of reference to return.
  • A1: Optional. A logical value that specifies reference style in A1 or R1C1.
  • Sheet_text: Optional. The name of worksheet to use, and it will refer to current sheet if omitted. For example, ADDRESS(1,1,,,”Sheet2”), returns Sheet2!$A$1.


Abs_num argument
The type of reference Example
1 or omitted Absolute row and column $A$1
2 Relative row, absolute column A$1
3 Absolute row, relative column $A1
4 Relative row and column A1
A1 argument Style Description
1 or TRUE or omitted A1 Columns are labeled alphabetically, and rows are labeled numerically
0 or False R1C1 Both columns and rows are labeled numerically

Return Value:

ADDRESS function returns a cell reference as text.

Usage and Examples

Here some examples are provided to explain how to use the INDEX function.

Example 1-Basic usage: Obtain cell address from given column and row

1) If you just enter the row and column arguments in the ADDRESS function,
A2 and B2 are the numeric values of row and column, and it returns
doc address function 2

2) If you enter the row, column and abs arguments in the ADDRESS function,
C3 is the abs arguments, 2 indicates to display reference as relative row and absolute column, and it returns
doc address function 3

3) If the fourth argument is entered in the ADDRESS function,
D4 controls the reference style, A1 or R1C1, 0 or False will display result in A1 style, 1 or True will show result in R1C1 style, here it returns
doc address function 4

4) If all the arguments are entered in the ADDRESS function,
E6 is the fifth argument which indicates the sheet refer to, it returns
doc address function 5

Example 2–Cell value from row and column number

The ADDRESS function returns the cell address as text, if you want to show the cell value in the cell address, you can to combine the ADDRESS function and the INDIRECT function to achieve this goal.

Here is a formula in B4 which will obtain the cell value in B1.

doc address function 6

Example 3–Get address of max value

In this case, I introduce how to use the ADDRESS function to get the cell address of max value.

Firstly, you need to get the max value with this formula =MAX(B2:B6).
doc address function 7

Then use the formula


MATCH(E1,B1:B6,0) will find the row number, E1 is the max value, B1:B6 is the column you find max value from;

COLUMN(B1) will find the column number, B1 is the column you find value from.
doc address function 8

Note: This formula can only find the max value in one column.

Example 4–Return column letter based on column number

In this example, I will introduce how to use the ADDRESS function to return column letter based on a given column number.

For example, you want to get the column letter for the 29th column, use below formula:


What the arguments mean:

ADDRESS function: 1 is row 1, A3 is the column number whose relative column letter you want to get, 4 is abs argument which returns reference in relative, in this part, the ADDRESS function gets result AC1;

SUBSTITUTE function: replace 1 with empty string, so the final result is
doc address function 9

If you want to get the column letter of current cell, you can use this formula

doc address function 10

Sample File
doc sample

The Best Office Productivity Tools

Kutools for Excel - Helps You To Stand Out From Crowd

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates  |  Delete Blank Rows  |  Combine Columns or Cells without Losing Data  |  Round without Formula ...
Super VLookup: Multiple Criteria  |  Multiple Value  |  Across Multi-Sheets  |  Fuzzy Lookup...
Adv. Drop-down List: Easy Drop Down List  |  Dependent Drop Down List  |  Multi-select Drop Down List...
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  Compare Columns to Select Same & Different Cells ...
Featured Features: Grid Focus  |  Design View  |  Big Formula Bar  |  Workbook & Sheet Manager | Resource Library (Auto Text)  |  Date Picker  |  Combine Worksheets  |  Encrypt/Decrypt Cells  |  Send Emails by List  |  Super Filter  |  Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters ...)  |  50+ Chart Types (Gantt Chart ...)  |  40+ Practical Formulas (Calculate age based on birthday ...)  |  19 Insertion Tools (Insert QR Code, Insert Picture from Path ...)  |  12 Conversion Tools (Numbers to Words, Currency Conversion ...)  |  7 Merge & Split Tools (Advanced Combine Rows, Split Excel Cells ...)  |  ... and more

Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...


Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)

  • One second to switch between dozens of open documents!
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Edge and Firefox.
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Leave your comments
Posting as Guest
Rate this post:
0   Characters
Suggested Locations