Formula Guide

The Excel INDIRECT Function
explained simply

INDIRECT turns a text string into a real cell or range reference — useful when the range you need is itself stored as data.

ExcelPro · 5 min read · Updated June 2026
Contents
  1. What does INDIRECT do?
  2. Syntax
  3. 4 examples
  4. Common mistakes
  5. FAQ

What does INDIRECT do?

INDIRECT converts a text string, like "B5" or "Sheet2!A1", into an actual working cell reference that other formulas can use.

This matters because most formulas need a real reference typed directly — INDIRECT lets you build that reference dynamically from a string, a dropdown selection, or another cell's content.

Syntax

=INDIRECT(ref_text, [a1_style])
ArgumentDescription
ref_text requiredA text string that looks like a cell reference, e.g. "B5" or "Sheet2!A1".
a1_style optionalTRUE (default) for normal A1-style references, FALSE for R1C1 style.
⚠️ INDIRECT is volatile, like OFFSET

It recalculates on every workbook change, which can slow down large sheets that use it heavily.

Examples

Example 1
Reference built from text
=INDIRECT("B5")

Identical to just typing =B5, but here it is built from a text string — the real use comes when that string is dynamic.

Example 2
Reference a sheet name stored in a cell
=INDIRECT(A1&"!B2")

If A1 contains the text "Sheet2", this returns the value of B2 on Sheet2 — letting a dropdown in A1 pick which sheet to pull from.

Example 3
Sum a range chosen from a dropdown
=SUM(INDIRECT(A1))

If A1 contains the text "C2:C10" (perhaps from a data-validation dropdown), this sums that exact range.

Example 4
Combine with cell-name shorthand
=INDIRECT("Q"&B1)

If B1 contains 4, this builds and resolves the reference "Q4" — useful for jumping between named ranges.

Common mistakes

⚠️ Forgetting the text must look exactly like a reference

"B5" works. "Cell B5" or "b 5" does not — INDIRECT is strict about format.

⚠️ Using INDIRECT across closed workbooks

INDIRECT cannot reference another workbook unless that workbook is currently open.

⚠️ Overusing INDIRECT in large models

Like OFFSET, it is volatile and can slow down heavy spreadsheets if used hundreds of times.

FAQ

Why use INDIRECT instead of a direct reference?
When the reference itself needs to change based on user input — a dropdown, a typed sheet name, or a formula-generated address — INDIRECT is the only way to resolve that text into something other formulas can use.
Does INDIRECT work with named ranges?
Yes — =INDIRECT("MyRange") resolves to whatever range "MyRange" refers to.

Practise INDIRECT with real data

ExcelPro has hands-on INDIRECT exercises built into real job scenarios — free to start.

Try INDIRECT exercises →

Related formulas

OFFSET VLOOKUP MATCH