INDIRECT – Excel’s Most Evil Function

My choice for Excel’s most evil worksheet function is INDIRECT, for reasons outlined below.

If you have a different choice you can cast your vote for your most evil function (and your favourite function) here.

So what makes INDIRECT so evil?

In no particular order here my reasons: you may have more!

Its Volatile

The INDIRECT function is volatile, which makes any formula that contains it volatile so that they defeat Excel’s smart recalc and recalc at every calculation. And of course this ripples down the dependency chains to make all dependent formulas also recalculate: SLOW!!!

Its Single-threaded

The INDIRECT function is single-threaded and so defeats Excel’s multi-threaded calculation engine:

#Ref Problem

If the argument you give INDIRECT cannot be resolved to a usable reference INDIRECT returns #Ref. The problem is that the process of attempting to resolve the reference involves looking in a very large number of places which consumes a lot of time:
Very SLOW!!!
(Colin Legg has a more detailed post about this problem here)

External workbooks

If you use INDIRECT to refer to external workbooks then they have to be open or else INDIRECT won’t work.
Error-prone & Fragile

Does not adjust when rows/columns added/deleted/moved

Because the argument to INDIRECT is text rather than a cell reference it does not automagically adjust when rows or columns are added/deleted/moved. OK its possible to build more complex INDIRECT formulas in some cases that do adjust, but they tend to get complex and error-prone.
Error-prone & Fragile

 Difficult to debug

It can be very difficult to understand & debug formulas containing INDIRECT because they are often complex, and because the trace precedents tool gets blocked by a textual reference.
Error-prone & Difficult

So are there any alternatives?

INDIRECT is an extremely powerful function that is often used to create workbooks that can dynamically adjust to structural changes such as changing the ranges or worksheets or the external workbooks that are being used in formulas. Useful alternatives can be:


The CHOOSE function is not volatile and is multi-threaded and is easy to debug and maintain.

CHOOSE(Index_Num,Arg1,Arg2, … Arg254)

The first argument must resolve to a number between 1 and 254 that determines which of the following arguments is returned. The first argument could be a MATCH function that looks up a parameter in a list to get a number. The arguments to be chosen from could be defined names referring to ranges, references, formulas or values.

The drawback of the CHOOSE function is that the formula gets unwieldy when there are a large number of choices.

The Reference form of INDEX

In addition to the standard form of INDEX (=INDEX(Range, Row_Index, Column_Index) ) there is a reference form which can be used to select from multiple ranges.


The multiple ranges must be:

  • Enclosed in ( )
  • On the same worksheet

The drawback of this form of INDEX are that the ranges must be on the same worksheet.


If INDIRECT is being used to insulate the workbook against structural changes you could consider using VBA to modify the relevant formulas. Using Defined Names to hold frequently used formulas, and modifying the defined names may prove easier and more efficient than modifying every formula. Remember that Defined Names can also hold formulas containing relative references. For relative references I recommend using R1C1 mode and notation when creating the named relative formulas.


INDIRECT is evil because:

  • Its slow
  • Its fragile and easily broken
  • Its hard to debug
  • Its hard to understand

If you have better ideas for eliminating INDIRECT please help stamp us stamp out use of INDIRECT!



This entry was posted in Uncategorized. Bookmark the permalink.

14 Responses to INDIRECT – Excel’s Most Evil Function

  1. mrbillbenson says:

    What is your alternative to using INDIRECT in data validation?

    • jeffrey Weir says:

      Cascading data validation (aka dynamic dropdowns) is about the only place that I *will* use INDIRECT. The volatility aspect of INDIRECT isn’t a problem in Data Validation, because it gets written to the cell as a string. And because it’s usually pointing at named ranges, with only one INDIRECT getting evaluated at any one time, most of the other downsides won’t bite you either.

      But DV is my exception.

  2. Very good points! I’d support it as “most evil”. But there is one place I rely on INDIRECT: Data Validation.


    MS hasn’t updated data validation to use tables yet. Pity. Tables are THE PERFECT place to put data validation lists. I wouldn’t put them anywhere else. When used in this capacity, INDIRECT adjusts readily to accommodate expanding/shrinking rows/columns and makes DV’s Source more readily understood. Of course, if performance suffers I can add named wrappers over table columns which slows down development and adds a cryptic link to the logic chain.

    I look forward to the day MS updates DV and I can stop using INDIRECT.

  3. Mynda says:

    Hi Craig,

    You can stop using INDIRECT now.

    If you reference the table cell range in your data validation source it will automatically adjust with the table even though it doesn’t recognise the structured referencing. e.g. say your table is in cells A1:A10 (including header) you would enter A2:A10 in your data validation source.

    Happy days. No INDIRECT, no named range, just table magic with regular cell references.

    Of course it would be better if you could use the structured references but it’s a small price to pay to avoid INDIRECT.


    • EXCELlent point but…

      I prefer the self documenting aspect of structured references and/or names in formulas over cell references. In my mind (this will cause some angst) I view cell references (which are not functions) as more evil than INDIRECT.

      • Mynda says:

        “more evil than INDIRECT”🙂

      • andypope says:

        Can you not use a named range that is the structured table reference?

        MYCODELIST: =Table1[CodeList]

        The DV list reference is =MYCODELIST

      • Hi Andy,

        Love your work. I have learned a great deal over the years from your posts.

        Yes. I call that a “named wrapper” (see my first post). I prefer names over cell references because they provide information that cell addresses cannot. However, I like that cell references, unlike names, do not require an extra step to determine what they point to nor an extra step to create them. Thus, utilizing INDIRECT and structured references (SRs) in DV:

        1) Exposes WHERE our DV list is (like cell references)
        2) Expounds on WHAT our DV list is (like names)
        3) Avoids the EXTRA STEP to create a named range and
        4) Avoids the associated additional link in our logic chain.

        It seems silly to me that nearly a decade after creating SRs, MS hasn’t adjusted DV to accept them.

  4. andypope says:

    Thank you.
    I missed the implied meaning of your named wrapper. And I agree they should add structured references to DV.

  5. Hello to all,
    Maybe this will not be in line with all the above testimonies…
    For my part, apart from the problems you all described, I think that the Indirect function allows you to rapidly solve many problems that would otherwise be quite hard to solve. For my part, I have used it most efficiently for many of my consulting customers.
    Maybe you could solve the speed calculation problems linked to this function – which did not give problems to me in most of the above applications – by using formulas such as:
    =if(flag=1,indirect(…),…). You would then set the flag to 1 when these functions would be needed in a recalc.
    Another possible solution – why not dream! – could be that Microsoft would add one option in the calculation mode where you would choose between three options: Manual, Automatic but without Volatile, Automatic with Volatile…
    Best regards. Hervé Thiriez.

  6. Anmol Singh says:

    Indirect is definitely a complex formula. But, it a superb formula. It has saved me a lot of time and has enabled me to create magic. For me, it is a hero formula

  7. Alan Jones says:

    I am confused I thought based on your blog
    That indirect became non-volatile in Excel 97 thus more safe to use?

  8. Alan Jones says:

    oops correcting myself .. i guess index was nonvolitile in ’97 not indirect😦

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s