In Excel, INDIRECT turns text into a cell or range reference, so you can use it to refer to a named range whose name is written in a cell or formula. For example, if Sales_Q1 is a defined name, =SUM(INDIRECT("Sales_Q1")) sums the cells it refers to; a direct formula such as =SUM(Sales_Q1) is simpler when the name is fixed.
What INDIRECT Does With a Named Range
A named range is a label assigned to a cell or group of cells. Instead of writing a coordinate such as B2:B20 in a formula, you might define that range as Sales_Q1 and use the name in calculations. INDIRECT adds a layer of interpretation: it reads text and treats that text as a reference. The text can be a named range, a cell address, or a sheet-and-cell reference.
This is useful when the reference needs to change according to a choice elsewhere in the workbook. If a cell contains the text Sales_Q1, INDIRECT can resolve it to the range with that name. Without INDIRECT, Excel generally treats the cell contents as ordinary text rather than as a reference to another range.
INDIRECT does not define a name or search for one. The name must already exist, and its spelling and scope must make it available to the formula. If Excel cannot interpret the supplied text as a valid reference, the result is usually a #REF! error.
How Excel Named Ranges Work
Defined names make formulas easier to read and can help keep workbook logic organized. A name can refer to one cell, a fixed range, or a formula that calculates a reference. Names are managed from the Formulas tab. In Microsoft 365 for Windows, open Formulas > Name Manager to inspect, edit, or delete names, or use Formulas > Define Name to create one. You can also select cells and enter a name in the Name Box to the left of the formula bar.
Each defined name has a scope. A workbook-scoped name is available throughout the workbook. A worksheet-scoped name is intended for use on its particular sheet, and different sheets can have local names with the same spelling. That distinction matters when INDIRECT resolves text: a name that works on one sheet might be unavailable or resolve differently in another context. When you are unsure, check the Scope column in Name Manager.
Names cannot contain spaces, so a label such as North Region is not a valid defined name. Use an underscore, for example North_Region. Names also cannot be written like ordinary cell addresses. These rules apply to the name itself; sheet names, by contrast, can contain spaces.
Using a Fixed Named Range
Suppose the workbook has a workbook-scoped name Sales_Q1 referring to A2:A4, where the cells contain 120, 80, and 100. INDIRECT can pass that named range to SUM. The name is fixed in this example, so INDIRECT works but is not essential.
With Sales_Q1 referring to A2:A4 and those cells containing 120, 80, and 100, use:
=SUM(INDIRECT("Sales_Q1"))
The result is 300. Excel interprets the text inside the quotation marks as the name, resolves it to A2:A4, and sums the values. The simpler formula =SUM(Sales_Q1) returns the same result without converting text into a reference. Prefer the direct version if the range will not vary; it is easier to follow and avoids INDIRECT’s recalculation costs.
Choosing a Named Range From a Cell
The more distinctive use is to keep a range name as text in a cell and let that choice control a formula. For instance, a selector cell might contain Sales_Q1 or Sales_Q2, with each name defined to refer to a different quarter’s data. The formula can then use the selected name.
Assume A2 contains the text Sales_Q1, and that name refers to the three values 120, 80, and 100. Use:
=SUM(INDIRECT(A2))
The result is 300. If A2 is changed to Sales_Q2, and that name refers to another valid range, the formula sums that range instead. The entry in A2 must match the defined name exactly. Extra spaces, a misspelling, or a name that is not in scope can cause an error.
This pattern can also support a dependent drop-down list. A data-validation list can use a formula such as =INDIRECT(A1) as its source when A1 contains the name of a defined list. The list names and the selector value must be compatible, and the defined names must refer to the intended list cells. For a workbook that will be maintained by other people, document the naming convention so entries are not changed accidentally.
Building a Reference to a Sheet
INDIRECT can assemble a sheet reference from text as well as resolve a defined name. This can be handy when multiple worksheets use the same layout and a cell selects which sheet to summarize. A sheet name that contains a space must be enclosed in single quotation marks within the reference text. The double quotes in the formula mark text for Excel; the single quotes become part of the sheet reference.
Assume A2 contains North Region, and each regional sheet stores its values in B2:B10. Use:
=SUM(INDIRECT("'"&A2&"'!B2:B10"))
Excel builds a reference to 'North Region'!B2:B10 and sums those cells. If the values in that range are 10, 20, and 30, with the remaining cells blank, the result is 60. The sheet named by A2 must exist, and the range must be valid on that sheet. This formula uses a text-built sheet address, not a named range, although the same INDIRECT function is doing the conversion.
Creating and Checking the Name
To set up a named-range example, select the cells to include, choose Formulas > Define Name, enter a name such as Sales_Q1, confirm that the intended scope is selected, and choose OK. Then open Name Manager to verify the name and its Refers to entry. A name can exist but point at the wrong cells, so checking its target is as important as checking its spelling.
For a selector-driven formula, put the exact defined name in the selector cell. If the name is generated by another formula, check the final displayed text too. A cell may look right while containing an unnoticed leading or trailing space. You can also test the name by temporarily entering a direct calculation such as =SUM(Sales_Q1); if that direct reference fails, INDIRECT will not fix the underlying name or scope issue.
Excel for Mac has the same core function and defined-name concept. The ribbon and dialog presentation can vary slightly by release, but use the Formulas tab’s name tools to define or review a name. Excel for the web supports many standard formulas, but some workbook-management options may be presented differently; use the desktop app if a name’s scope or definition needs careful editing.
Common Errors and Performance Limits
#REF!: The text does not make a valid reference, the named range was deleted, the selected sheet does not exist, or a range address is malformed.- A name resolves unexpectedly: Check for worksheet-scoped and workbook-scoped names with the same spelling. Review the current sheet and the name’s Scope in Name Manager.
- The result is wrong but not an error: The name may point to a valid but unintended range. Inspect the Refers to field and confirm that the selector contains the expected text.
- An external workbook reference fails: INDIRECT cannot resolve a reference to a closed external workbook. This makes it a poor choice for links that must work when the source file is not open.
INDIRECT is a volatile function. In practical terms, Excel recalculates it whenever recalculation occurs, rather than only when a cell it directly depends on changes. A workbook with many INDIRECT formulas can therefore recalculate more slowly, especially when each formula builds a large or complicated reference. Keep the number of such formulas reasonable and avoid using them when a straightforward reference will do.
Another limitation is that text references are less transparent than direct references. If a referenced row or column is inserted, a direct formula can often adjust its reference automatically; a text string assembled inside INDIRECT may not adjust in the same way. That can leave a formula pointing at an old address without producing an obvious error. Test workbook edits that affect the layout, and make the reference-building logic explicit.
When to Use an Alternative
Use INDIRECT when a reference genuinely needs to be selected or assembled from text, such as choosing among a controlled set of named ranges or building a sheet reference from a selector. If the target is fixed, use the named range directly. If the task is to find a value by matching a key or heading, a lookup formula is usually clearer than constructing a reference as text.
For example, imagine B1:D1 contains the headings Jan, Feb, and Mar, B2:D10 contains their data, and F1 contains the heading to summarize. With those cells laid out as described, this formula sums the data under the matching heading:
=SUM(INDEX(B2:D10,0,MATCH(F1,B1:D1,0)))
If F1 contains Feb and the values in C2:C10 total 245, the result is 245. INDEX and MATCH select the matching data column without turning text into a reference. This approach is nonvolatile and can be easier to maintain when the data is arranged as a consistent table. The best choice depends on whether the problem is truly dynamic reference selection or ordinary data lookup.
Frequently Asked Questions
Can INDIRECT refer to a named range stored in a cell?
Yes. Put the exact name in a cell and pass that cell to INDIRECT, as in =SUM(INDIRECT(A2)). The named range must exist and be in scope for the formula.
Do I need INDIRECT for every named range?
No. Use a name directly when the target is fixed. INDIRECT is useful when the name itself is chosen or assembled as text; otherwise it adds complexity and volatility without changing the calculation.
Can a named range contain spaces?
No. Defined names cannot contain spaces. Use a permitted substitute such as an underscore. Sheet names can contain spaces, but references to those sheets need the appropriate single quotation marks.
Why does INDIRECT return #REF!?
Check that the text forms a valid reference, that the named range or sheet exists, and that the name is visible within the formula’s scope. Also check spelling, quotation marks, and any extra spaces in the selector cell.
Does INDIRECT work with a closed external workbook?
No. INDIRECT cannot resolve a reference to a closed external workbook. If formulas must retrieve data while another workbook is closed, use a different approach suited to that link or import workflow.
Related
- Excel Mini Calendar and Date Picker: Your Options
- Excel Min Sec Format: Display Minutes and Seconds
- Small Excel Table: Create, Format, and Use One
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Sort Excel by Color: Steps, Options, and TroubleshootingTo sort an Excel sheet by color in Microsoft 365 for Windows, select a cell in your data,…
- Excel Date Plus Weeks: Formulas, Dates, and ExamplesTo add weeks to a date in Excel, multiply the number of weeks by 7 and add the…
- Excel Formula for Beginning of Month: Dates Made EasyIf A2 contains an Excel date, use =EOMONTH(A2,-1)+1 to return the first day of that date’s month. For…
- Excel EDATE Function: Add or Subtract Months from a DateUse Excel’s EDATE function to move a date forward or backward by a specified number of whole months:…
- SUMIF and SUMIFS Examples in ExcelIn Excel, use SUMIF to add values that meet one condition and SUMIFS to add values that meet…
- XLOOKUP Example With IFERROR: Excel Formula and TipsAn XLOOKUP formula wrapped in IFERROR can show a useful message instead of an Excel error: with the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.