How to Use the Indirect Function in Excel

Indirect isn't well known, but it's one of Excel's best functions

Once you know how to use the indirect function in Excel, you can grab information from other sheets, reference named ranges, and combine it with other functions to create a truly versatile tool. It can take a little practice to get to grips with, but with the indirect function, you can do more than you might think.

Instructions in this article apply to Excel for Microsoft 365, Excel 2019, and Excel 2016.

What is the Indirect Function?

The indirect function is a way to convert a text string into a reference. That is, it draws information from a reference to another cell or range. It creates a reference from text, and doesn't change when cells, rows, or columns are altered, added, or removed from a cited range. The references it creates are evaluated in real time, so the reference is always accurate to the data it's drawing from.

If that feels a little confusing, don't fret. The indirect formula can be easier to understand with valid examples and in practice. If in doubt, have a go following the steps below, and you'll soon get the hang of it.

Using the Indirect Function With Named Ranges

Named ranges in Excel are a great way to collect data under a single reference, and the indirect function makes grabbing that information from them that bit easier. Here's how to do it:

  1. Open an Excel document with named ranges already applied. In our example, we have sales information from various foods and drinks, with the money earned over each day of the week collected under named ranges titled after the products.

    INDIRECT Function
  2. Choose a cell for your named range, and input one of them into it. In our example, we used Burgers. Add other designator titles and coloring if you wish.

    INDIRECT Function
  3. Select another cell where you want the indirect output to go. Since we're looking to add up all the sales volume from the week for a specific food, in this case Burgers, we'll type the following into the cell:

    =SUM(INDIRECT(G5)

  4. This designates a SUM function, that will use the indirect function to draw the information from the named range in cell G5, in this case Burgers. The output is 3781, the total of sales for the week for Burgers.

    In our example, we can replace Burgers in cell G5 with Lemonade or Desserts, the other two named ranges, and the output will change to their SUM total instead.

    INDIRECT Function

Using the Indirect Function on Multiple Sheets

The indirect formula is even more powerful when you use it to pull information from other sheets. You don't need to use named ranges to do it, either.

  1. Open your Excel document with multiple sheets, or create them with all the necessary information.

  2. In the sheet where you want the indirect output to go, create a cell with the name of the sheet you want to draw information from. In our example, it's FoodSales.

  3. Since we want to pull information from our FoodSales sheet to total the number of Burgers sold, we typed the following (replace the cell range and sheet name(s) with your own):

    =SUM(INDIRECT(B4&"!B4:B10"))

    INDIRECT Function
  4. This designates it as a SUM function, since we are trying to find a total. It then designates cell B4 as the reference text for the indirect function. The & brings together the elements of this function, followed by a quote and exclamation point, and then the range of cells that we want to draw the data from. B4 through B10.

  5. The output is the total of Burger sales for that week. When we create a new FoodSales2 sheet for a new week with different numbers, we only need to adjust cell B4 to say FoodSales2 to get the data on Burger sales for that week.

Using the Indirect Function With R1C1 Style Reference

For sheets that are continuously expanding, where the reference you want to use won't always be in the same cell, R1C1 Style references can be used with the indirect formula to give you the information you need. We'll continue to use our food sales examples here, but imagine it's for a higher level worksheet that looks at weekly sales totals overall.

  1. Open the Excel document with all of the data you want to draw from and select a cell for your indirect function output. In our example, we're looking at monthly food sales totals and we want to know the most recent sales total for the month.

  2. In our example, the formula looks like this:

    =INDIRECT("R12C"&COUNTA(12:12),FALSE)

    INDIRECT Function
  3. The indirect function is using R12 (row 12) followed by a C to denote a column, enclosed within quotes. The & joins the two parts of the function together. We're using the COUNTA function to count all non blank cells in row 12 (selecting the row or typing 12:12), followed by a comma. FALSE designates this as an R1C1 reference.

  4. The output is then the last entry in our table, in this case 8102, or $8,102. When we eventually add April's sales data, the latest sales number will update automatically in real time.

    INDIRECT Function