Calculation Chain

The cells in a workbook can be calculated in different orders depending on various optimizations and dependencies. The calculation chain specifies the order in which the cells in a workbook were last calculated.

The calculation chain only deals with cells that require calculation - i.e., it only deals with cells that contain formulas. It does not track or express dependencies amongst the formulas, but rather only records the order in which the cells were last calculated.

The calculation chain order may change over time. One obvious way this can happen is that new formulas can be added, formulas can be removed or updated. The spreadsheet application may also optionally implement partial calculation as an optimization. Partial calculation is when the spreadsheet only recalculates cells that have had their dependencies or values changed. This way, when a number in a cell is changed, requiring an update to a dependent formula, only the cells that are affected by the update will be recalculated, as opposed to recalculating the entire workbook.

The calculation chain described in this section is not required by the spreadsheet application, but can be used if the spreadsheet application finds it useful. It can be loaded by a spreadsheet application, or the application may optionally construct it at run time in memory based on formula dependencies. Since the xml data described in this section is not strictly required, the spreadsheet application is free to ignore the order in which the calculation chain specifies calculations - i.e., even if the calculation chain is loaded, the spreadsheet application is free to perform calculations in a different order at run time.


Consider the following workbook (the formulas shown instead of cell values):

There is a constant entered in A1 and A5, and next to each of those cells are a series of cells which contain formulas that depend on those cells.

After entering the cells on the first row, and then the cells on the 5th row, the calc chain xml looks like this:

<calcChain xmlns="">
  <c r="E5" i="1"/>
  <c r="D5"/>
  <c r="C5"/>
  <c r="B5"/>
  <c r="E1"/>
  <c r="D1"/>
  <c r="C1"/>
  <c r="B1"/>

It is in this order because B1 was calced first (it was the first formula enterd in the workbook), followed by C1, D1, and so on. Then B5 was entered in the 5th row, followed by the other cells in the 5th row, ending with E5.

But, after a full recalculation, the spreadsheet application has realized that cells B5:E5 are on the same child chain, and cells B1:E1 are likewise on their own child chain. The xml now looks like this:

<calcChain xmlns="">
  <c r="B1" i="1"/>
  <c r="C1" s="1"/>
  <c r="D1" s="1"/>
  <c r="E1" s="1"/>
  <c r="B5"/>
  <c r="C5" s="1"/>
  <c r="D5" s="1"/>
  <c r="E5" s="1"/>