2010 v 365

[Ray Stone]

I've worked with Excel 2010 for many years and have developed some very large spreadsheets.

I find that the files take ages to load and then a long time to recalculate.

I wondered if moving to Excel 365 or another newer version would help?

Thanks

Ray

 

[Special-K99]

Also depends what formulas you have, array formulas will be slow, avoid them if possible.
Complete ranges, e.g. A:A will be slow to calculate, insert row numbers in an expected range to avoid delays, e.g. A1:A1000

 

[Ray Stone]

Thanks - I've done as much as I can to improve the formulii etc - I was more after any insight as to whether a more modern version of excel would help in terms of loading/saving and recalculation ?

 

[joeu2004]

In my experience, each newer version of Excel is slower, not faster (!) -- at least, on the same hardware (CPU and memory size).

In part, the reason might be because each new version seems to consume more memory. Consequently, there is less memory available for data, and there might be more cache misses during calculation. (That is just a theory.) A "cache miss" turns a simple load-from-memory instruction into expensive access to secondary and tertiary memory. (The latter is the "hard drive", which might be an SDD.)

On the other hand, especially with Office 365 (which I would never subscribe to), you might be able to re-engineer some array-entered formulas, taking advantage of new functions, some of which are only available on an experimental basis to "insiders". (Anyone can become an "insider". But you do need to sign up.)

 

[Ray Stone]

It sounds like moving to a newer version wont help - would more memory help? I currently have 16gb of Ram and using windows 10 64 bit and some of my files are reaching 400-500mb
Many thanks

 

[joeu2004]

I'm not knowledgeable about Excel design per se. But as general principles, more memory might very well help, even more than increasing CPU performance. Cache size is also very important, as well as the type of primary "hard drive": SDD is better than the usual HDD. To improve load and save time, you might consider saving as xlsb instead of xlsx/xlsm; however, I believe xlsb limits sharability between the Mac and PC and with Excel 2003 and older versions. To improve recalculation time, you might look at your Excel Advanced Options to see if multi-threading is enabled and not limited arbitrarily; however, any improvement that might bring depends on your workbook design, as well as your CPU architecture.

But I agree with Special-K99: poor recalculation performance is usually due to poor worksheet design. In addition to the misuse of whole-column references like A:A, the unnecessary use of "volatile" functions like OFFSET and INDIRECT, which can often be replaced with INDEX (albeit more complicated). In VBA, repeatedly referencing Range and Cells expressions instead of loading ranges into VBA arrays; and using type Variant (or undeclared) variables instead of type Double and Long in long-running loops. And that only scratches the surface.