Excel

How to Use the INDIRECT Function in Excel

Use INDIRECT in Excel to turn text into a reference, with examples for sheet names, dynamic ranges, and common errors to avoid.

June 12, 2026/5 min read

The INDIRECT function in Excel turns text into a cell or range reference. It is useful when a formula needs to point to a sheet, cell, or range whose name is stored in another cell.

Use it carefully. INDIRECT is flexible, but it can also make workbooks harder to audit because the referenced range is hidden inside text.

The syntax

=INDIRECT(ref_text, [a1])

ref_text - text that describes the reference
[a1] - optional; TRUE for A1-style references, FALSE for R1C1-style references

Most workbooks use the default A1 style.

Basic example

If cell A2 contains the text B10, this formula returns the value in B10:

=INDIRECT(A2)

That is the core idea: the text in one cell becomes the reference used by the formula.

Reference a sheet name from a cell

Say A2 contains a month name such as June, and each month has its own sheet. To return cell B5 from the sheet named in A2, use:

The single quotes around the sheet name make the formula safer when sheet names contain spaces.

This pattern can work in a small business budget where each month sits on a separate tab and a summary sheet pulls the selected month.

Build a dynamic range

INDIRECT can also build a range reference from text:

=SUM(INDIRECT("B2:B"&E2))

If E2 contains 20, Excel sums B2:B20. This can be useful in controlled models, but it is often cleaner to use an Excel Table or a structured reference if the data set grows over time.

WATCH OUT

INDIRECT is volatile, which means it recalculates often. Too many INDIRECT formulas can slow down larger workbooks.

When not to use INDIRECT

Avoid INDIRECT when a normal reference, named range, table reference, INDEX, XLOOKUP, or CHOOSE would be clearer. The formula may work, but future users have to understand both the text construction and the final reference.

INDIRECT is usually strongest when the workbook genuinely needs a reference selected by sheet name, scenario name, or a controlled input cell.

Common INDIRECT problems

Problem	Cause	Fix
`#REF!`	Text does not point to a valid range	Check the sheet name and cell address
Formula breaks after sheet rename	Hard-coded text no longer matches	Pull names from controlled cells
Slow workbook	Too many volatile formulas	Replace with tables, named ranges, or lookups
Hard-to-audit formulas	References are hidden in text	Use INDIRECT only where the dynamic reference is necessary

The Griddy way

INDIRECT can solve dynamic reference problems, but it can also hide the logic that makes the workbook work.

"Audit these INDIRECT formulas, identify which ones can be replaced with normal references or tables, and keep only the dynamic sheet selector"

Griddy can trace the generated references, repair broken sheet names, and simplify formulas that do not need INDIRECT.

Skip the manual work

Describe it. Griddy does it.

Instead of writing this formula yourself, just tell Griddy what you need in plain English. Works in Excel and Google Sheets.

Excel Plugin Sheets Plugin

Use this on real templates

Keep dynamic references understandable

INDIRECT is most useful when a workbook selects a month, scenario, or sheet name from a controlled input instead of hiding ordinary references in text.

Finance

All posts