How to Use the indirect Function in Excel: Complete Guide with Examples | Updated 2025

Mastering the INDIRECT Function in Excel

CyberSecurity Framework and Implementation article ACTE

About author

Deshma Marceline (Data Scientist )

Deshma Marceline is a skilled data scientist with a strong background in machine learning, statistical analysis, and data visualization. She excels in transforming complex datasets into actionable insights that drive strategic decisions. Passionate about innovation, Deshma leverages data to solve real-world problems and enhance business performance.

Last updated on 21st Apr 2025| 7267

(5.0) | 232512 Ratings


Excel’s INDIRECT function is ideal when you need to build flexible formulas that adapt as your spreadsheet evolves. Unlike static cell references, INDIRECT allows for dynamic adjustments based on inputs or changing cell values. This makes it a valuable tool for anyone handling large or complex spreadsheets where references are frequently updated. It also enhances data management across multiple sheets by allowing you to reference cells or ranges without manually revising each one. Whether you’re retrieving data, calculating dynamic ranges, or working across various spreadsheets, INDIRECT simplifies the task with a more reliable method of cell referencing. Learn more with our Data Science Training to master such powerful Excel features.

In Excel, what is an INDIRECT function?

Excel’s INDIRECT function yields the reference that a text string specifies. It lets you make a reference that remains unchanged even if you add or remove rows or columns. Creating dynamic cell references is very helpful when working with big spreadsheets where your data may change.

Syntax of the INDIRECT Function:

The syntax of the INDIRECT function is: INDIRECT(ref_text, [a1])

  • ref_text: This is a required argument. The text string represents the cell or range you want to refer to. It could be a cell reference, a range, or a sheet name.
  • [a1]: This is an optional argument. It specifies the reference style. If TRUE (or omitted), the reference is treated as an A1-style reference (e.g., “A1”). If FALSE, it uses an R1C1-style reference (e.g., “R1C1”).
  • Example:

    =INDIRECT(“A1”) This formula will return the value of cell A1.

      Subscribe For Free Demo

      [custom_views_post_title]

      Excel Formula and Indirect Function Use

      You can use INDIRECT to refer to a cell whose address is provided as text. This is useful when you want the cell reference to change dynamically based on a formula or input. For more details, check out Tips to Improve Your Basic Microsoft Excel Skills.

      Using INDIRECT to Reference a Cell

      You can use INDIRECT to refer to a cell whose address is provided as text. This is useful when you want the cell reference to change dynamically based on a formula or input.

      For example, if you have a value in cell B1 that contains the reference A1, you could use:

      =INDIRECT(B1) This will return the value of cell A1.

      Using INDIRECT to Reference a Range

      In cases where you want to reference a range dynamically, you can combine INDIRECT with other functions such as SUM or AVERAGE. For example: =SUM(INDIRECT(“A1:A10”)) This formula will sum the values in cells A1 through A10.

      How to Use an Excel Indirect Function

      Example 1: Using INDIRECT for Dynamic Range Reference

      Let’s say you want to sum up a dynamic range of cells that change based on some condition. You could use the INDIRECT function with a concatenation of text and a value from another cell to achieve this:

      • Suppose cell B1 contains the number 10, and you want to sum cells from A1 to A10. You can use this formula: =SUM(INDIRECT(” 1:A” & B1)) This formula dynamically refers to the range from A1 to A10. If you change the value in B1, the range will automatically update.
      • Example 2: Using INDIRECT for Conditional References

        You can also reference different ranges or sheets conditionally using the INDIRECT function. For instance, if cell C1 contains “Sheet1 and you want to sum values from A1:A10 in that sheet, you could use: =SUM(INDIRECT(C1 & “!A1:A10”)) This formula will sum cells A1:A10 on the sheet named in cell C1 (in this case, “Sheet1”). If the value in C1 changes, the sheet reference will also change dynamically.

      DIRECT Formula to Instantaneously Access Another Worksheet

      A standout capability of the INDIRECT function is its dynamic referencing of cells or ranges across different worksheets. Rather than manually entering a sheet name, you can use INDIRECT to pull data from another sheet based on a cell’s value. This technique is especially useful for managing complex data structures efficiently. To dive deeper into functions like this, check out our Data Science Training.

      Example: Dynamically Reference a Different Sheet

      If cell D1 contains the name of a sheet (e.g., “SalesData”), and you want to sum the range B1:B10 from that sheet, you could write: =SUM(INDIRECT(D1 & “!B1:B10”)) This allows you to switch the sheet from which data is being summed by simply changing the value in D1.

      Example: Combining INDIRECT with VLOOKUP

      You can combine INDIRECT with VLOOKUP to perform lookups across multiple sheets dynamically. For example, if cell E1 contains the name of a sheet, you can search for a value in that sheet using: =VLOOKUP(F1, INDIRECT(E1 & “!A1:B10”), 2, FALSE) This will search for the value in F1 in the specific sheet’s range (A1:B10) and return the corresponding value from the second column.

      Course Curriculum

      Develop Your Skills with Data Science Training

      Weekday / Weekend BatchesSee Batch Details

      Additional Tips and Tricks for Using the INDIRECT Function

      • Avoid Using INDIRECT with Large Ranges: While INDIRECT is a powerful function, it can slow down your workbook if used with large ranges or in many cells. Use it judiciously to avoid performance issues.
      • Combining INDIRECT with Other Functions: INDIRECT can be integrated with various other Excel functions, like MATCH, INDEX, IF, and SUMIF, to craft more advanced and flexible formulas. For further expertise, you can explore becoming a Certified MS Excel Professional.
      • Referencing External Workbooks: You can also use INDIRECT to reference another workbook, but the external workbook must be open. The syntax for referencing an external workbook is as follows:
      • =INDIRECT(‘[WorkbookName.xlsx]SheetName!A1’) Mastering INDIRECT opens up many possibilities for dynamic and adaptable Excel spreadsheets.

      Data Science Sample Resumes! Download & Edit, Get Noticed by Top Employers! Download

      Conclusion

      Excel’s INDIRECT function is an invaluable tool for creating dynamic references that enhance the flexibility and efficiency of your spreadsheets. Mastering this essential formula and applying it in different scenarios like referencing ranges or entire worksheets can greatly boost your Excel expertise. Whether you’re handling large datasets, performing complex lookups, or summing dynamic ranges, incorporating INDIRECT into your skillset is a game-changer. You can deepen your understanding with our comprehensive Data Science Training

      Upcoming Batches

      Name Date Details
      Data Science Course Training

      28-Apr-2025

      (Mon-Fri) Weekdays Regular

      View Details
      Data Science Course Training

      30-Apr-2025

      (Mon-Fri) Weekdays Regular

      View Details
      Data Science Course Training

      03-May-2025

      (Sat,Sun) Weekend Regular

      View Details
      Data Science Course Training

      04-May-2025

      (Sat,Sun) Weekend Fasttrack

      View Details