Overview

If you have ever used custom functions in Excel, depending on the complexity of them, you have probably run into an issue where the accuracy of the results was sporadic. There is a quick solution. Use CTRL ALT SHIFT F9.

The lengthier explanation from Microsoft explains that the calculation of worksheets in Excel can be viewed as a three-stage process:

• Construction of a dependency tree
• Construction of a calculation chain
• Recalculation of cell

With the introduction of complex VBA functions, the default calculation can produce inaccurate results because it doesn’t evaluate the dependency tree and calculation chain correctly.

So, if you have this issue, the most complete and thorough (and time consuming) calculation can be initiated by clicking CTRL ALT SHIFT F9. This forces the dependency tree to be rebuilt and recalculates the entire workbook. There are several levels in forcing Excel to calculate.

F9

Recalculates all cells that Excel has marked as dirty, that is, dependents of volatile or changed data, and cells programmatically marked as dirty. If the calculation mode is Automatic Except Tables, this calculates those tables that require updating and also all volatile functions and their dependents.
VBA: Application.Calculate

SHIFT F9

Recalculates the cells marked for calculation in the active worksheet only.
VBA: ActiveSheet.Calculate

CTRL ALT F9

Recalculates all cells in all open workbooks. If the calculation mode is Automatic Except Tables, it forces the tables to be recalculated.
VBA: Application.CalculateFull

CTRL ALT SHIFT F9

Causes Excel to rebuild the dependency tree and the calculation chain for a given workbook and forces a recalculation of all cells that contain formulas.
VBA: Workbooks(reference).ForceFullCalculation (introduced in Excel 2007)

References

• Microsoft.com Article
• Microsoft Article (PDF)