Got the need for speed?

DATodd

New Member
Joined
Mar 18, 2004
Messages
14
Any general or specific suggestions on ways to speed-up calculations in Excel workbooks? I usually work with large spreadsheets. I don't use VB functions, and I avoid array functions.

Are there any especially slow functions to avoid? I've heard the CHOOSE function is slower than the INDEX function.

What are your favorite tricks to speed-up the calculation or recalculation speed of a workbook?

Are there any organizational ways? Would using more cells with shorter formulas be faster than fewer cells with longer formulas?
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
If you are using and reusing data in VBA -it speads things up incredibly if you read it once and store it in memory rather than referring back to the cells that the information is stored in.

For example imagine that you have a single workbook and you are looking for each line of data within 50 other workbooks. The temptation is to write the Script so that you do a find (or whatever) for each cell in your primary workbook for each of the 50 that you open.

This is bad and much, much faster to read each cell once and store it in a string (or whatever).

BTW this will seem obvious to anyone who has programmed outside the VBA paradigm. (Reads from disk are slower than reads from memory).

As far as worksheet functions go -it is also true that well written VBA is faster than worksheet functions because you can minimize the reads.
 
Upvote 0
Good point on VBA. I might have to try that one. I suppose a well written routine that replaced a real complicated (slow) section of a worksheet might be a lot faster if it only reads and writes once.

I also use a lot of boolean logic, lots of named ranges, intersection operators, and lots of MIN and MAX functions.

But I've having a hard time getting my models to run more than say 2 or 3 iterations per second. I'd like to be in the 10-15 range.

Are there any ways to identify which portions of a spreadsheet are calculating slowly?
 
Upvote 0
One little tip for VBA. ALWAYS declare your variable types. If you don't, VBA will assume that every non-declared variable is of "Varient" type which takes longer to compile than if they are specifically declared as string, boolean, long, etc.
 
Upvote 0
Are there any ways to identify which portions of a spreadsheet are calculating slowly?

I've found that FastExcel from http://www.decisionmodels.com can help. The site has some useful info on fast and slow formulas, and utilities to help you spot bottlenecks. One that I hadn't thought of is conditional formatting: it can slow you down. By the way, I found the tip on reorganising your worksheets alphabetically to NOT be useful when I tried it. Many of the other tips were worth it though.

Denis
 
Upvote 0

Forum statistics

Threads
1,225,335
Messages
6,184,332
Members
453,227
Latest member
Slainte

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
Back
Top