Wednesday, December 14, 2005

Speeding up Excel

The following document officially applies to versions of Excel prior to Office 97. However the spreadsheet creation techniques still have value today.

XL: Optimizing Worksheets for Fastest Calculation
The information in this article applies to:
• Microsoft Excel for Windows 3.0, 3.0a, 4.0, 4.0a, 4.0c, 5.0, 5.0c
• Microsoft Excel for Windows NT 5.0
• Microsoft Excel for Windows 95 7.0
• Microsoft Excel 97 for Windows

Summary
In Microsoft Excel, recalculation performance is affected by the way data and formulas are arranged on the worksheet. The following list contains tips for optimizing your worksheet to improve recalculation speed:
• Organize your worksheets vertically. Use only one or two screens of columns, but as many rows as possible. A strict vertical scheme promotes a clearer flow of calculation.
• When possible, a formula should refer only to the cells above it. As a result, your calculations should proceed strictly downward, from raw data at the top to final calculations at the bottom.
• If your formulas require a large amount of raw data, you might want to move the data to a separate worksheet and link the data to the sheet containing the formulas.
• Formulas should be as simple as possible to prevent any unnecessary calculations. If you use constants in a formula, calculate the constants before entering them into the formula, rather than having Microsoft Excel calculate them during each recalculation cycle.
• Reduce, or eliminate, the use of data tables in your spreadsheet or set data table calculation to manual.
• If you only need a few cells to be recalculated, replace the equal signs (=) of the cells you want to be recalculated. This is only an improvement if you are calculating a very small percentage of the formulas on your worksheet.
• When a certain group of formulas must be recalculated a great number of times, then it may be helpful to replace the equal sign (=) in the formulas that you do not need to recalculate with a unique string that does not appear elsewhere. The formulas without the equal signs will not be recalculated (they are no longer considered formulas). When Microsoft Excel has recalculated the formulas that still contain equal signs, search for the unique string and restore the =.
• Activate the Automatic Except Tables option. To do this, follow the appropriate procedure below for your version of Microsoft Excel:

Microsoft Excel 5.0 and Later
1. From the Tools menu, choose Options, and select the Calculation tab.
2. On the Calculation tab, select the Automatic Except Tables option.

Microsoft Excel 4.x and Earlier
1. From the Options menu, choose Calculation.
2. Select the Automatic Except For Tables option.
• Do not use the Precision As Displayed option on the Calculation tab (the Calculation Options dialog box in Microsoft Excel 4.x and earlier). This option will slow recalculation because Microsoft Excel will have to round the numbers as it recalculates.

References
"User's Guide," version 5.0, pages 166-170
"User's Guide 1," version 4.0, pages 167-174
"User's Guide," version 3.0, pages 294-300, 697-700


Posted by: Jethro | 8:37 AM |