#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.