Calculation

Q: How can I recalculate my workbook?

A: Excel uses an algorithm that only recalculates cells that have changed and those dependent on those cells within all open workbooks; these are sometimes referred to as "dirty" cells. So, if you have Excel's recalculation mode set as "Automatic", this is what it's doing al the time. If Excel's recalculation mode is set to "Manual", these are the cells that are recalculated when you press F9.

There are some occasions when you may need to perform a full recalculation. You do this by pressing Ctrl+Alt+F9. Excel recalculates all dependencies within all open workbooks.

Shift+F9 only recalculates dirty cells in the active worksheet.

Ctrl+Shift+Alt+F9 rechecks the dependencies between formulas and then recalculates all open workbooks. You can do something similar in VBA using:

    Application.CalculateFullRebuild

You don't have to write a procedure to use this: open the VBA editor and run it in the immediate window (Ctrl+G, if it's closed).

Q: How does Excel recalculate?

A: Since Excel 2002 builds a tree of dependencies based on the whole workbook and performs recalculation based on that tree. Versions, up to 2000, created dependencies based on each worksheet and would then calculated each one before moving on to the next sheet. If a subsequent sheet referred to a previous one, then the previous sheet would be recalculated to reflect the latest dependency found, which could slow down the whole process. Excel recalculated these sheets according to their alphabetical order.

Please note: Unless otherwise stated, these comments relate to Excel 2003. Other versions can differ.

 

Published: 19-Jun-2004
Last edited: 01-Mar-2011 20:50