INDIRECT: How to Use Text as a Cell Reference in Excel

indirect, formula, excel
The INDIRECT formula in Excel

Usually you type =A1 for referring to the cell A1 in Excel. But instead, there is also another method: You could use the INDIRECT formula. The formula returns the reference given in a text. So instead of directly linking to =A1, you could say =INDIRECT(“A1”). In this article, we are taking a look at how to use the INDIRECT formula and why it is very useful. But there are also disadvantages.

Steps for applying the INDIRECT formula in Excel

indirect, formula, cell, reference, example
Example for the INDIRECT formula in Excel.

You want to get data from different sheets but always on the same cell?

As said in the introduction, INDIRECT returns the value of a cell which you specify by a string. Please take a look at the picture on the right hand side. What will this formula return?

The formula says 

=INDIRECT(C5&D5)

C5 has the letter B in it and D5 the number 2. If you combine them with the & sign, it’s B2. So if we step into this formula, it says =INDIRECT(B2) . It refers to cell B2. As B2 contains the text “Test Cell”, the formula will return “Test Cell” in cell B5.


Hold on a second. Was this information helpful so far?
Connect with me on LinkedIn...
Professor Excel at LinkedIn

Boost your Excel skills: Learn the best Excel tricks and tutorials!


Example: Use INDIRECT for referring to another sheet

Let’s take it a step further:  Instead of just B2, you can also refer to other sheets, or even other workbooks. Let’s assume we type this formula into Sheet2 but we want to get the value from cell B2 on Sheet1. So we have to add the worksheet name. This could look something like this:

=INDIRECT("Sheet1!B2")

If you replace B2 with the cell references like in our picture, the formula will look like this:

=INDIRECT("Sheet1!"&C5&D5)

Of course, you can now replace the static text “Sheet1!” by another cell reference, which contains the text “Sheet1”. Then you have to concatenate the cells including the ! in the middle. Please note that if your sheet names have spaces or special characters like “-” in them, you have to add ‘ before and after the sheet name:

=INDIRECT("'Sheet 1'!"&C5&D5)


Example: Use named ranges in INDIRECT

Instead of creating cell references with the cell location, you can also use named ranges. Let’s assume you’ve named cell C6 “Tax_Rate” like in the following screenshot:

INDIRECT function with named range in Excel

Now you can refer to this cell using =Tax_Rate.

Using the INDIRECT function, it works the same way as with normal cell references: =INDIRECT(“Tax_Rate”). The name is not case sensitive, so it’ll also work when you type =INDIRECT(“Tax_rate”).

INDIRECT function with named range in Excel

Why is the INDIRECT formula useful?

Within the reference of INDIRECT, you can combine the cell link as complicated as you like. If you have several similar sheets, for example filled out surveys. You can easily put together the indirect formula always linking to the same cells on each worksheet.

Furthermore you can use it within your formulas:

=VLOOKUP(A1,INDIRECT("Sheet1!A:B"),2,FALSE)

There are countless ways of integrating INDIRECT into your formulas. 

Disadvantages of the INDIRECT formula

Volatile

But there is are also disadvantages, especially in very large workbook: The INDIRECT formula is volatile. That means that Excel will calculate it again, each time when it calculates the whole workbook. Must other formulas will only be calculated depending on if their input values have changed. But INDIRECT will always be recalculated.


Cell references not recognized by Excel

Another disadvantage is, that the reference is not recognized by a cell link. Let me give you an example: When you copy and paste a worksheet containing the INDIRECT formula, the break link function doesn’t work. Instead, you will see the #REF! error.

Also, formula auditing functions are hardly working: You can’t highlight the input cell with the “Trace Precedents” function. In the other direction it also doesn’t work: The the “Trace Dependents” function you can check, if your current cell is an input value for another cell. If your cell is an input for the INDIRECT formula, it won’t be detected.

INDIRECT is very unstable

The formula is very unstable in terms of changing workbook structures. Most formulas adapt when you insert rows or columns. Or move cells. =A1  will adapt, when you move the cell A1. =INDIRECT(“A1”)  on the other hand won’t.

Conclusion

The INDIRECT formula can be very helpful. It allows you to dynamically change cell references. But it comes with major disadvantages so that you should consider well, if it is worth using it.


Professor Excel is a fictional person with one passion: Microsoft Excel. He is devoted to pass on his vast experience and help all future generations to excel in Excel!

11 comments

  1. How can I copy the value of cell A1 and paste said value into another cell, using the value of cell B1, to determine the cell location where the value is to be pasted?

  2. I’ve got text in a cell (happens to be “b67”) but I want Excel to recognise that as a cell reference, so I can use it in formulae. Indirect will give me the Content of cell B67

  3. Can I refer to a cell using a title?

    Meaning that I created a Vlookup function using big data. I have one row that has all the info from that big data. The first cell (A1) I created a listed names of all the accounts, so I can choose with out typing the names. Then I have created a second row named Total. Now I can compare each account with the total. Then, I added a chart to that table. So I can notice the results in the chart. However, now I am stuck, I wanted to name the title of the chart (Total Sales Compared with ” specific name account “.) Can I make the title change the name of an account based on A1? Meaning if A1 = Account blah. In the title will be (Total Sales Compared with “Account blah”.) if I change A1 to Account blah 2, the title change as well?

    I wish my question is clear. Thank you.

    1. Hi Malik,
      If I understand your question correctly: yes, you can. You want to base a chart title on a cell content, right?
      Just click on the title, then into the formula bar and type =A1. The chart title then shows the value of cell A1.
      Best regards,
      Henrik

    2. One more comment: I’ve updated the article with an example of how to use INDIRECT with named ranges… (although I think you won’t need it for your example…)

  4. Let say the value of A1 is a word “sum”. Can I use indirect(A1) to as a function sum? Ex: indirect(A1)(A2:A4)

  5. Is it possible to save a formula in a cell and refer to the formula as indirect reference to populate a value. I am having 5 formulas that will need to be replicated across all the data calculation grid according to some conditions. I would rather want to dynamically “build” and run the formula based on value saved in a template formula which can be referenced with an indirect call. Is it possible? Eg, instead of cell A1 having value 5, it may have a formula of =Row()*5. Is it possible to use this with indirect?

Leave a comment

Your email address will not be published. Required fields are marked *