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!
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!!!
The INDIRECT function is single-threaded and so defeats Excel’s multi-threaded calculation engine:
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:
(Colin Legg has a more detailed post about this problem here)
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!
What is your alternative to using INDIRECT in data validation?
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.
You can use tables to get dynamic dropdowns
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.
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.
“more evil than INDIRECT” 🙂
Can you not use a named range that is the structured table reference?
The DV list reference is =MYCODELIST
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.
I missed the implied meaning of your named wrapper. And I agree they should add structured references to DV.
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.
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
I am confused I thought based on your blog
That indirect became non-volatile in Excel 97 thus more safe to use?
Probably you are thinking of INDEX which became non-volatile in Excel 97, rather than INDIRECT which has always been volatile,
oops correcting myself .. i guess index was nonvolitile in ’97 not indirect 😦
I’m moving toward the INDIRECT is Evil side, but I need some help with replacing INDIRECT in current formulas.
CHOOSE shows potential.
I can get this simple formula to work . . ..
Entering a 2 in B2 returns NamedRange2.
But the formula below (which is simple using INDIRECT), does not . . .
I need the VLOOKUP to read from a different Named Range based on the number entered in B2.
Thoughts on how to get this to work, or another way to proceed are greatly appreciated.
Don’t put your named range inquotes: that makes it a string of text rather than a named range. This works for me =VLOOKUP(E17,CHOOSE(C14,Name1,name2,name3),4,FALSE)
Is the INDIRECT() function still volatile when contained in a formula in a named range?
Yes: any formula that refers to a named range containing INDIRECT will be volatile
. . . that was too simple!
Great Post and Discussion.
I see a lot more uses for CHOOSE now.
How would you reference a sheet based on cell value without INDIRECT()?
@Vinicius – Try using CHOOSE or an alternate design, maybe using VBA or Templates or …
I’ve long considered INDIRECT as the most evil Excel function, if for reason #3 alone – try to indirect to an invalid reference in another document and you may as well open the task manager to kill Excel straightaway rather than wait half an hour if you’ve got a bunch of them (admittedly this was 15 years ago when PCs were a lot slower).
I’d nominate Goto as the most evil VBA function. They should rename it for error trapping where it’s useful to something else, but I’ve never used a goto in anything non-error related (since 1993 in VBA and 1983 Cobol/Fortran). Fortunately using Goto is a bad habit few VBA codres have picked up
I have a super slow spread sheet and I am now rather certain it is because my formulas… I use each of the following formulas per row, for about 30 rows:
So if anyone is interested in my workbook or helping me out i would greatly appreciate it. I am way beyond my understanding at this point as far as making the thing I created and works, into a more efficient and better performing sheet.
I also have a mess of conditional formatting that highlights stock levels, errors, and a few other things. No idea if conditional formatting is a big performance concern or not.
Seems like some of my formulas got cutoff or vanished when they posted to the blog?
The following formula is sued on upto 52 entries per row for 30 rows (weekly data input calculation)
My case is, I need to search a whole sheet.
Performance wise, which one is better, INDIRECT or INDEX when searching whole column / row ?
INDEX is always faster than INDIRECT
I’m using Indirect to reference a cell value that contains the name of a sheet in the same workbook. Am I able to use choose instead of indirect to refer to a cell containing the name of a sheet name?
Depends how many sheets you have to choose from. With CHOOSE you have to be able to set up the choices in advance (using Named Ranges makes it easier to handle) and you are limited to a reasonable number of choices. INDIRECT is more flexible but has many more downsides.
Tab names would be around 40. Would you consider that to be too many?
Well, its doable but your CHOOSE formulas will get rather long!
Oh, so I have to write out each tab name into the Choose formula? I thought that was the purpose of listing out all the tab names in the Table?
You can always just name your sheet tabs 1, 2, …, 40. I’ve used this trick to make someone elses’ workbook usable, and it was certainly worth it.
Alternatively spend your efforts optimizing the calculation chains downstream , to see if you can’t significantly speed them up to the point that the volatility introduced by the existing INDIRECT is no longer noticeable.
Interesting… I’m always looking for ways to avoid volatile functions but am struggling in this case.
I use INDIRECT in order to reference a user editable cell in a protected worksheet. The user cell is part of a range of contiguous unprotected user entry cells where the user can edit, copy/paste and DRAG & DROP. It’s the DRAG & DROP where INDIRECT becomes invaluable to me. It allows the user to utilise all the standard Excel features for data entry, but it stops all my internal protected formulas from being changed. Without INDIRECT, as soon as the user performs a drag and drop, my formulas that need to look at that specific position would automatically change to follow the cell – and I don’t want that. I am basically decoupling my formulas from the user. Hope that makes sense!
How could you achieve the same level of abstraction without using INDIRECT?
Is this really as simple as using INDEX(), ROW())?
If your cells are in A1:B4 then =INDEX(A1:B4,2,2) will only change if you move the entire A1:B4 block of cells: individual drag/drop/copy/cut operations will not affect it.
I just figured that part out for myself whilst testing something related – got me excited for a second until I moved the entire range and broke the formulas that were pointing to the location I dropped the cells into.
Unfortunately, I have a combination of both use cases, e.g. SUM(Range) where the user is able to drag the entire range (because they need to edit those ranges), and also where individual cells are abstracted individually (where one or more dependencies would be messed by dragging one or more cells about).
In my case, I want the user to be able to drag and drop cells within the editable area.
I use INDIRECT to lookup the range of sheets (tabs) and then do a little INDEX MATCHING to find what I’m looking for. Each sheet is set up identically. Can I use CHOOSE to find the right sheet (tab)? Guessing from the above that I might need to name the range???
Yes you can use CHOOSE. You do not have to name the ranges on each sheet, you can just use the usual reference. But if you have a lot of sheets the CHOOSE formula gets messy unless you use named ranges.
Are you able to explain how to do this? I have sheets called 1Units, 2Units, 3Units, 4Units, and use a cell (o5) to identify which sheet to go to (it returns the exact sheet name). I intend then to grab the cell info needed (let’s say that is a13) from whichever unit comes from o5. It’ll be found in a13 in any of the sheets because they’ve been created identically (just with different values in cells).
Make O5 return the number ( 1,2,3 or 4) rather than a sheet name. then something like CHOOSE(O5,1Units!A13,2Units!A13,3Units!A13,4Units!A13)
All makes sense now. It’s brilliant…and now I get rid of a heap of painful INDIRECT functioning. I’m interested to see if it reduces the size of my workbook file.
One more quick query with this – is it possible to use a cell reference to actually be a sheet reference? In my example, if I put my CHOOSE functioning to happen in O5 leaving out the A13, it’s could return any of the sheet names (1Units etc). How do I then make that sheet name work with an INDEX MATCH formula? It would be something similar to:
The cell references don’t matter too much – it’s how I present O5 to then be a sheet reference. Any help would be fantastic!
Yes, Indirect made my workbook very very very slow. I deleted the formular with Indirect, and ran back to normal. So I started Googling around and found this article. Great place to learn.