Excel INDIRECT function explained

When using Excel, we often use cell references in formulas. This is what makes Excel so useful in the first place: Instead of performing calculations manually – like math back in school – by entering the values directly into the formulas, in Excel we simply specify the cells from which the values should be taken, across entire columns and rows. Microsoft’s spreadsheet program is therefore dynamic.

The INDIRECT function in Excel lets you go one step further. Rather than writing the cell reference in the formula, you can simply obtain it from another cell or compile it based on multiple parameters. It sounds complicated, but it can be a great help!

With INDIRECT, Excel allows you to obtain a reference from another cell, instead of entering it directly into the formula. Users can also piece together cell references in this manner – extracting the first component from one cell and the second from another. The cell containing the INDIRECT function then displays the value to which the compound reference refers.

INDIRECT is rarely used on its own. However, it could be used in this way for certain dynamic formulas, where the cell reference changes continuously with new parameters entered in a cell. In most cases, INDIRECT is used in combination with other functions.

It’s also possible to use INDIRECT to display a value from another worksheet. In theory, you could even refer to another workbook. But this kind of reference only works when the other workbook is also opened. If the file referred to in the INDIRECT function is closed, however, an error message will appear instead of a value.

INDIRECT comprises a required and an optional parameter.

• Reference: This is where you enter the cell reference or cell references. You can also write a part of the cell reference into the formula directly (using quotation marks). Two reference components are combined with the ampersand character (&).
• A1: This optional parameter specifies the format you wish to create the cell reference in. If the parameter is left out or TRUE is entered in the formula, the A1 format is applied. In the case of FALSE, the formula uses the R1C1 format.

Excel is typically used in A1 format. Here, the rows are numbered (A1, A2, A3, ...) and the columns shown with running letters (A1, B1, C1, ...). But you can also configure the use of the R1C1 format in the program settings; both rows and columns are numbered in this case. Accordingly, R5C10 would denote the cell where the fifth row meets the tenth column.

For instance, if the cell A1 contains B1 and the cell B1 contains the value 5, the following formula would produce the value 5:

The function becomes more complex when the cell reference is split into parts.

But you can also write the row as well as column in two different cells.

INDIRECT also offers an interesting application when the formula is used to reference values in other worksheets. This can be useful, for example, if similar tables and calculations are contained in multiple sheets and you wish to display the selected values of other sheets in an overview sheet. Here, the name of the worksheet is obtained from a cell.

You can now either adjust the value in cell A1 – using a drop-down menu for example – as necessary or arrange the values of the different sheets one below the other. Here you can simply drag down the formula (using the cursor) and Excel will automatically adjust the references to the new position of the function.

The INDIRECT Excel function is often used in conjunction with others. For example, combining INDIRECT with the SUM function is useful if you wish to add values from another worksheet. Rather than generating the total in the corresponding sheet and then reproducing it in an overview, like in the example earlier, the values can be added directly in the overview.

At the same time, you can add values from different worksheets with the SUM function.

Many other mathematic or statistical functions can also be expanded in Excel using this technique (such as MEAN).