Learn Excel - Simplify Life with Excel Concepts

Excel INDIRECT Function


How to use the INDIRECT Function (WS)

This Excel tutorial explains how to use the Excel INDIRECT function with syntax and examples.


The INDIRECT function returns a reference to a range. You can use this function to create a reference that won't change if row or columns are inserted in the worksheet. Or, use it to create a reference from letters and numbers in other cells.
Returns the reference specified by a text string. References are immediately evaluated to display their contents. Use INDIRECT when you want to change the reference to a cell within a formula without changing the formula itself.


Create a reference from text


The INDIRECT function returns the reference to a cell and thus displays the referenced cell's value.


=INDIRECT(ref_text, [a1])


The INDIRECT function syntax has the following arguments:

  • ref_text: Required. A cell reference or a text string (or both), that create a range reference to a cell, range of cells or named range. If ref_text is not a valid cell reference, INDIRECT returns the #REF! error value.
    • If ref_text refers to another workbook (an external reference), the other workbook must be open. If the source workbook is not open, INDIRECT returns the #REF! error value.
      (External references are not supported in Excel Web App.)
    • If ref_text refers to a cell range outside the row limit of 1,048,576 or the column limit of 16,384 (XFD), INDIRECT returns a #REF! error.
  • a1: Optional. It is either a TRUE or FALSE value. A logical value that specifies what type of reference is contained in the cell ref_text.
    • If a1 is TRUE or omitted, ref_text is interpreted as an A1-style reference.
    • If a1 is FALSE, ref_text is interpreted as an R1C1-style reference.


Use INDIRECT to create or supply a reference in text form. Indirect is useful when you want to convert a text value into a valid cell reference.
The reference created by INDIRECT will not change even when cells, rows, or columns are inserted or deleted. For example, the formula =INDIRECT("A1:A100") will always refer to the first 100 rows of column A, even if rows in that range are deleted or inserted.

  • References created by INDIRECT are evaluated in real time and the content of the reference is displayed.
  • When ref_text is an external reference to another workbook, the workbook must be open.
  • a1 is optional. When omitted, a1 is TRUE = A1 style reference.
  • When a1 is set to FALSE, INDIRECT will created a an R1C1-style reference.

Useful Links: Link 1Link 2Link 3Link 4