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.

workbook_calculation_01

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).

dependency tree

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

 

 

 

Wyman W
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

Leave a Reply

Your email address will not be published. Required fields are marked *