Excel > Functions


The INDIRECT function allows you to use a cell reference entered as a text string. This can be used to:

  • Ensure the reference does not change when additional rows and columns are inserted in the worksheet
  • Create a reference from letters and numbers entered into other cells. This also enables you to change the reference in the formula without editing the formula itself
  • Refer to a named range. Very useful when used with Data Validation to create dependent drop down lists

The syntax for the INDIRECT function is:

=INDIRECT(ref_text, [a1])




The referenced range. Can be entered as a cell reference, text string that creates the cell reference or a range name


The type of reference used in ref_text. True, or omitting the argument, uses the A1 style. False uses the R1C1 style

The example below demonstrates the INDIRECT function being used to create a cell range using a text string. Using this method allows us to alter the function by changing cells E1 and E2 as opposed to changing the function itself. It also locks the cells so the function will not change when new rows are added.

Entering a reference using a text string

The example below demonstrates the INDIRECT function being used to return the total for the South region. The value in cell E1 refers to a named range and the INDIRECT function allows us to pass this to the SUM function

Using named ranges with the XIRR function

Follow us on

Facebook  Twitter  You Tube 

Other Lookup & Reference Functions

Excel 2010 Superhero Course