Excel calculation mode keeps changing: How to fix it!

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

• Automatic calculation – calculates when a value changes or based on specific actions (inserting, deleting, hiding rows/columns, or renaming a worksheet, etc.).
• Manual calculation – only calculates when explicitly requested by the user:
  • From the ribbon, click Formulas > Calculate Now (shortcut key F9) to calculate all open workbooks.
  • From the ribbon, click Formulas > Calculate Sheet (shortcut key Shift + F9) to recalculate only the active worksheet.

I’m sure 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 mode.

The differences between these two calculation modes can cause issues as they don’t always behave as we might expect. To some users, it may appear the calculation mode keeps changing all by itself. In this post, we’ll explore what causes this to happen, and how we can solve it.

Table of Contents

• Understanding the scope of the calculation mode
• What actions change the calculation mode?
• Settings changed by the user
• VBA code
• Office Scripts
• First workbook in session
• The saved calculation settings?
• How to fix Excel to always be in one calculation mode

Understanding the scope of the calculation mode

The calculation mode is an application-level setting. This means it applies to all the workbooks open in Excel. If Excel is in automatic calculation mode, all workbooks are in that mode. Equally, if Excel is in manual calculation mode, then all workbooks are also in manual calculation mode. It is one setting that applies to all open workbooks in the Excel session.

If using Excel Online, each browser window is treated as a separate application and therefore has its own calculation settings.

What actions change the calculation mode?

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

• Settings changed by the user
• VBA code
• Office Scripts
• The first workbook opened in a session

Let’s take a look at each of these in turn.

Settings changed by the user

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

Option 1: Formula ribbon

In the ribbon, click Formulas > Calculation Options > [Select: Automatic / Auto except data tables / Manual]

Option 2: Excel Options

Click File > Options from the ribbon to open the Excel Options dialog box.

Then, in the Excel Options dialog box click Formulas > Calculation options > [Select: Automatic / Manual / Auto except data tables].

Click OK to close the window.

Note: Using this method you can also enable/disable recalculating when saving.

What is a data table?

In the calculation options, the second item is automatic except for data tables. The data tables being referred to are the feature found under Data > What If Analysis > Data Tables…, it is not referring to the Tables feature.

Data tables are separated because they can cause slow calculation due to:

• Performing multiple recalculations of the workbook
• Using single-threaded calculations

Data tables are useful because they provide a way to calculate multiple versions of a calculation using different parameters, and then compare the variations between those calculations. However, as they can cause slow calculation an additional option is available.

VBA code

When using VBA, it is easy to change the calculation mode. The lines of code below are examples of the types of how 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 to manual at the start of a procedure, then change it back at the end, as it helps with speed.  However, if the macro errors or exits before completion, the calculation mode will not return to its previous state.

So, VBA can cause unexpected changes in calculation mode if the code is not written correctly.

Office Scripts

Office Scripts, like VBA, can change the calculation mode through code.

//Change to automatic calculation
workbook.getApplication().setCalculationMode(ExcelScript.CalculationMode.automatic)

//Change to manual calculation
workbook.getApplication().setCalculationMode(ExcelScript.CalculationMode.manual)

//Change to automatic except for data tables
workbook.getApplication().setCalculationMode(ExcelScript.CalculationMode.automaticExceptTables)

Unlike VBA, Office Scripts does not have any options to force calculation before save.

First workbook in session

The methods above make sense, as they involve user action or running a code. But the most common reason for the switch between automatic and manual is not as apparent.

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

Each workbook contains the calculation mode setting. Excel adopts the calculation mode of the first workbook opened in a session. Any workbooks subsequently opened also adopt the calculation mode of the first workbook, irrespective of its own settings.

This is the part that causes the most problems, as we don’t usually check calculation settings when opening a workbook in a new session.

NOTE: Excel Online treats each workbook as a separate application session therefore it cannot impact other workbooks.

The saved calculation settings?

When a workbook saves, the calculation mode at the time of saving is held within the workbook. This becomes a problem for anybody who does not understand calculation modes.

Let’s use an example:
Jack is a newish Excel user and does not know that calculation modes exist. He believes all Excel files are set to automatic calculation; that is all he has ever experienced. Jack’s work colleague sends him a workbook saved in manual calculation mode via e-mail. Jack opens the workbook, this happens to be the first workbook opened in Excel. Jack has no idea the calculation mode for the application has now been set to manual; he has no idea that manual even exists. Jack continues to work with Excel; opening, modifying, saving, and e-mailing workbooks. There are two issues key issues here:

1. The workbooks saved during that session may now be set to manual. If subsequent users open up those same workbooks first, their session too will change to manual.
2. Jack’s workbooks may not have calculated at the right point in the process, and therefore he has potentially created workbooks containing lots of incorrect values.

For many users, manual calculation mode can become like a virus that spreads without them knowing it. So, the lesson is to check your calculation mode regularly.

How to fix Excel to always be in one calculation mode

The quick answer is that there is no ‘fix’ because that is how Excel has been designed to operate. However, there are a few options to mitigate the potential problem:

Use VBA in all workbooks, which must be opened with automatic calculation
Adding a VBA workbook open event to your workbook will force the calculation mode to change to automatic each time the workbook is opened. The example code below should be inserted into the Workbook code module

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

However, there is one drawback to using a macro option. Depending on the users’ settings and whether they click the button to enable content, the macro may or may not execute.

NOTE: In Excel Online there is no way to automatically trigger an Office Script; therefore, there is no online equivalent.

Always open a specific workbook first
A better option may be to save a workbook with the correct calculation mode in your XLSTART folder. That workbook will always open first and set the calculation mode to automatic for the session.

An alternative to having a workbook in the start folder is to use a Personal Macro Workbook set to manual calculation, even if that it contains no macros. Find out more how to create a Personal Macro Workbook here: https://www.ablebits.com/office-addins-blog/excel-personal-macro-workbook/

This will not prevent the user, or macros from changing the calculation mode, but it should reduce the risk of triggering a change based on the first workbook opened.

NOTE: Excel Online does not have a feature to open a specific workbook when the application opens.

Related Posts

• Excel formulas not calculating? 14 reasons & how to fix it
• Excel can calculate the wrong results: WARNING
• Automatically open workbooks when Excel starts

---

What next?

Discover how you can automate your work with our Excel courses and tools.