# Excel Workbook Calculation Automatic and Manual

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.

## 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

Wyman is a Human Resources professional based in Hong Kong, specialized in business analysis, project management, data transformation with Access and Excel.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist