Excel Indirect Functions: How They Work and What They Are There For

Some Excel functions can be tricky to work with, not so much because you don’t understand them, but because you don’t see when and how they would be useful. It’s one thing to get a grasp on how to build the formula, but it’s another thing altogether to have a handle on how you can use it to enhance the work you do with spreadsheets.

As an example, Excel’s indirect formula is one that can help you save a lot of time in performing certain tasks, but to read about it on Microsoft’s official support site, you probably wouldn’t know it.  So to get better acquainted, let’s take a look at how the function works and review some contexts for using it. If you would like to continue learning about Excel functions and how to apply them, you can also take an advanced course in Excel.

Basic understanding of the indirect function

Fundamentally, the indirect function is designed to return a cell reference specified by a text string. Here’s an example to show what that means:

Entered in Cell C5 of this spreadsheet is the most basic form of the indirect function. In this example, the function references cell B2. So if you follow the arrow over to cell B2, you see the text “D2.” By applying the indirect function to that cell, Excel is considering that text to be a reference to cell D2, so it takes the value from that cell and returns it in the cell where the function is written.

So, at it’s core, the indirect function is as simple as that. However, let’s expand a little to test out what else it can do.

Concatenation within an indirect function

One popular way of using an indirect function is to use concatenation to simplify the user input. If we make a few modifications to the example used above, you can see what that means.

Here, you can use the & symbol to combine “D”, indicating the column, with the input in cell B2. Indirect puts together this text to read “D2” then it converts that to a cell reference. Returning the value in cell D2, then, it returns the value from that cell: 5.

Using Indirect within another function

This second example starts to point to some meaningful applications of the indirect function for Excel users. Namely, it provides a nice, clean method for on-the-fly data processing that doesn’t require any formula or function rewrites. Using indirect with concatenation, you can just type a record number from a list in your spreadsheet, and the function will give you the value for that record.

However, this still falls short of explaining the real-world usefulness of the function. To get there, we need to consider another ability of the function.  And that is the ability to work within another formula or function.

Take a look at the example below to see what you can do when you write an indirect function, including concatenate, and nest it within another function.

Here, our spreadsheet includes a list of months and values for each. We’ll say for the purpose of this example that the values in column H represent units sold for a particular item at a retail store. Then in column B, we have two cells for user input. Our formula using indirect functions is in cell D2.

So in this formula, there are two indirect functions with concatenated references. These are included as paramaters within a SUM function, telling that function where to begin and end its range of values to sum.

Taking a look at the first indirect function, you see that you have the letter H combined with the value in cell B1. That results in H3. Using the same process to analyze the second indirect function, you’ll see that you get H5. Thus the sum function will process the values in those cells, equivalent to the function =SUM(H3:H5). The result is 16. So, in this context, we see that there were sixteen units sold from March through May of 2013.

To get a result for a different time frame, the user of the spreadsheet simply has to enter a different start and end number.

Combining functions in Excel is an advanced approach that can be very useful, but if you are not used to it, you might be a little confused.  That’s OK. You can quickly learn to understand how to do this yourself by taking a course covering Excel’s functions.

Using named cells as an alternative

If you are creating a spreadsheet that operates like the one above and you need to make it accessible to multiple users, there is another option that might help users work with the sheet more intuitively. That is, you can use named cells to make cell references simple to work with.

So looking at the sheet above, for example, you could define names for all of the cells in column H, using the month, year format in column G. Then you can revise your formula to read as follows:

=SUM(Indirect(B1):Indirect(B2))

The user could then enter their month and year selections in cells B1 and B2 and get the sums.  In addition, since the indirect function will work with data validation, you can create a dropdown in the user input fields to guide selection.

If you are interested in building out a spreadsheet this way but are unfamiliar with how to define cell names or create a drop-down list, you will need some training in those operations.  A basic through intermediate level course in Excel should be helpful.

How to keep building your understanding

You may have gathered that the method of using the indirect formula described here is a lot like using an interactive dashboard on a report.  And that is certainly a great potential use of the Excel program.  If you are interested in learning more about how to use the program this way, try a course that shows you how to build stunning dashboards in Excel.