INDIRECT Function in Excel: Explained

In this article, you will learn what the INDIRECT formula in Excel does and how to use it.

‍

What does the INDIRECT formula in Excel do?

The INDIRECT function in Excel is used to convert a text string into a cell reference or range reference. It allows you to create dynamic formulas and references by referencing cells indirectly, based on the contents of another cell.

‍

What are the uses of INDIRECT function in Excel?

The INDIRECT function in Excel has various uses and applications. Here are some common uses of the INDIRECT function:

1. Dynamic referencing: The INDIRECT function allows you to create dynamic cell references based on the contents of other cells. This is particularly useful when you want to refer to different cells or ranges based on changing criteria or user input.
2. Referring to named ranges: The INDIRECT function can refer to named ranges indirectly. This is beneficial when you have defined named ranges in your workbook and want to dynamically reference those ranges based on conditions or calculations.
3. Handling variable sheet names: When working with multiple sheets, you can use the INDIRECT function to reference cells or ranges on different sheets. This is particularly helpful when the sheet names vary dynamically or when you want to create flexible formulas across multiple sheets.

These are just a few examples of how the INDIRECT function can be used in Excel. Its ability to work with text strings as cell references or range references provides flexibility and allows for dynamic calculations, referencing, and formatting. By leveraging the INDIRECT function, you can build more versatile and adaptable formulas and functions in your Excel workbooks.

‍

How to use the INDIRECT formula in Excel?

The syntax of the INDIRECT function is as follows:

=INDIRECT(ref_text, [a1])

ref_text (required): This is the text string that represents the cell reference you want to create. It can be a direct reference like "A1" or a cell address specified in another cell, such as A1 in cell B1.

a1 (optional): This parameter specifies the style of the cell reference. If omitted or set to TRUE, the reference style is in the A1 format (e.g., A1, B2, C3). If set to FALSE, the reference style is in the R1C1 format (e.g., R1C1, R2C2, R3C3).

Case study: Using the INDIRECT function with the SUM formula and a Named Range in Excel

Suppose you have a sales dataset with multiple products, and you want to analyze the sales for each product using named ranges and the INDIRECT function.

‍

Step 1: Set up the data. Create a table with sales data, including columns for each products and sales figures.

Step 2: Define named ranges for the products to make it easier to refer to them in formulas. Here, we name the range of B2:B7, C2:C7, and D2:D7 as “Product_A”, “Product_B”, and “Product_C”.

‍

Step 3: Use the following formulas to calculate the Total and Average Sales by product, where Product is specified in cell H1

=SUM(INDIRECT(H1))

‍

Step 4: Now you can change the product reference in cell H1 to Product_B and the formulas for Average and Total Sales automatically refer to the sales figures for Product_B without you having to change them.  

‍

The INDIRECT function, combined with named ranges, allows you to dynamically refer to the correct range of sales figures based on the region and product specified in the analysis table.

‍

By using this approach, you can easily update the sales data or add new regions/products without adjusting the formulas manually. The named ranges and INDIRECT function handle the referencing dynamically.

‍