Excel ADDRESS Function — Build a Cell's Address as Text (Not Its Value)

TL;DR — ADDRESS takes a row number and a column number and returns the text of that cell's address. Syntax: =ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text]). =ADDRESS(1, 1) returns the string "$A$1" — not the contents of A1. It's a label builder, not a value fetcher. The abs_num argument controls the $ locking (1=$A$1, 4=A1); sheet_text prepends a sheet name. Unlike INDIRECT and OFFSET, ADDRESS is not volatile — it's pure text arithmetic. To actually read the cell it names, wrap it: =INDIRECT(ADDRESS(...)).

=ADDRESS(3, 2)                       ' -> "$B$3"  (text, not the value in B3)
=ADDRESS(3, 2, 4)                    ' -> "B3"    (relative, no $ locks)
=ADDRESS(MATCH(x, A:A, 0), 1)        ' -> the address of where x sits in column A

ADDRESS is the odd one out in the reference-building family. INDIRECT turns text into a reference; OFFSET moves to a reference; ADDRESS goes the other way — it produces the text of an address from plain row and column numbers. That makes it niche, and it's routinely misunderstood by people expecting it to return a value. This guide clears that up, shows the $-locking argument nobody remembers, and is honest about the handful of jobs where it actually earns a spot.

What you'll learn

