How to Use the INDIRECT Function in Excel
Introduction
You maintain a workbook with a separate sheet for each quarter, and every time someone asks for a different quarter's numbers, you edit the formula by hand. Change the sheet name, double-check the cell address, hope nothing broke. The INDIRECT function handles that by turning a text string into a cell reference.
What Is INDIRECT?
INDIRECT takes a piece of text and treats it as a cell reference. Instead of pointing to a cell directly like =A1, you give INDIRECT a string that describes where to look, and Excel goes there.
The syntax is:
=INDIRECT(ref_text, [a1])
- ref_text: A text string that represents a cell reference, like
"A1"or"Sheet2!B5". - a1: Optional. TRUE (or omitted) for A1-style references, FALSE for R1C1-style. You can ignore this argument in almost every case.
A Simple Example
Say you have these values:
| A | |
|---|---|
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
In cell C1, you type the text A3. Now enter this formula in D1:
=INDIRECT(C1)
Excel reads C1, finds the text "A3", and treats it as a reference to cell A3. The result is 300.
Change C1 to A1, and the formula instantly returns 100. The formula itself stays the same. Only the text it reads changes.
This is different from =C1, which would return the literal text "A3". INDIRECT resolves the text into a real reference that Excel follows.
Dynamic Sheet References
Suppose you have a workbook with three sheets named Q1, Q2, and Q3, each containing quarterly revenue in cell B2:
| Sheet | B2 (Revenue) |
|---|---|
| Q1 | 45,000 |
| Q2 | 52,000 |
| Q3 | 61,000 |
On a summary sheet, you want a dropdown in cell A1 where someone picks a quarter, and a formula in B1 automatically pulls that quarter's revenue.
=INDIRECT(A1 & "!B2")
A1holds the sheet name the user selected (say, "Q2")."!B2"is the cell address on that sheet.- The
&operator joins them into"Q2!B2".
INDIRECT resolves that string into an actual reference to sheet Q2, cell B2. The result is 52,000. Pick Q3 from the dropdown, and it returns 61,000. No manual editing.
Key takeaway: Pair INDIRECT with a dropdown list and users can pull data from any sheet without touching the formula.
INDIRECT with Named Ranges
INDIRECT also works with named range names passed as text. If you have a named range called Revenue pointing to B2:B10, this formula works:
=SUM(INDIRECT("Revenue"))
On its own, that's no different from =SUM(Revenue). The difference shows up when the range name itself is variable. Suppose you have three named ranges: Revenue_Q1, Revenue_Q2, and Revenue_Q3. With a quarter selector in A1:
=SUM(INDIRECT("Revenue_" & A1))
If A1 contains "Q2", INDIRECT builds the string "Revenue_Q2" and resolves it into the matching named range. Excel sums that range. Change the dropdown to Q3, and the formula recalculates against Revenue_Q3.
One dropdown can control every formula on the page by swapping which named range each INDIRECT call resolves to.
Handling Sheet Names with Spaces
If your sheet names contain spaces, Excel requires single quotes around the name in the reference string. A sheet called "Q2 Sales" needs:
=INDIRECT("'" & A1 & "'!B2")
The formula wraps the sheet name in single quotes so the final string reads 'Q2 Sales'!B2. Without the quotes, INDIRECT returns a #REF! error because Excel can't parse the space.
A clean way to avoid this entirely is to name your sheets without spaces. Use underscores or short codes like Q1, Q2, and Q3.
Things to Watch
INDIRECT is a volatile function. Excel recalculates it every time anything in the workbook changes, not just when its inputs change. In a small workbook, this doesn't matter. In a large file with hundreds of INDIRECT calls across complex ranges, it can slow recalculation noticeably.
A few other things to keep in mind:
- Renaming breaks it. If you rename a sheet from "Q2" to "Quarter2" and your text strings still say "Q2", INDIRECT returns #REF!. It reads text literally. It has no way to track renames.
- Closed workbooks don't work. INDIRECT can only resolve references in workbooks that are currently open.
- No autocomplete. Because the reference is built from text, Excel can't offer formula autocomplete or highlight the referenced cells. Debugging requires checking the text string manually.
If you find yourself stacking dozens of INDIRECT calls to solve a structural problem, it may be worth reorganizing the workbook instead. INDIRECT is best used selectively for dynamic references, not as a replacement for proper data layout.
Conclusion
INDIRECT converts a text string into a cell reference, so your formulas can point to different cells, ranges, or sheets without being rewritten. Combine it with a dropdown and one selection can update every formula on the page.
For setting up the dropdown that drives an INDIRECT formula, see our guide on creating drop-down lists. If you're building named ranges to use with INDIRECT, check out our guide on named ranges in Excel.
Level up your Excel skills
Bite-sized lessons, drills, and daily challenges to build real spreadsheet skills.