Calculation modes and types
This is currently the start of a rough outline of what the page could contain.
The two main settings that impact the way that Excel calculates are (1) the calculation mode, and (2) the option to force full calculations.
The Excel calculation mode can be:
- Automatic, which means that Excel tries to automatically perform a regular calculation (more on that later, in the section on calculation types) every time that there is any change in workbook content that could impact results. This includes updating the results of data tables.
- Semi automatic, which is like automatic except that in semi automatic calculation mode, the automatically triggered calculations do not update results of data tables.
- Manual, which means that changes in workbook content do not automatically trigger calculations.
[Mention code for calculation modes, e.g. VBA]. Calculation mode is an application-level setting (i.e. it is the same for all workbooks open within an instance of Excel). The default calculation mode is automatic, however this is typically overridden because Excel switches to the calculation mode of the first workbook that gets opened, including any personal workbook (explain this or like to a page that explains it) and add-ins.
The option to force full calculations is switched off by default, and should be left off for almost all purposes. When forcing full calculations, Excel performs fun calculations wherever regular calculations would otherwise be called. The benefit of forcing full calculations is that Excel stops keeping track of which cells are dirty (dirty cells are the ones that will be evaluated at the next calculation) - which can improve performance of some workflows. The most commonly cited downside of forcing full calculations is that full calculations are slower than regular calculations - and that is often the case for financial models but it is not always true, some financial models perform full calculations faster than normal calculations. The more concerning problems when forcing full calculations relate to Excel not working properly - trace dependents becomes unreliable and charts usually stop refreshing with changed data. These Excel problems generally do not resolve themselves when forcing full calculations is switched off or the workbook is closed - to fix it, you need to close that instance of Excel.