|
PureBytes Links
Trading Reference Links
|
XL: Optimizing Worksheets for Fastest Calculation
The information in this article applies to:
Microsoft Excel for Windows, versions 3.x, 4.x, 5.0, 5.0c
Microsoft Excel for Windows NT, version 5.0
Microsoft Excel for Windows 95, version 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.
Updated March 21, 1999
|