The Excel ADDRESS function builds a cell address as text from a row number and a column number: feed it 5 and 3 and it hands back "$C$5". On its own it just makes strings — the payoff comes when those strings answer “where is my data?” (the cell holding the maximum, say) or get passed to INDIRECT to become live references, including references to other sheets.
Syntax
| Argument | Description | |
|---|---|---|
row_num | Required | The row number for the address. |
column_num | Required | The column number (A = 1, B = 2, …). |
abs_num | Optional | Reference style: 1 = absolute $A$1 (default) · 2 = row absolute A$1 · 3 = column absolute $A1 · 4 = relative A1. |
a1 | Optional | TRUE (default) = A1 style; FALSE = R1C1 style like R5C3. |
sheet_text | Optional | A sheet name to prepend: "Q1 Data" produces 'Q1 Data'!$C$5. |
ADDRESS returns text, not a reference. "$C$5" is just a string — you can’t SUM it. To turn it into a live reference, wrap it in INDIRECT.
Building addresses, four $ styles at a time
The first two arguments place the cell; abs_num chooses where the dollar signs go:
Add a sheet name (and ADDRESS quotes it for you when it contains spaces):
Set a1 to FALSE for R1C1 style, used by some macros and legacy systems:
Try it: interactive ADDRESS demo
Dial in a row, a column, and an abs_num style and watch the address string assemble itself.
Practical uses: locating values and feeding INDIRECT
1. Where is the maximum? MAX tells you the biggest value; ADDRESS + MATCH tells you where it lives — gold for auditing and dashboards:
| A | B | D | E | ||
|---|---|---|---|---|---|
| 1 | Day | Sales | Question | Answer | |
| 2 | Mon | $4,150 | Where is the peak? | $B$4 | |
| 3 | Tue | $3,890 | |||
| 4 | Wed | $5,720 | |||
| 5 | Thu | $4,460 | |||
| 6 | Fri | $5,010 |
2. Build a reference, then make it live. ADDRESS assembles the string; INDIRECT dereferences it. Pull a value from whichever sheet is named in A1:
3. Document your workbook. Combined with ROW and COLUMN, ADDRESS labels a cell with its own location — useful in templates and error messages:
Reach for INDEX first. If you only need the value at a computed position, INDEX does it directly, stays non-volatile, and skips the text round-trip. ADDRESS earns its keep when you need the address itself, a sheet name baked in, or R1C1 strings.
Errors & common pitfalls
#VALUE! — bad position numbers. row_num or column_num is zero, negative, or beyond the grid (1,048,576 rows / 16,384 columns), or abs_num is outside 1–4.
Pitfall: it’s only text. =SUM(ADDRESS(5,3)) does not sum C5 — it errors, because ADDRESS produced a string. Wrap in INDIRECT to act on the cell itself.
Pitfall: abs_num 2 vs 3 confusion. 2 locks the row (C$5), 3 locks the column ($C5). The mnemonic: the codes count dollars from the right — and when in doubt, the demo above settles it.
Pitfall: INDIRECT downstream is volatile and fragile. Whatever ADDRESS builds, INDIRECT recalculates on every sheet change and breaks if the target sheet is renamed. Fine for occasional lookups; avoid in thousand-row blocks.
Practice workbook
Frequently asked questions
How do I get the value at the address ADDRESS returns?
=INDIRECT(ADDRESS(5, 3)) returns the value in C5. But if a plain position lookup is all you need, =INDEX(A:Z, 5, 3) is simpler and recalculates less.What do the abs_num codes 1, 2, 3, 4 mean?
How do I get a cell's own address?
How do I extract just the column letter from a column number?
=SUBSTITUTE(ADDRESS(1, 27, 4), "1", "") returns "AA". The abs_num 4 keeps dollar signs out of the way.Can ADDRESS point at another sheet or workbook?
=ADDRESS(5, 3, 1, TRUE, "Q1 Data") gives 'Q1 Data'!$C$5, and INDIRECT can dereference it while that workbook is open. For closed workbooks INDIRECT fails, so cross-file links should be written as ordinary references instead.Master functions like this in one day
This page covers one function. Our Excel Formulas and Functions class covers the 30 that matter most — live, hands-on, taught by professionals in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class