Excel ADDRESS Function
last modified October 5, 2026
The ADDRESS function creates a cell reference as text, given
specific row and column numbers. This tutorial provides a comprehensive guide
to using the ADDRESS function with detailed examples. You'll learn
basic syntax, practical applications, and advanced techniques to master this
useful Excel function.
ADDRESS Function Basics
The ADDRESS function returns a cell address as text based on
specified row and column numbers. It's particularly useful for creating
dynamic references in formulas. The syntax includes several optional arguments.
| Component | Description |
|---|---|
| Function Name | ADDRESS |
| Syntax | =ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text]) |
| Arguments | row_num, column_num required; others optional |
| Return Value | Text string of cell reference |
row_num and column_num are positive row and column
numbers. abs_num selects the reference style (1 = absolute row
and column, 2 = absolute row, 3 = absolute column, 4 = relative); it defaults
to 1. a1 defaults to TRUE for A1 notation; FALSE selects R1C1
notation. sheet_text optionally adds a worksheet name. Invalid row,
column, or reference-style values return #VALUE!.
Basic ADDRESS Example
This example demonstrates the simplest use of the ADDRESS function with just row and column numbers.
=ADDRESS(5, 3)
This formula creates a reference to cell C5 (row 5, column 3). The result will be "$C$5" in absolute reference format. This shows ADDRESS's core functionality.
ADDRESS with Relative References
The third argument controls reference style. This example shows how to create relative references instead of absolute ones.
| Reference Type | abs_num Value | Example Result |
|---|---|---|
| Absolute | 1 (default) | $A$1 |
| Row absolute | 2 | A$1 |
| Column absolute | 3 | $A1 |
| Relative | 4 | A1 |
The table shows the four address styles selected by abs_num. These
are text addresses; the dollar signs describe how a reference behaves when
Excel uses it in a formula, but ADDRESS itself does not create a live reference.
=ADDRESS(10, 2, 4)
This formula creates a relative reference to cell B10 (row 10, column 2). The result will be "B10" without dollar signs. This is useful for dynamic formulas.
ADDRESS with R1C1 Reference Style
The fourth argument controls A1 vs R1C1 reference style. This example shows R1C1 format output.
| Style | a1 Value | Example Input | Example Output |
|---|---|---|---|
| A1 | TRUE (default) | =ADDRESS(5,3) | $C$5 |
| R1C1 | FALSE | =ADDRESS(5,3,,FALSE) | R5C3 |
This table compares the two reference styles available in Excel. The R1C1 style directly shows row and column numbers, which some users prefer.
=ADDRESS(7, 4, 1, FALSE)
This formula creates an absolute reference to cell D7 in R1C1 format. The result will be "R7C4". This style can be helpful for certain programming tasks.
ADDRESS with Sheet Reference
The fifth argument adds a sheet name to the address. This example demonstrates creating references to other worksheets.
| Sheet Name | Formula | Result |
|---|---|---|
| Data | =ADDRESS(3,5,1,TRUE,"Data") | Data!$E$3 |
| Quarter1 | =ADDRESS(10,2,4,TRUE,"Quarter1") | Quarter1!B10 |
The table shows how sheet names are incorporated into the address string. A
sheet name with spaces or certain punctuation is enclosed in single quotes,
for example, 'Quarter 1'!$E$3. The sheet name is text; the
referenced worksheet must exist when the result is used as a reference.
=ADDRESS(2, 3, 1, TRUE, "Sales")
This formula returns the text Sales!$C$2, an absolute address on
the Sales sheet. ADDRESS returns text, not a live reference; wrap it in
INDIRECT when a formula needs to dereference that text. Such
references are valuable in multi-sheet workbooks.
ADDRESS in Combination with INDIRECT
ADDRESS is often used with INDIRECT to create dynamic cell references. This example shows this powerful combination.
| A | B | C |
|---|---|---|
| 5 | 3 | |
| =INDIRECT(ADDRESS(A1,B1)) |
The table demonstrates how ADDRESS and INDIRECT work together. The formula in C2 will reference whatever cell is specified by the values in A1 (row) and B1 (column).
=INDIRECT(ADDRESS(5, 3))
This formula combination first creates the text "$C$5" with ADDRESS, then INDIRECT converts it to an actual cell reference. The result is the value from cell C5.
Dynamic Range with ADDRESS
ADDRESS can build a dynamic range reference. The formula below can be used in a calculation or as the “Refers to” formula for a defined name, under the stated contiguous-data assumption.
=INDIRECT(ADDRESS(1,1)&":"&ADDRESS(COUNTA(A:A),1))
This builds the text address A1:A<count> and INDIRECT
resolves it as a range. It identifies the last row only when column A contains
one contiguous, non-empty block starting in A1; blanks, a displaced starting
row, or a formula in column A can make COUNTA return the wrong row. For a robust
expandable dataset, use an Excel Table rather than a volatile INDIRECT-based
range.
The ADDRESS function is a powerful tool for creating dynamic cell
references in Excel. From basic address creation to complex multi-sheet
references, ADDRESS handles it all. Mastering its various
applications will significantly improve your ability to build flexible
spreadsheets. Remember that ADDRESS returns text strings that
often need INDIRECT to become active references.
Author
List all Excel Formulas.