Why does the calculation mode keep changing?

Calculation mode changes

Calculation mode changes

You’re probably aware that Excel has two main calculation modes: automatic and manual.

  • Automatic calculation re-calculates the workbook each time a value changes, or based on specific actions (such as inserting, deleting, hiding rows/columns, or renaming a worksheet).
  • Manual calculation only re-calculates when specifically requested by the user.  Formulas -> Calculation -> Calculate Now (shortcut key F9) to calculate all open workbooks.  Formulas -> Calculation -> Calculate Sheet (shortcut key Shift + F9) to recalculate only the active worksheet.

I think everybody would prefer to use automatic calculation, however formula complexity and large data sets can cause spreadsheet calculation to be slow.  As a result, some users set Excel to manual calculation.

These two calculation modes can cause issues as they do not always behave as we might expect.

 

Understanding the scope of the calculation mode

The calculation mode setting is an application level setting.  It applies to all the workbooks open in Excel.  If Excel is in automatic calculation mode, then all workbooks are in automatic calculation mode.  Equally, if Excel is in manual calculation mode, then all workbooks are in manual calculation mode.

 

What actions change calculation mode

So far, it all seems straight forward.  However, what causes the most confusion is understanding what actions cause the calculation mode to change:

  • Settings changed by the user
  • VBA code
  • First workbook open in a session

 

Settings changed by user

To my knowledge, (excluding VBA code) there are two ways for the user to change the calculation mode, via the Formula ribbon, or via the Options.

Formula Ribbon
Formulas -> Calculation -> Calculation Options

Calculation modes change with Ribbon

Options
File -> Options -> Formulas -> Calculation options

Calculation modes change with options

From either of these methods it is possible to select automatic, automatic except for data tables and manual as options.  The second method also allows changing the option of recalculating when saving.

 

VBA code

When using VBA, it is easy to change the calculation mode.

'Change to automatic calculation
Application.Calculation = xlAutomatic

'Change to manual calculation
Application.Calculation = xlManual

'Change to automatic except for data tables
Application.Calculation = xlSemiautomatic

'When in manual mode, calculate the workbook before saving
Application.CalculateBeforeSave = True

'When in manual mode, do not calculate the workbook before saving
Application.CalculateBeforeSave = False

With VBA it is common to change the calculation mode at the start of a procedure to manual, then change it back at the end to it’s previous setting.  If the Macro errors or is aborted before the completion of the code, the calculation mode will not return back to its previous state.  So, VBA code can cause unexpected changes in calculation modes.

 

First workbook in session

The methods above make sense, as they involve application changes from a user action or running a macro.  But the most common reason for change is not as obvious.

The calculation mode is most often set based on the calculation mode of the first workbook opened in the Excel session.

Each Excel workbook contains the setting of the calculation mode at the point it was saved.  The Excel application will adopt that calculation mode if it is the first workbook opened in a session.  Any workbooks opened after this will not change the calculation mode.

 

Which calculation mode are workbooks saved with?

When a workbook is saved, it is the calculation mode at the time of saving which is saved back into the workbook.  This can become a problem for anybody who does not understand calculation modes.

Let’s use an example:
John is a new-ish Excel user and does not know that calculation modes exist.  He believes all Excel files are set to automatic calculation, because that is all he has ever experienced.  John’s work colleague sends him a workbook saved in manual calculation mode via e-mail.  This is the first workbook opened in John’s Excel session (he has no idea the calculation mode has now changed to manual, he has no idea that manual even exists).  John continues to work with Excel; opening, changing, saving and e-mailing workbooks.  There are two issues key issues here (1) John has potentially emailed workbooks containing data errors caused by the calculation mode (2) all the workbooks saved during that session are now set to manual.

To inexperienced users, changing calculation modes can become like a virus which spreads throughout workbooks without them knowing it.  So, check your calculation mode regularly.

 

The bug which may catch you out

There is one ‘bug’ which may catch you out.  If a workbook is saved with multiple sheets selected the calculation mode will display as manual, even though it is automatic.  When a single worksheet is then selected the mode will display to automatic again.  Whether this has further consequences, and which versions of Excel it affects, I am not sure.  But just be aware of it.

 

How to fix your Excel to always be in one mode

The quick answer is that there is no ‘fix’, but there is a couple of workarounds:

Use VBA in all the workbooks which must be opened with automatic calculation
Adding a workbook open event to your workbook will force the calculation mode to change to automatic each time the workbook is opened.

Private Sub Workbook_Open()
Application.Calculation = xlCalculationAutomatic
End Sub

Always open a specific workbook first
Save a workbook with the correction calculation mode in your XLSTART folder.  That workbook will always open first and set the calculation mode.

 

What next?

Get Excel news, tips & tricks straight to you inbox.  Helping you to save time and achieve more with Excel.

You will also receive the Tab Hopper Add-in for FREE.

Tab Hopper Thumb







Save

Save

Save

Save

Save

Leave a Reply

Your email address will not be published. Required fields are marked *