Quick Navigation
ADDRESS Function
Summary
The Excel ADDRESS function dynamically generates cell references as text strings based on specified row and column numbers. This powerful function enables flexible reference creation for use in other formulas, INDIRECT lookups, and dynamic worksheet navigation.
Syntax
ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])Parameters
| Parameter | Type | Required | Description |
|---|---|---|---|
| row_num | Number |
Yes | Row number for the cell reference (must be positive integer) |
| column_num | Number |
Yes | Column number for the cell reference (1-16384 in modern Excel) |
| abs_num | Number |
No | Reference type: 1=Absolute ($A$1), 2=Abs row/rel col (A$1), 3=Rel row/abs col ($A1), 4=Relative (A1) |
| a1 | Boolean |
No | TRUE (default) for A1 style, FALSE for R1C1 style references |
| sheet_text | Text |
No | Worksheet name to include in external reference (e.g., 'Sheet2'!A1) |
Using the ADDRESS Function
ADDRESS is essential for creating dynamic cell references that can be combined with INDIRECT for flexible formula construction. Use it to generate references based on calculations, create navigation formulas, or build complex lookup systems.
Common ADDRESS Examples
Basic Absolute Reference
=ADDRESS(2,3)Returns $C$2 - fully absolute reference to row 2, column C
Mixed Reference
=ADDRESS(2,3,2)Returns C$2 - absolute row, relative column
R1C1 Style
=ADDRESS(2,3,2,FALSE)Returns R2C[3] - R1C1 notation with relative column
Sheet Reference
=ADDRESS(1,1,,,"Sheet2")Returns Sheet2!$A$1 - external reference to another worksheet
Dynamic with ROW/COLUMN
=ADDRESS(ROW(),COLUMN())Returns address of current cell (e.g., $D$5 if in D5)
Frequently Asked Questions
Common Errors and Solutions
#VALUE! Error
Cause: Non-numeric row_num or column_num
Solution: Ensure both are valid positive numbers
#NUM! Error
Cause: row_num ≤ 0, column_num ≤ 0 or >16,384, or invalid abs_num (not 1-4)
Solution: Use valid ranges: rows 1+, columns 1-16384, abs_num 1-4
#REF! Error
Cause: Invalid sheet_text reference
Solution: Verify sheet name exists and format is correct
Notes
- Maximum column is XFD (16,384)
- abs_num 1 or omitted = absolute ($A$1)
- Perfect companion for INDIRECT function
- R1C1 style useful for certain macro programming
Compatibility
Available in: Excel 2007, Excel 2010, Excel 2013, Excel 2016, Excel 2019, Excel 2021, Microsoft 365
Not available in:
Content last reviewed: December 9, 2025
Update frequency: As needed
Excel versions tested: Excel 2007+