Excel Processing Speed issue

[wilfrid147]

Hi all,

I have a 7.1MB Excel file which purely uses excel formulae and graphs (no macros) and the processing speed of the file is horrendous and it doesn't seem to finish processing(ever). I would like to know what are the possible issues that may be causing this?

My file mainly uses index, match, offset, small, large functions. The only reason I can think of that may be causing this issue is that my index match function usually refers to entire columns i.e R:R rather than R1:R100. However, I'm guessing Excel would stop after it finds the first correct value rather than going through the entire column.

Thanks in advance.

 

[AlexandraT]

Well technically INDEX() and OFFSET() are volatile functions. This means they recalculate whenever a change is made, even if the change does not affect the formula. (say your Index/Offset uses cell A1 and not A2; if you change A2, Index/Offset will recalculate anyway). So this might account for some of the sluggishness (see here for more Handle Volatile Functions like they are dynamite | Chandoo.org - Learn Microsoft Excel Online).
It is a very good idea to not use entire columns but use more exact ranges, as you suggest. If you think you might add to the ranges, you can use Tables so any addition is taken into account in all formulas.
Additionally, conditional formatting and different formats in cells might slow everything down. SUMPRODUCT, SUMIFS and array formulas might also slow everything down so see if you can replace them with simpler functions.
You can also make sure that all available processors are being used in File->Options->Advanced, under Formulas. (How to Change the Automatic Calculation and Multi-Threading Features in Excel 2013)
You could also try saving the file as xlsb as opposed to xlsx. This reduces the file size without any negative effects. It even supports macros if you ever want to add those.
As a last resort (and I would be very careful when doing this), you can change to Manual Calculation as opposed to Automatic. However, this means you have to manually refresh all the formulas since they will not update after a change, even if it would normally impact the formula. Fixing bloated file size and slow calculation in Excel | Chandoo.org - Learn Microsoft Excel Online
I've used some of the tips from here as well: 75 Excel Speeding up Tips - How to speed-up & optimize slow Excel workbooks? | Chandoo.org - Learn Microsoft Excel Online. You can search Google for other solutions.
Alex

 

[RoryA]

INDEX is not volatile. It is semi-volatile (only volatile immediately on opening the workbook) only if it's used on either side of a : range operator (for example A1:INDEX(A:A,10) but otherwise it is not volatile at all.

What are the SMALL and LARGE functions doing - are they used in array formulas?

 

[Kyle123]

xposted here Excel Processing Speed