Excel Workbook Calculation Automatic and Manual

This tutorial explains Excel Application Calculation Automatic and Manual

Excel Workbook Calculation

In Excel worksheet, there are three options under Workbook Calculation.

Explanation of Excel Workbook Calculation Options

Excel Option	Description
Automatic	Default option. Recalculation occurs when- Edit a Cell- Open workbook – Add / Delete / Hide / Unhide row – Rename worksheet – Reorder worksheet – Change Calculation Mode from Manual to Automatic / rerun xlCalculationAutomatic in VBA – Modify Named Range Types of Cells that will recalculate – Cells affected directly by the above actions – Cells indirectly affected by the above actions such as formula referencing to the affected Cells – Cells that contain volatile Functions recalculate every time when any of the above action is performed
Automatic except for data tables	See the above description for Automatic. For “data tables”, it refers to the Function under Data > What-If Analysis > Data Table
Manual	- Do not auto calculate until clicking on the cells that contain a formula- Able to force recalculate of workbook by pressing CTRL+ALT+F9- SHIFT+F9 to recalculate active worksheet

Excel calculation process

Excel uses a special mechanism to minimize the number of calculation to speed up the process.  Excel builds “Dependency Trees”, which keeps track on the following changes

• Formulae/Names that have changed.


• Formulae containing Volatile Functions


• Formulae dependent on changed or volatile formulae or cells or names.

The below graph shows how the underlying Dependency Trees look like visually (just an example, not the actual one).

As Dependency Trees are built, Excel knows which dependent Cells need to update when a Cell changes.

Click here to read more about how to use VBA to turn off Automatic to improve performance.

Outbound References

https://support.microsoft.com/kb/214395?wa=wsignin1.0

https://msdn.microsoft.com/en-us/library/ff700515%28v=office.14%29.aspx?f=255&MSPPError=-2147217396

 

 

 

Excel Workbook Calculation Automatic and Manual