Handling efficiently large amount of data ?

[Morphil]

Hello all,
When processing a large amount of data, Excel may become VERY slow.
Is there a tip on "best pratice" to input formulas which are all the same and apply similarly on a range of cells so ythat Excel would optimise its calculation speed ?

Thanks for your help

 

[FastExcel]

My website has a lot of information about speeding up Excel calculations
http://www.Decisionmodels.com

or you could look at the white paper I wrote for Microsoft

http://msdn2.microsoft.com/en-us/library/aa730921.aspx

 

[tusharm]

The answer depends on identifying the bottleneck first. At the very least you need to figure out where, at an abstract level, where the problem lies.

Is the slowdown in

recalculating a worksheet after it has been completely designed?

adding new data, together with associated formulas?

editing data?

Hello all,
When processing a large amount of data, Excel may become VERY slow.
Is there a tip on "best pratice" to input formulas which are all the same and apply similarly on a range of cells so ythat Excel would optimise its calculation speed ?

Thanks for your help

 

[Morphil]

Hi Charles and Tushar
Your useful links give me many hints that I will investigate. I understand that my problem description was a bit too much "general"
Thanks !

 

[Legacy 14611]

Operating on large data sets on worksheets can be pretty slow.

Putting the data into arrays and operating on the array elements instead of the worksheet cells is, if you do it efficiently, very much faster.

Just how best to do it depends on the problem in hand, but it's good general advice.