INDIRECT turns text into a real Excel reference. That means a formula can point to a cell or sheet name stored in another cell instead of hardcoding the reference directly.
Does this affect you?
Use this for Excel for Microsoft 365, Excel 2021, Excel 2019, and Excel for the web when the referenced cell, range, or sheet needs to change based on text.
Try a basic INDIRECT formula
Start with the simplest version so the behavior is clear.
- In an empty cell, enter =INDIRECT(“A1”).
- Excel returns the value from A1.
- Type A1 as plain text in C1.
- In another cell, enter =INDIRECT(C1).
- Change C1 to B1 and the formula now returns B1 instead.
The formula did not change, but the text it reads changed, so the reference changed.
Reference a sheet name stored in a cell
This is a common use for month tabs, region tabs, or report tabs.
- Type a sheet name in a control cell, such as January in A1.
- In another cell, use =INDIRECT(A1&”!B2″).
- The formula returns B2 from the sheet named in A1.
- Change A1 to another sheet name, such as February, and the formula points to that sheet instead.
If a sheet name contains spaces, use single quotes in the constructed reference, such as =INDIRECT(“‘”&A1&”‘!B2”).
More control
Understand volatility
INDIRECT recalculates whenever anything changes, not only when its own inputs change. Many INDIRECT formulas can slow a large workbook.
Fix reference errors
A #REF! result usually means the text does not match a real sheet, cell, or range. Check spelling, renamed tabs, deleted sheets, and the exclamation point between sheet name and cell address.
Closed workbooks are a limit
INDIRECT generally cannot pull from another workbook unless that workbook is open. Use normal external workbook links when the source file may be closed.
Use Tables or named ranges when appropriate
If the goal is only a growing range, an Excel Table or dynamic named range is usually faster and easier to maintain than INDIRECT.
Sources
- Microsoft Support – INDIRECT function (2025)
- Microsoft Support – Function volatility in Excel (2024)
- Microsoft Support – Create a reference to the same cell range on many worksheets (2024)