The mental model: numbers in, an address string out (the reverse of a read)
The #1 misunderstanding: it returns text, not the cell's value
The abs_num argument — controlling $A$1 vs A$1 vs $A1 vs A1
Why ADDRESS is not volatile (and why that's a nice contrast)
The real jobs: reporting where a value is, and feeding INDIRECT
The honest judgment call: when you almost never need ADDRESS at all

The mental model: row and column numbers in, a label out

Every function so far has read a cell. ADDRESS does something narrower and more mechanical: you give it a row number and a column number, and it assembles the text that describes that position:

=ADDRESS(1, 1)     ' -> "$A$1"
=ADDRESS(10, 3)    ' -> "$C$10"
=ADDRESS(5, 27)    ' -> "$AA$5"   (column 27 = AA — it handles the letters for you)

The one genuinely useful thing it saves you is that column-number-to-letter conversion: knowing that column 27 is AA or column 53 is BA is fiddly to do by hand, and ADDRESS just does it. Beyond that, think of it as a string generator that speaks Excel's address grammar — nothing more.

The misunderstanding that trips up everyone

Read this once and you'll never lose an afternoon to it: ADDRESS returns text, not a value. =ADDRESS(1,1) gives you the six characters "$A$1", the same as if you'd typed them into a cell. It does not look inside A1:

=ADDRESS(1, 1)              ' -> "$A$1"   (a string)
=INDIRECT(ADDRESS(1, 1))    ' -> the actual contents of A1

To turn the address ADDRESS builds into the thing it points at, you have to hand it to INDIRECT, which is the function that turns text into a live reference. That pairing — INDIRECT(ADDRESS(...)) — is the single most common reason ADDRESS appears in a formula at all. If you catch yourself expecting ADDRESS to fetch data, you want a different function.

The abs_num argument: which parts get locked

The third argument, abs_num, decides how many $ signs land in the string — the same absolute/relative locking you get by pressing F4 on a reference:

=ADDRESS(3, 2, 1)   ' -> "$B$3"   (1 = fully absolute — the default)
=ADDRESS(3, 2, 2)   ' -> "B$3"    (2 = row locked, column relative)
=ADDRESS(3, 2, 3)   ' -> "$B3"    (3 = column locked, row relative)
=ADDRESS(3, 2, 4)   ' -> "B3"     (4 = fully relative)

Two more optional arguments finish the string: a1 (default TRUE for A1-style; FALSE gives R1C1 like "R3C2"), and sheet_text, which prepends a sheet name — =ADDRESS(3, 2, 1, TRUE, "Sheet2") returns "Sheet2!$B$3". That sheet-qualified form is exactly the kind of string you'd then feed to INDIRECT.

Why ADDRESS is NOT volatile (and why that matters)

Here's the clean contrast that ties the reference-building family together. INDIRECT and OFFSET are volatile — they recalc on every edit because Excel can't predict what they'll resolve to. ADDRESS is not volatile at all. It never touches a live reference; it just does arithmetic on numbers and returns characters. =ADDRESS(5, 3) is "$C$5" and always will be, regardless of what's in the workbook.

The practical takeaway: ADDRESS on its own is free — you can use as many as you like with no performance cost. The volatility only enters when you wrap it in INDIRECT to read the cell. So if you only need the label — a report that says "the maximum is in cell $C$5" — ADDRESS alone stays cheap and static.

The real jobs ADDRESS is good at

Two patterns justify it, plus one honest "and that's about it":

' 1) Report WHERE a value is, not just what it is (pair with MATCH)
=ADDRESS(MATCH(MAX(B:B), B:B, 0), 2)   ' -> address of the largest value in B

' 2) Build a sheet-qualified reference string to hand to INDIRECT
=INDIRECT(ADDRESS(row, col, 1, TRUE, SheetName))

"Which cell holds this?" — MATCH gives you the position (a number); ADDRESS turns that position into a human-readable address. Useful in audit and QA sheets that need to point a person at a location: "largest variance: cell $C$5."
Dynamic reference strings for INDIRECT — when you're already computing row and column numbers, ADDRESS assembles the exact text INDIRECT needs, including the $ locking and sheet name.

The judgment call: you probably don't need ADDRESS

The honest take: ADDRESS is the most skippable of the three. In modern Excel, the questions people reach for it to answer usually have cleaner answers:

"Get me the value at this position" → INDEX or XLOOKUP return the value directly, with no text-then-INDIRECT round trip and no volatility.
"Where is this value?" → often MATCH alone (the row number) is all a formula downstream actually needs; the human-readable $C$5 is only worth building when a person has to read it.
"Convert a column number to a letter" → this is the one small thing ADDRESS does uniquely well, usually via =SUBSTITUTE(ADDRESS(1, col, 4), "1", "") to strip the row.

So use ADDRESS when you specifically need an address as text — for a report a human reads, or as the string half of an INDIRECT reference. For everything else, INDEX/XLOOKUP/MATCH get you there without the detour through a string. Knowing that keeps you from building a fragile INDIRECT(ADDRESS(...)) tower where a single INDEX would do.

How ExcelMaster helps

ADDRESS shows up in formulas that are usually one layer too clever — an INDIRECT(ADDRESS(MATCH(...))) stack where an INDEX or XLOOKUP would read straight through. ExcelMaster recognises the intent — find the value, report which cell it's in, read from a computed position — and writes the direct version: XLOOKUP/INDEX when you want the value, ADDRESS with MATCH only when you genuinely need the address as a label. You end up with the shorter, non-volatile formula the next person can actually read.

Frequently asked questions

What does the ADDRESS function return in Excel?

The text of a cell address built from a row number and a column number. =ADDRESS(1,1) returns the string "$A$1" — not the value in A1. To read the cell it names, wrap it in INDIRECT: =INDIRECT(ADDRESS(1,1)).

How do I get the value at an address from ADDRESS?

ADDRESS only builds the text; INDIRECT turns that text into a live reference. Combine them: =INDIRECT(ADDRESS(row, col)). On modern Excel, though, INDEX(range, row, col) usually gets the value directly with no round trip and no volatility.

What does the abs_num argument do?

It controls the $ locking in the returned string: 1 = $A$1 (absolute, default), 2 = A$1 (row locked), 3 = $A1 (column locked), 4 = A1 (relative). It mirrors what pressing F4 does to a reference.

Is ADDRESS volatile like INDIRECT and OFFSET?

No. ADDRESS is not volatile — it only does arithmetic on numbers and returns text, so it never triggers extra recalculation. Volatility only appears when you wrap it in INDIRECT to actually read the cell.

How do I find the cell address of a value?

Pair ADDRESS with MATCH: =ADDRESS(MATCH(value, A:A, 0), 1) returns the address of value in column A. If a downstream formula only needs the position (not a human-readable address), MATCH on its own is enough.

Tested in

Tested in: Excel 365 (Windows 11) — last verified 2026-07-01.

Related guides: Excel INDIRECT · Excel OFFSET · Excel XLOOKUP · Excel FILTER · Excel SUMIFS