Unleashing the Power of Excel INDIRECT Function: A Comprehensive Guide to Dynamic Cell Referencing

Examples of the Excel INDIRECT Function

Here are few examples of using the Excel INDIRECR function in Excel:

Example 1: Basic Usage

Suppose you have data in cell A1, and you want to retrieve the value from that cell using the INDIRECT function. You just need to provide it with the name of the cell, written as text, as shown in the example below.

Here is an explanation of the formula: =INDIRECT(“A1”)

The formula above accepts the cell reference A1 as an input argument (enclosed in double quotes as a text string) and yields the value found in that cell, which, in this case, is 100.

Now, you might be wondering if this could have been achieved by directly referencing the sales (e.g., A1 or =$A$1), as demonstrated below. Then, why should we use the INDIRECT function unnecessarily?

When you use =A1 or =$A$1, you get the same result. But, if you add a row above the first row, the cell references change automatically to fit the new row.

When adding rows or columns to a worksheet and needing to keep the cell reference locked and unchanged, you can utilize the Excel INDIRECT function.

Example 2: Use Cell Reference

Suppose you have data in cell A1, and you want to dynamically reference the cell specified in cell C1. In cell E1, you can use the following formula:

When you use the Excel INDIRECT function with C1 (which holds a cell address as text), it makes A1 a proper reference. This means it looks at A1 and gives you what is there.

Remember, no need for quotes because C1 has the cell address in text.

But if C1 has something that is not a proper cell address, INDIRECT will show an error (#REF!).

You can make a reference to a cell by using both the column letter and the row number. For example, if you have the number 1 in cell C1, and you use the formula =INDIRECT(“A” & C1), it will point to cell A1, as shown in the example below.

Example 3: Calculate the Average of a Range of Cells

You can use the Excel INDIRECT function in Excel to reference a range of cells, just as you would reference a single cell, as shown in the example below.

Here is an explanation of the formula: =AVERAGE(INDIRECT(“A1:A6”))

• INDIRECT(“A1:A6”): The INDIRECT function is used to convert the text string “A1:A6” into a reference to the actual range A1 to A6. So, it essentially refers to the range of cells A1 to A6.
• AVERAGE(INDIRECT(“A1:A6”)): The AVERAGE function then calculates the average of the numbers in the range A1 to A6.

Just like the AVERAGE function, you can use other functions such as SUM, COUNT, MAX, MIN, LARGE, SMALL, etc.

Example 4: INDIRECT Function With Name Range

When Excel INDIRECT function combined with named ranges, it can be a powerful tool for creating dynamic and flexible formulas. Named ranges allow you to assign a meaningful name to a specific cell or range of cells, making it easier to understand and manage your formulas.

Suppose you have the following data on test scores for students in different subjects:

The scores for the subjects are listed in columns B, C, and D. To make it easier to refer to these ranges, you can assign names to them. Specifically, you want to name the range in column B as “Math” the range in column C as “Science”, and the range in column D as “English”.

Now, to achieve this, you can follow the method below:

Select the Range:

• Highlight the cells in column B where the Math scores are (e.g., B2 to B7).
• Then, go to the “Formulas” tab on the ribbon.
• Click on “Define Name” or “Name Manager” (depending on your Excel version).
• Enter the name “Math” and click OK. Now, the range B2:B7 is named as “Maths”.
• Repeat the same process for Other Subjects:

Now, you can use the Excel INDIRECT function to refer to the named range in a formula.

For example, if you want to average the values in the named range “Math” you could use the following formula:

Example 5: Dependent Drop-down List with the Excel INDIRECT Function

Creating a dependent dropdown list in Excel using the INDIRECT function involves setting up named ranges and using data validation.

For example, suppose you have a list of categories arranged in a row and their corresponding subcategories in a column, as shown below:

Now, to create a dependent dropdown list, assign a name range to each category, such as A2:A6 for Electronics, B2:B6 for Clothing, and C2:C6 for Furniture. We have already covered how to assign name ranges in the previous example.

Now, select the cell where you want to create a dropdown list for the main category (Electronics, Clothing, Furniture). This will be the first drop-down list.

• Select cell E2
• Click on Data Tab
• Click on Data Validation
• Select List as the Validation criteria and use the following formula in the Source field: =$A$1:$C$1

Your first drop-down list will look like below:

Now follow below method to create dependable drop-down list:

• Select cell F2
• Click on Data Tab
• Click on Data Validation
• Select List as the Validation criteria and use the following formula in the Source field: =$A$1:$C$1

Now, when you select ‘Electronics’ in the first drop-down (cell E2), the dependent drop-down in cell F2 will display the subcategories of Electronics (Phones, Laptops, Headphones, Smartwatches, Digital Cameras).

And when you select ‘Furniture’ in the first drop-down, the dependent drop-down in cell F2 will display the subcategories of Furniture (Tables, Sofas, Chairs, Beds, Dining Sets).