#REF! Means Your INDIRECT() Formula May Be Slow As A Dog

A user recently handed me a spreadsheet and asked me to investigate some bizarre behaviour. For some people the spreadsheet’s calculation was fast, for others quite slow and, for a few, extremely slow. After a bit of playing around I found what the problem was and, since I couldn’t find it documented anywhere, I thought it would be worth a quick blog post.

The workbook in question had many sheets and a lot of those sheets had several thousand INDIRECT() formulas. The workbook had been structured with INDIRECT() formulas so the users could easily redirect ‘lookup’ formulas to pull data from different sheets as and when they needed to. This sort of design isn’t uncommon.

The workbook was really slow to calculate (1 minute) on my machine. There were no array formulas or complex calculations anywhere and there were very few downstream dependencies on the INDIRECT()formulas. However, I found that, if I deleted one particular sheet, the workbook calculations suddenly became ‘instantaneous’ (2 seconds). When I noticed that the INDIRECT() formulas on that sheet were all returning #REF! errors things began to make sense.

Let’s take a step back and think about what INDIRECT() does. It takes a string (or a cell containing a string), resolves what that string represents and then returns a reference to the corresponding range. The string may be an A1 or R1C1 style reference or the name of a named range. INDIRECT() has to go through a number of steps to determine what the reference may be; if it can’t find a match then it returns a #REF! error.

It’s a logical conclusion that the function will start with a local scope and then broaden its search through other open workbooks and add-ins to try to find a match if it needs to. If you have an invalid string reference, INDIRECT() is going to go through every possibility it can to try to find a corresponding range before it finally spits back an #REF! error: it is doing more work which means it will be slower.

This can also cause some pretty insidious behaviour: the speed of the #REF! INDIRECT() function will be affected by the number of workbooks and add-ins you have open and how many sheets and names they have. If you test the workbook in a “light” Excel instance it could recalculate quite quickly but for someone else with a “heavy” session it could be painfully slow. This behaviour is compounded by the fact that INDIRECT() is volatile which means it recalculates on every calculation event. In extreme cases where lots of #REF! INDIRECT() formulas are involved it can quite literally cripple the spreadsheet.

So, dear reader, manage your string references carefully and beware INDIRECT() formulas which return #REF! errors.

Advertisements

About Colin Legg

RAD Developer Microsoft MVP - Excel 2009 - 2014
This entry was posted in Microsoft Excel and tagged , , . Bookmark the permalink.

5 Responses to #REF! Means Your INDIRECT() Formula May Be Slow As A Dog

  1. jeffrey Weir says:

    Great insight, and thanks for sharing it, Colin. I don’t use INDIRECT if I can avoid it. And I almost always can. In this particular case, I use CHOOSE, with an array of defined names. Unlike INDEX, CHOOSE allows you to dynamically select ranges on other worksheets. It’s not *quite* as dynamic as INDIRECT, as you need to update your CHOOSE arguments to support the addition of any new sheets. But that can be done globally with a FIND/REPLACE, and it’s a hell of a lot faster than INDIRECT in large models as its non volatile.

    Like

  2. jeffrey Weir says:

    I wrote an article some time back over at Chandoos blog that has a comment detailing how to use CHOOSE in this way: see http://chandoo.org/wp/2014/03/03/handle-volatile-functions-like-they-are-dynamite/#comment-472410

    Like

  3. Pingback: Excel Roundup 20160222 « Contextures Blog

  4. fastexcel says:

    You beat me to it: I have been meaning to blog about how INDIRECT is probably the most evil Excel function and including this behavior as one of the evilnesses … I will wrap up the other reasons in my forthcoming post

    Like

  5. Pingback: INDIRECT – Excel’s Most Evil Function | Excel and UDF Performance Stuff

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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