tips for spreadsheet design for faster calculation times?

[excelquest23]

Hello,

I'm working with a fairly large spreadsheet involving many calculations and was wondering if someone could give me some tips on designing worksheets to minimimize calculation times.

I'm using Excel 2007. I believe that in previous versions of Excel, calculation times were slower if calculations used data on cells from different worksheets. I also think that calculation times were lengthened by using 'IF' formulae rather than 'OFFSET'.

Could someone tell me if the old rules for improving calculation speeds in previous versions of Excel still apply to Excel 2007?

Also, I'd like help with the following specific cases:
-I need to run calculations using two grids of data. Each grid contains approximately 40,000 rows and 2,000 columns of data. Would the calculations using data from both of these grids be faster if they were on the same worksheet than if they were on separate worksheets?

-In general, is it faster to have calculations split up over multiple worksheets than entirely on one worksheet?

If anyone has a link they can send me that addresses these general questions it would be much appreciated!

Thanks in advance!
Derek

 

[Fazza]

Hi Derek. http://www.decisionmodels.com/ HTH, Fazza

 

[xenou]

If you avoid linking to other workbooks you will have faster calculations. I don't think that other sheets per se would slow you down...but Lookups (maybe) are slower than other links. Definitely, if you do lookups on sheets in other workbooks, I find it much faster to open the linked workbook.

Naturally, setting calculation to manual is always a possibility - you learn to keep an eye on the status bar to see if the sheet needs to be calculated (2003, anyway). I typically have about 30,000 rows of calculation * 10 columns and it is virtually instant, unless I am linked to other workbooks with lookups .. then, its a pain alright.

 

[excelquest23]

one more follow-up question...

Thanks for the help!

Do you know if it is possible to turn 'off' one or more worksheets in a workbook while running calculations using the other worksheets? I have some extensive calculations on a couple worksheets that only need to run periodically so if I could turn them off when I don't need them then that might help speed things up.

Thanks again!

 

[xenou]

It is possible to calculate only one sheet or a few sheets, either in Manual or Automatic mode (with different behavior for each of these):

This link is a little mystifying:
http://www.decisionmodels.com/calcsecretsg.htm

Microsoft offers this help:
http://office.microsoft.com/en-us/excel/HP051992271033.aspx?pid=CH010004931033

Press SHIFT+F9 Calculates formulas that have changed since the last calculation, and formulas dependent on them, in the active worksheet.

I do not have any experience with sheet calculation. I hope this helps.
Regards.

 

[ExcelChampion]

Re: tips for spreadsheet design for faster calculation times

Hello,
I also think that calculation times were lengthened by using 'IF' formulae rather than 'OFFSET'.

IF typically is a fast function. It tests the criteria and chooses TRUE or FALSE, and then stops.

OFFSET is a volatile function. This means that if any part of the spreadsheet is changed, even if it does not affect the OFFSET function, it will still recalculate. Volatile functions typically will slow down a spreadsheet and make calculation times longer.

However, I can't help but wonder why/how you interchange between IF and OFFSET? I can't think of a single reason why one would/could do this. This leads me to think that there may be a better way to accomplish whatever it is that you are trying to do.