In Microsoft Excel, all currently open documents use the
same mode of calculation, regardless of the mode in which they have been
saved.
Back to the top
To help explain how the mode of calculation is determined,
this article refers to the following hypothetical documents:
Saved with this
File name Type of document mode of calculation
------------------------------------------------------
Auto1.xls Workbook Automatic
Manual1.xls Workbook Manual
Auto2.xls Workbook Automatic
The following statements apply to calculation modes in
Excel:
| • | The first document opened uses the calculation mode with
which it was last saved. Subsequently opened documents use the same mode.
For example, if you open Auto1.xls and then open Manual1.xls, both documents
use automatic calculation (the mode used by Auto1.xls). If you open Manual1.xls
and then open Auto1.xls, both documents use manual calculation. |
| • | Changing the calculation mode of one open document changes
the mode for all open documents.
If Auto1.xls and Auto2.xls are both
open, changing the calculation mode of Auto2.xls to manual also changes the
mode of Auto1.xls to manual. |
| • | All sheets contained in a workbook use the same mode of
calculation.
If Auto2.xls contains three worksheets, changing the
mode of calculation of the first worksheet to manual also changes the mode of
calculation to manual in the other two sheets. |
| • | If all other documents are closed and you create a new
document, the new document uses the same calculation mode as the previously
closed documents.
However, if you use a template, the mode of calculation is
the mode that is specified in the template. |
| • | If the mode of calculation in a workbook has changed and
the file is saved, the current mode of calculation is saved.
If
Auto1.xls is opened, Manual1.xls is opened, and Manual1.xls is immediately
saved, the mode of calculation is saved as automatic. |
Back to the top
How to control the mode of calculation
All open documents use the same mode of calculation. You must
follow special procedures to work with documents that use different calculation
modes. For example, if you are working with Auto1.xls and you want to open
Manual1.xls in manual calculation mode, do either of the following:
| • | Set Auto1.xls to manual calculation mode before you open
Manual1.xls. |
| • | Close Auto1.xls (and any other open documents) before you
open Manual1.xls. |
There are four modes of calculation that you can select in
Microsoft Office Excel and in earlier versions of Excel. These modes are as follows:
| • | Automatic |
| • | Automatic except tables |
| • | Manual |
| • | Recalculate before save |
In Microsoft Office Excel 2007, the four modes of calculation are as follows:
| • | Automatic |
| • | Automatic except for data tables |
| • | Manual |
| • | Recalculate workbook before saving |
For this mode Recalculation occurs when
-------------------------------------------------------------------
Automatic You make any change to the document. All
affected parts of the document are
recalculated.
Automatic You make any change to the document. All
except tables affected parts of the document except tables are
recalculated. A table is recalculated only when a
change is made to it.
Manual You press the F9 key, click Options on the
Tools menu, click the Calculation tab, and
click the Calc Sheet button.
Manual / You press F9, or click Calc Sheet on the
Recalculate before Calculation tab on the Tools/Options menu, as
Save well as every time you save the file.
Back to the top
Recalculate the active sheet
To recalculate only the active sheet, use one of the following methods:
| • | Press SHIFT+F9. |
| • | In Excel 2003 and in earlier versions of Excel, click Options on the Tools menu, and then click the Calculation tab. Click the Calc Sheet button to calculate only the active sheet.
In Excel 2007, click Calculate Sheet on the Formulas menu in the Calculation group. |
Back to the top
Recalculate all open documents
To recalculate all open documents, use one of the following methods:
| • | Press F9. |
| • | In Excel 2003 and in earlier versions of Excel, click Options on the Tools menu, click the Calculation tab, and then click the Calc Now button.
In Excel 2007, click Calculate Now on the Formulas menu in the Calculation group. |
Back to the top
How to change the mode of calculation in Excel 2007
| 1. | Click the Microsoft Office Button, and then click Excel Options. |
| 2. | On the Formulas tab, select the calculation mode that you want to
use. |
Back to the top
How to change the mode of calculation in Excel 2003 and in earlier versions of Excel
| 1. | Click Options on the Tools menu, and then click the Calculation tab. |
| 2. | Under Calculation, click the calculation mode that you want to
use. |
Back to the top