How Excel Calculates

I'm currently investigating a technique proposed by a client for reorganizing one of their more complex and dynamic Excel workbooks. I'm supposed to determine how badly the new technique will impact the workbook's calculation performance.

As proposed, the new technique makes liberal use of INDIRECT. Unfortunately, INDIRECT is volatile, which means that its appearance in a cell's formula will cause the cell to be calculated with every recalculation, whether it needs to be or not. However, determining what impact this will actually have on the workbook's performance depends on a lot of things, such as the workbook's existing auto/manual/range calculation control logic and the total number of inter-book/sheet dependencies.

In any case, while investigating this, I found some resources worth sharing.

• Excel Calculation Secrets
  The folks at Decision Models have kindly assembled and published a lot of useful information regarding Excel's calculation engine, including a list of the volatile functions, some background on partial and full recalculations, the "Calculate" status bar message, and differences in the Excel 97/2000/2002 calculation engines. There's a lot of good stuff here.
  
  
• Recalculation in Microsoft Excel 2002
  This is an MSDN technical article that describes in excruciating detail the improvements made to Excel 2002 in "recalculating large, complex models that span multiple sheets." It includes an explanation of how Excel tracks changed cells and then combines this with a list of linked cells to perform optimal recalculations.

I'll try to post some benchmarks after I get them together. I'll also be exploring some non-volatile alternatives to INDIRECT and will try to write some about that, too.