This tutorial explains Excel Application Calculation Automatic and Manual.
You may also want to read:
Excel VBA Application Volatile Method
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