Excel: Optimizing Worksheets for Fastest Calculation

Last reviewed: November 26, 1997
Article ID: Q72622
3.x 4.x 5.00 5.00c 7.00 WINDOWS kbusage

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

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


KBCategory: kbusage
KBSubcategory:

Additional reference words: 7.00 3.00 4.00 4.00a 5.00 recalc calc


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: November 26, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.