ADDRESS Function in Excel
ADDRESS takes a row number and column number and returns a cell address as text — like "A1", "$B$5", or "Sheet2!C10". It doesn't return a value; it returns a string that describes a cell. Rarely useful alone; often paired with INDIRECT to build dynamic references from calculated positions.
What it does: Constructs a cell address as a text string. =ADDRESS(5, 3) returns "$C$5". Doesn't return the value at that cell — just the address as text. To use it as a reference, wrap in INDIRECT (with the usual volatility caveat).
What ADDRESS does
ADDRESS is Excel's "convert coordinates to address text" function. Give it a row number and column number and it returns a string like "$B$5" — an absolute A1-style reference by default. Adjust arguments to get relative references, R1C1 style, or references that include a sheet name.
The result is always a string, not a live cell reference. If you want the value at that address, wrap ADDRESS in INDIRECT: =INDIRECT(ADDRESS(5, 3)) returns the value in $C$5. But there's almost always a cleaner path — usually INDEX.
=INDEX(A:Z, row, col) does it cleanly and non-volatilely. The main legitimate uses of ADDRESS are (1) generating a list of addresses as text for documentation, (2) building a hyperlink target, and (3) the specific case where you need cross-sheet dynamic references and INDEX can't cover it. Reach for it sparingly.
Syntax breakdown
row_num Required
The row number of the address. Must be a positive integer within the sheet's row range (1 to 1,048,576).
column_num Required
The column number, where 1 = A, 2 = B, 26 = Z, 27 = AA, and so on up to 16,384 (XFD). ADDRESS converts the number to the letter representation automatically.
abs_num Optional
How much of the reference should be absolute (with dollar signs): 1 (default) both row and column absolute like "$A$1"; 2 row absolute only like "A$1"; 3 column absolute only like "$A1"; 4 fully relative like "A1".
a1 Optional
TRUE (default) returns A1-style ("A1"). FALSE returns R1C1-style ("R1C1"). Almost always leave default — R1C1 is legacy.
sheet_text Optional
Sheet name to prepend. =ADDRESS(1, 1, , , "Sheet2") returns "Sheet2!$A$1". If the sheet name contains spaces, ADDRESS automatically wraps it in single quotes.
5 real-world examples
Example 1: Basic address generation
Get the address for row 5, column 3:
=ADDRESS(5, 3)Result: $C$5 — a text string, not a live reference. To get the value at that cell, wrap in INDIRECT (or use INDEX).
Example 2: Relative address (no dollar signs)
Get "C5" instead of "$C$5":
=ADDRESS(5, 3, 4)Result: C5. The 4 as abs_num means "fully relative" — no dollar signs on either dimension.
Example 3: Cross-sheet address for hyperlink
Build a HYPERLINK to a specific cell on another sheet:
=HYPERLINK("#" & ADDRESS(10, 5, 1, TRUE, "Sales"), "Go to Sales!E10")Result: Clickable "Go to Sales!E10" link. This is one of the cleanest legitimate uses of ADDRESS.
Example 4: Find where a value first appears (as text)
Return the address (as text) where a value first appears in a range:
=ADDRESS(MATCH("Chen", A:A, 0), 1)Result: Text like "$A$4" showing where "Chen" is. Note: this returns the address as a string, not the value. Useful for reports that document data locations.
Example 5: ADDRESS + INDIRECT to build a dynamic reference
Reference a cell whose row is calculated from another cell (D1 holds the row number):
=INDIRECT(ADDRESS(D1, 3))Result: The value in column C at row D1. But =INDEX(C:C, D1) does the same thing without INDIRECT's volatility. Prefer INDEX in almost every case.
Common errors and how to fix them
Non-numeric row or column argument, or a row/column value that's out of range. Verify inputs produce integers within the sheet's dimensions (rows 1-1,048,576, columns 1-16,384).
ADDRESS returns text — Excel doesn't automatically treat it as a reference. Wrap in INDIRECT to make it live: =INDIRECT(ADDRESS(...)). But INDIRECT is volatile — usually INDEX is cleaner.
ADDRESS handles this automatically — it wraps sheet names with spaces in single quotes. If you're seeing issues, check whether you're manually adding quotes and doubling them.
Default is fully absolute ($A$1). Pass 4 as abs_num for fully relative, or 2/3 for mixed. Read the abs_num table above carefully — the numbers don't match intuitive expectations.
ADDRESS vs alternatives
| Approach | Best for | Trade-off |
|---|---|---|
| ADDRESS | Displaying an address as text; hyperlinks | Not a live reference |
| INDEX | Getting the value at row/column | Doesn't produce an address string |
| ADDRESS + INDIRECT | Cross-sheet dynamic references | Volatile, slow at scale |
| HYPERLINK | Direct clickable jumps | ADDRESS still useful for the target string |
| CELL("address", ref) | Getting address of an existing reference | Volatile; different use case |
Version compatibility
Download the practice workbook
Every example above, plus hyperlink-generator templates and ADDRESS vs INDEX comparisons.
Related functions
Frequently asked questions
Does ADDRESS return a live cell reference?
No — it returns a text string that looks like a reference. To use it as a live reference, wrap in INDIRECT: =INDIRECT(ADDRESS(...)). But INDIRECT is volatile. For getting the value at a computed position, INDEX is almost always better: =INDEX(A:Z, row, col).
How do I get row and column back from an address?
ADDRESS goes one way (numbers to string). To reverse, use ROW and COLUMN with INDIRECT: =ROW(INDIRECT("C5")) returns 5. But this requires a valid string input — validate first.
When should I actually use ADDRESS?
Rare cases: (1) generating a text list of cell addresses for a documentation or audit report; (2) constructing HYPERLINK targets that include sheet names; (3) building addresses that will be pasted into other tools. For getting the value at a computed position, use INDEX instead.
How do I create an absolute reference to a specific column but relative row?
Use abs_num = 3: =ADDRESS(5, 3, 3) returns "$C5" — column locked with dollar sign, row relative. Handy when generating formulas as text for output elsewhere.
Why does ADDRESS output "R5C3" instead of "$C$5"?
Because the fourth argument (a1) is FALSE, giving R1C1-style output. Change it to TRUE (or omit — the default is TRUE) to get A1-style like "$C$5".
Can ADDRESS reference external workbooks?
Not directly — it only supports sheet name in the fifth argument. For external workbook references, you'd need to concatenate the workbook name yourself: ="[Book2.xlsx]" & ADDRESS(...). But this is fragile; use direct references or Power Query for real cross-workbook needs.
Is ADDRESS volatile?
No — ADDRESS is not volatile. It only recalculates when its inputs change. INDIRECT (which people often wrap ADDRESS in) is volatile — that's where the performance cost comes from, not ADDRESS itself.
Build any reference — with the Sheets & Cells AI Add-in
The Add-in recognizes when you actually need ADDRESS (documentation, hyperlinks) versus when INDEX would be cleaner — and writes the right formula automatically. All inside Excel.
Learn about the Add-in →