Introduction to Financial Modelling
Principles of Excel as a Modelling Tool
Excel Operations, Structures and Short-Cuts
Introduction to Excel Functions
Applications of Lookup and Reference Functions
Planning and Building Models for Optimal Decision Making

2.6 Excel Calculation Options

Excel has three “Calculation Options”, which are shown in the image below. These options may be changed under the File>Options>Formulas menu. They will be retained when the workbook is saved.

Automatic is the default (when Excel is first installed). This means that – whenever an input value is changed or formula entered (and when some other operations are performed) – the Excel workbook will recalculate to reflect the effect of these changes i.e. all cells whose values depend on the changed input cells will be updated. (This applies to all dependent cells, whether directly dependent or dependent only via another intermediate variable).

 

 

If the Manual option is chosen, then changes to items (such as an input value) will have no effect until the F9 key is pressed (or the equivalent command made e.g. using a macro). The Manual option is useful if one is working with large workbooks whose recalculation is slow, so that one may wish to recalculate it only after several changes or manipulations have been made, and not for each change.

As an example, with the Sales Revenue model used earlier, if one first sets the workbook to Manual calculation, and then change its inputs to zero, then the figures shown for the calculations are incorrect, because they have not been updated to reflect the changed input values (the values of zero are used only to make this clear, the principle would apply generally).

 

To update the calculations when in the Manual workbook calculation mode, one can press the F9 key. Doing this will result in the calculations being updated as expected:

 

The option “Automatic except data tables” is useful in large models which may update slowly and for which there are Data Tables (see Chapter on Sensitivity Analysis), in order to allow more efficient working by not waiting for the full set of sensitivities to update each time an input item is changed, whilst nevertheless having the correct figures shown in the main model area.

[Note that the calculations settings relate to the whole workbook i.e. all worksheets. It is possible to invoke settings for individual worksheets by using VBA macros, but this is beyond the scope here, and generally not recommended except in very specialised applications].

Scroll to Top
error: Alert: Content is protected !!