Excel Calculation Process
|Smart Recalculation||Evaluation Circumstances||Calculation Process||Dependency Trees|
|Controlling Calculation||"CALCULATE" in Status Bar||Calculation Methods||Calculating from VBA|
|Volatile Functions & Actions||User-Defined Functions||Repetitive Calculation||Version Timing Comparison|
|Calculation Process||Dependencies||Forward References|
|Cell Calculation Sequence||Worksheet Sequence||Multi-threaded Calculation|
Excel does not calculate cells in a fixed order, or by Row or Column. Instead, Excel dynamically determines and remembers its own internal calculation sequence, which is based on dependencies and dependency trees and chains.
Overall Excel has a two step calculation process:
The first step is executed at each cell entry or change even in Manual calculation mode. Normally this executes so fast that you do not notice it happening, but if there is a noticeable delay in Manual mode then in Excel 2007 setting Workbook.ForceFullCalculation to true will eliminate the delay at the expense of making each calculation a Full calculation.
The second step is executed
at each calculation or recalculation.
The second time you calculate a workbook is often significantly faster than the first. This is due to several factors:
The default calculation method for Excel is to only calculate cells
that are flagged as either uncalculated or dependent on an uncalculated
Circular Reference Cell Calculation Sequence
When your workbook contains circular references and Iteration is switched on (Application.Iteration is True) Excel has to use a slightly different calculation algorithm:
The first step is the same as a recalculation (or full calculation, depending how the calculation was invoked) that does not involve circular references. This enables all the cells that come before the circular reference to be properly calculated and the list of cells in the circular reference to be identified together with their dependents. During this first step Application.Iteration is False.
The second step occurs once on each iteration:
This step is repeated until the iteration limits (maximum number of iterations and maximum cell change per iteration) are reached. During this second step Application.Iteration is True.
Data Validation and Automatic Calculation
When you are using Data Validation rules on cells with Automatic Calculation the result of the data validation may be different if Recalculation is done before data validation, or data validation is done before Recalculation.
Experiments show that this sequence is dependent on the way you
exit the cell after changing the data.
Download a workbook demonstrating this effect (with a problem bypass)
Microsoft Calculation Documentation
Some more details of Excel's calculation algorithm and the changes made in Excel 2002 have recently been published by Microsoft.
Excel 2007 can split calculation across multiple processors or cores. When Excel 2007 loads a workbook, it determines from the operating system how many processors are available and then creates a separate calculation thread for each processor. These threads can then run in parallel. The beauty of this system is that it scales extremely well with the number of processors.
Most workbooks show a significant improvement in calculation speed on a system with multiple cores. The degree of improvement depends on how many independent calculation trees the workbook contains. If you make a workbook that contains one continuous chain of formulas, it will not show any multithreaded calculation (MTC) performance gain, whereas a workbook that contains several independent chains of formulas will show gains close to the number of processors available.
A test on a range of workbooks with between 840K and 23K formulas using Excel 2007 on a Dual-Core system showed improvement factors from using MTC ranging from 1.9 to no improvement, with the larger workbooks tending to show the most improvement.
In collaboration with Intel Corporation, Microsoft conducted testing on a suite of user-created spreadsheets. Comparisons were done between Excel 2007 and Excel 2003. (A prerelease version of Excel 2007 was used, but little to no difference is expected with the final release version.)
Results showed calculation times ranging from no improvement to greater than theoretical (2x/4x) improvement on both the Dual-Core and Quad-Processor systems. Typical (median) improvement for a system with an Intel Dual-Core Pentium 4 at 3.0 GHz with 1 GB of RAM compared to the same file calculating in Excel 2003 were 48 percent, or a 1.92x speedup. Typical (median) speedup for a system with an Intel Quad-Core Xeon at 3.0 GHz with 4 GB of RAM were 76 percent, or a 4.17x speedup. Similar speed improvements were observed on other processors and platforms. Improvements beyond theoretical speedup (due to multithreading) are attributed to other performance enhancements in Excel 2007, such as enhancements to the speed of function execution.
Some Excel features do not use multithreaded calculation, for example:
The first time that Excel calculates a workbook on a computer that has multiple processors, you incur some overhead while Excel examines dependencies. Therefore, you can see the maximum performance increase on the second and subsequent calculations (although there is still usually improvement on the first calculation versus running the same task on the same speed of computer with a single processor).
You also incur this overhead the first time you calculate a workbook on a multiple-processor computer that has a larger number of processors than the computer on which you last saved the workbook. If you turn off MTC, or run Excel 2007 on a system that has a single processor, there is no performance gain or loss from the MTC feature. You can use MTC in Excel 2007 even in compatibility mode, and the information that is stored by the calculation can be reused even after the workbook is calculated and saved by using an earlier version of Excel.
Controlling the number of calculation threads
You can manually specify the number of threads to run simultaneously. This number can be more than the number of processors on the computer. This is useful if, for example, you have XLL user-defined functions dependent on long-running external calls to a database server. If the database server can process multiple requests in parallel, you can make very effective use of multithreading even on a single-processor system.
To control MTC options, click the Microsoft Office Button, select
Excel Options, select Advanced, and then select Formulas.