Leading the way in Microsoft Office Development

Excel

Step 1 - (Tools > Option) dialog box

Automatic - Calculates all dependent formulas every time you make a change to a value, formula or name. Any workbook that contains formulas will be automatically calculated when it is opened. This is the default calculation setting.

Automatic except tables - Calculates all dependent formulas except those in data tables. You can calculate the formulas in data tables by pressing the "Calc Now" button,

Manual - Calculates only when requested by the user. "Calculate" will appear in the status bar when you have uncalculated formulas.

Recalculate before save - Calculates all dependent formulas in the worksheet before it is saved.

Calculate Now - Calculates all open worksheets, including data tables, and updates all open chart sheets.

Calc Sheet - Calculates the active worksheet and any charts and chart sheets linked to this worksheet.

All these settings are workbook specific but it is the first workbook that is opened that determines what the settings are. Opening subsequent workbooks will not change the options.

For more information about the Calculation tab of the (Tools > Options) dialog box, please refer to the Calculation Tab page.

Step 2 - What does Automatic Calculation mean ?

The default setting is Automatic this means that Excel will automatically recalculate all the necessary cells in a workbook when a value is changed.

This type of calculation is the default and is what some people call a minimum recalculation.

This means that the data and formulas in your workbook are constantly up-to-date.

Most of the time when the workbook is fairly simple recalculation takes a fraction of a second since the majority of the cells will be unaffected.

When you have a complicated workbook containing a lot of formulas, Excel will determine which cells to recalculate and in what order.

It is important to remember though that Excel can only track 65,536 dependencies to unique references.

If Excel finds more than 65,536 dependencies then the whole workbook is calculated and the word "Calculate" may remain in the status bar. For more information please refer to this Knowledge Base Article (243495).

Step 3 - What does Manual Calculation mean ?

This lets you select or clear the Recalculate Before Save check box.

The "Recalculate Before Save" check box controls if the formulas are recalculated before the workbook is closed.

This is often a good idea, as it prevents a user opening the workbook to display data that is not up-to-date.

Manual calculation is a good idea when you have large workbooks as you want to control when a recalculation occurs.

If your workbook contains any uncalculated formulas in the active workbook when you are working in Manual calculation the word "Calculate" will be displayed in the status bar.

Step 4 - Tips for Optimisation

1) Arrange your worksheets in alphabetical order with the sheets containing cells that are referred to a lot being at the start of the alphabet and those that contain a lot of formulas using those cells should be last.

2) If you are including user defined functions in your formulas, then try to put them at the end of the formula. For example instead of having "=UDF() + B2" use "=B2 + UDF()".

3) Any cell values which a user defined function might refer to are obtained at the top of the function and not after a lot of initial calculations.

Step 5 - Things to Remember

Calculation is not a workbook or worksheet specific setting. When you change the setting it affects all open workbooks (and worksheets).

(F9) - Recalculates any cells that have changed in all open workbooks. This will only calculate formulas that have changed since the last calculation.

(Shift + F9) - This is the same as the F9 except that it only recalculates cells on the active worksheet.

There is currently no way to quickly recalculate all the cells in just the active workbook.

(Ctrl + Alt + F9) - Recalculates all cells in all open workbooks regardless of whether they need to be recalculated. This does not seem to work in Excel 2003.

(Ctrl + Shift + F9) - Recalculates all cells in the active workbook regardless of whether they need to be recalculated. This does not seem to work in Excel 2002 or Excel 2003.

(Ctrl + Alt + Shift + F9) - Recalculates absolutely everything in all open workbooks regardless of whether they need to be recalculated. This includes all custom worksheet functions and external worksheet functions.