tips for spreadsheet design for faster calculation times?

excelquest23

Board Regular
Joined
Apr 8, 2006
Messages
58
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
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. Windows
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

Board Regular
Joined
Apr 8, 2006
Messages
58
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

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. Windows
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

Well-known Member
Joined
Aug 12, 2005
Messages
976
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.
 

Forum statistics

Threads
1,181,416
Messages
5,929,790
Members
436,694
Latest member
dpatete

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top