To make Excel interpret text as a cell reference, use INDIRECT. With “B3” in A1 and 42 in B3, enter =INDIRECT(A1) in another cell; the result is 42. INDIRECT is useful when a formula needs to build a reference from text, such as a sheet name or a cell address. It does not retrieve a value by searching for a match, and it will return an error if the text does not describe a valid reference.
What You’ll Need
- Excel for Microsoft 365, Excel for the web, or another recent Excel version. The INDIRECT function is available in these versions.
- A worksheet with a cell or range you want to refer to, plus a text value or formula that describes that reference.
- If you plan to refer to another worksheet, know its exact tab name. External workbook references require the source workbook to be open.
The formula examples below use commas between arguments, as in standard Excel syntax. If your computer’s regional settings use semicolons as list separators, Excel may require semicolons instead. The formulas work the same way in Excel for Windows, Mac, and the web; menu paths below are for Windows where relevant.
Step-by-Step
- Identify the reference you want Excel to follow. INDIRECT takes text and treats it as a reference. The text can be an address such as B3, a range such as B2:B6, or a sheet-qualified address such as ‘North Region’!B2. First decide whether the reference should stay fixed or be assembled from changing values. INDIRECT changes how Excel reads the reference; it does not change the referenced cell’s contents.
- Put a simple address in a cell and test it. For this example, put the text B3 in A1 and the number 42 in B3. In D1, enter
=INDIRECT(A1). The result should be 42, because Excel reads the text in A1 as the address B3 and returns that cell’s value. If B3 is blank, the formula returns a blank-looking result; if A1 contains an invalid address, it returns an error. This is a useful first check before building a more complex formula. - Build an address from a column letter and the current row. This is handy when each row should refer to a different cell in a specified column. For example, put the letter B in A2 and the value 18 in B2. Enter
=INDIRECT(A2&ROW())in C2. Because the formula is on row 2, ROW() returns 2; Excel joins the text B and 2 to make B2, so the result is 18. Copy the formula down and it will construct B3, B4, and so on, while the column letter comes from column A. - Refer to a cell on another worksheet. Suppose A2 contains North Region, and the worksheet tab North Region has 125 in B2. In D2, enter
=INDIRECT("'"&A2&"'!B2"). The formula adds apostrophes around the sheet name and adds !B2, creating a complete worksheet reference. It returns 125. Apostrophes are important for sheet names containing spaces or punctuation, and they are safe to include around simple sheet names too. Make sure the sheet name matches the tab exactly. - Use INDIRECT with a range-taking function. INDIRECT can supply a range to functions such as SUM. Suppose B2:B6 contains 10, 15, 20, 5, and 10. Enter
=SUM(INDIRECT("B2:B6"))in B8. The result is 60, the total of those five cells. This pattern is useful when the range address itself comes from a cell or needs to change based on a selection. Check that the text describes the whole range, including its colon, and that the cells contain values SUM can add. - Use the R1C1 reference style when relative references are useful. R1C1 describes a cell by row and column numbers; brackets indicate a relative offset. Suppose your formula is in C2 and B2 contains 18. Enter
=INDIRECT("RC[-1]",FALSE)in C2. The second argument, FALSE, tells INDIRECT to read the text as R1C1 notation. RC[-1] means the same row and one column to the left, so the result is 18. Copying this formula elsewhere still means “one cell to the left.” You do not need to change Excel’s display setting because the argument specifies the reference style for this function. - Check the formula and copy it only after the first result is right. Press Enter, confirm that the result is what you expect, then use the fill handle or copy and paste to extend the formula. Inspect the constructed reference when troubleshooting: for example, check that a cell expected to contain B3 does not contain an extra space. If you edit the source text, INDIRECT will use the new reference as soon as Excel recalculates.
In general, INDIRECT has two arguments: the text that describes a reference, and an optional setting for the reference style. If the second argument is omitted or TRUE, Excel interprets the text as A1-style notation, such as B3. Use FALSE for R1C1 notation, such as RC[-1]. The function returns the value or range at the reference, rather than displaying the reference text itself.
Common Mistakes
- Using a cell address that is not actually text. If the cell holding the address has a typo, leading or trailing spaces, or an incomplete range, Excel may return #REF! or another unexpected result. Check the source cell directly, and remove accidental spaces. Text such as B3 is different from a descriptive label such as Sales total, which is not a valid cell reference.
- Leaving out quotes around a sheet name with spaces. A text reference to a sheet named North Region needs apostrophes around the sheet name in the completed reference. Without them, Excel may not recognize the address. The apostrophes are part of the reference text; they are not quotation marks around the entire formula argument.
- Expecting INDIRECT to search for a value. INDIRECT follows a reference that you provide. It does not find a row by matching a name or return a corresponding value from a lookup table. Use an appropriate lookup function for a search task instead.
- Using an external workbook that is closed. INDIRECT cannot reliably resolve a reference to a closed external workbook. If the source file is closed, the formula commonly returns #REF!. Keep the source workbook open or use a different approach, such as a direct external reference or a Power Query workflow.
- Assuming a reference will adjust like an ordinary cell reference. Because the address is text, inserting rows or columns may not update the text inside INDIRECT as Excel would adjust a normal reference. Review dependent formulas after changing worksheet structure.
Tips
- Keep reference text easy to inspect. If a long formula is difficult to debug, create the address text in a helper cell first. Check that it displays the exact address you intend, then point INDIRECT at that cell. This separates address-building problems from reference problems.
- Use named ranges when they make a workbook clearer. INDIRECT can use text that matches a defined name. For example, a selection cell containing a valid defined name can be used as the reference text. Create and review names through Formulas > Name Manager in Excel for Windows, and make sure the text matches the name exactly.
- Remember that INDIRECT is volatile. Excel recalculates volatile functions whenever calculation occurs, even if their inputs have not changed. A workbook with many INDIRECT formulas can recalculate more often and feel slower. Use ordinary references or a nonvolatile alternative where a changing text reference is not necessary.
- Keep sheet names and labels consistent. If a formula builds a worksheet reference from a drop-down or typed label, make the label match the tab name exactly. A consistent naming scheme reduces spelling errors and makes formulas easier to maintain.
- Test one representative formula before filling a large area. Confirm the initial row, a middle row, and the last intended row. This catches off-by-one row numbers, incorrect column letters, and references that go beyond the available data before they affect many results.
Troubleshooting
If you see #REF!: Check whether the text forms a valid reference, whether the named sheet or range still exists, and whether an external workbook is open. For a sheet reference, verify the spelling and the exclamation mark between the sheet name and cell address. Also check that a formula has not built a row or column outside the worksheet.
If you see #NAME? or another name-related error: Check whether the text is meant to be a defined name and confirm that the name exists in Formulas > Name Manager. A misspelled name, a name with extra spaces, or text that is only a label will not resolve as a reference. If the formula uses a function nested around INDIRECT, inspect the entire formula for a misspelled function name.
If the result is from the wrong cell: Look at the text being passed to INDIRECT, not just at the final formula. Confirm that row numbers, column letters, and sheet names are being joined in the intended order. For R1C1 references, verify that the second argument is FALSE and count the relative offsets from the formula cell.
If the result does not update: Check the workbook’s calculation setting under Formulas > Calculation Options and select Automatic if appropriate. INDIRECT is volatile, but calculation settings can affect when formulas update. Press F9 on Windows to recalculate; on Mac, use Fn+F9 on keyboards where function keys control hardware features, or use the Formulas menu’s calculation command.
If a copied formula points to unexpected cells: Review how the text is built and whether the referenced labels change as intended when copied. Unlike ordinary references, text inside INDIRECT does not automatically adjust in every situation when rows, columns, or sheet layouts change. Test the constructed address in a helper cell and update the formula logic if the layout has changed.
Frequently Asked Questions
What does INDIRECT do in Excel?
It converts text that represents a cell, range, or defined name into a reference Excel can use. The formula returns the value at that reference, or passes the reference to another function.
What is the difference between A1 and R1C1 in INDIRECT?
A1 notation uses column letters and row numbers, such as B3. R1C1 uses row and column numbers and can describe relative positions with brackets. The optional second argument is TRUE or omitted for A1, and FALSE for R1C1.
Can INDIRECT refer to another sheet?
Yes. Include the sheet name, an exclamation mark, and the cell or range address in the reference text. Use apostrophes around sheet names with spaces or punctuation, and check that the tab name is exact.
Can INDIRECT refer to a closed workbook?
No, not reliably. References to external workbooks generally require the source workbook to be open; otherwise INDIRECT can return #REF!. Consider a direct external reference or another data-import method if the source must stay closed.
Why does INDIRECT return #REF! after I insert a row?
The address inside INDIRECT is text, so Excel may not revise it when worksheet structure changes. Inspect the text and update the formula so it points to the intended new cell or range.
Is INDIRECT available in Excel for Mac and Excel for the web?
Yes, INDIRECT is available in current Excel for Mac and Excel for the web, and its basic syntax is the same. Ribbon layouts differ by platform, but the formula examples and reference rules apply.
Related
- table() in RStudio: How It Counts and Displays Data
- Data Validation and Reconciliation in Excel Explained
- PowerPoint Table Diagonal Split Cell: Practical Methods
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- XLOOKUP Excel Column Index: What Replaces It?XLOOKUP does not use a column index number. Instead, you give it the range or array to search…
- Excel Sum Negative Numbers: Formulas, Filters, and FixesExcel’s SUM function already includes negative numbers when you add a range: their values reduce the total. To…
- Excel Day Countdown: Build a Live Days-Left FormulaWith a deadline date in B2, enter =B2-TODAY() in C2 to show how many calendar days remain. For…
- How to Copy a Formula in Excel to Other Cells SafelyTo copy a formula in Excel, select the cell that contains it, then drag its fill handle into…
- Excel IF Text Contains a Word: Formulas and ExamplesTo check whether a cell contains a word or phrase in Excel, use =IF(ISNUMBER(SEARCH("apple",A2)),"Yes","No"). It returns Yes when…
- AVERAGEIF Excel Formula: Examples, Criteria, and TipsIn Excel, use AVERAGEIF to average numbers that meet one condition. For example, with departments in A2:A7 and…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.