Get our FREE VBA eBook of the 30 most useful Excel VBA macros.
Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.
Email Address * First Name *
By entering your email address you agree to receive emails from Excel Off The Grid. We'll respect your privacy and you can unsubscribe at any time.

Why does Excel’s calculation mode keep changing?

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.

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’s is one setting that applies to all open workbooks.

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
  • 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), 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 Formula ribbon, click Formulas > Calculation Options > [Select: Automatic / Auto except data tables / Manual]

Formulas - Calculation Options in Ribbon

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].

Excel Options - Calculation Mode

Click OK to close the window.

Note: if you are using option 2 and manual calculation, you can also enable/disable recalculating when saving.

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 exists 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.

First workbook in session

The two methods above make sense, as they involve user action or running a macro.  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 will 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.

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.

Formula Magic with Dynamic Arrays

Of all the features available in Excel dynamic arrays provide the most power for the smallest time investment.  Yet most Excel users do not even know what they are.

Have you ever faced these spreadsheet scenarios?

  • How can I use VLOOKUP to return all the matching items, not just the first?
  • How can I sort my information using a formula, so I don’t have to keep clicking the sort button?
  • How can I quickly create unique lists of items to use with my SUMIFS calculation?
  • How can I stop copying down formulas every time my source data changes.
  • How can I build a PivotTable-like report, but using formulas so I don’t have to click refresh ever again.

Well, I’m here to give you some good news. with dynamic arrays, all these can be achieved easily 🙂

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 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 with using a macro option.  Depending on the users’ settings and if they click the button to enable content, the macro may or may not execute.

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.

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.



Get our FREE VBA eBook of the 30 most useful Excel VBA macros.
Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.

Email Address * First Name *

By entering your email address you agree to receive emails from Excel Off The Grid. We’ll respect your privacy and you can unsubscribe at any time.


Don’t forget:

If you’ve found this post useful, or if you have a better approach, then please leave a comment below.

Do you need help adapting this to your needs?

I’m guessing the examples in this post didn’t exactly meet your situation.  We all use Excel differently, so it’s impossible to write a post that will meet everybody’s needs.  By taking the time to understand the techniques and principles in this post (and elsewhere on this site) you should be able to adapt it to your needs.

But, if you’re still struggling you should:

  1. Read other blogs, or watch YouTube videos on the same topic.  You will benefit much more by discovering your own solutions.
  2. Ask the ‘Excel Ninja’ in your office.  It’s amazing what things other people know.
  3. Ask a question in a forum like Mr Excel, or the Microsoft Answers Community.  Remember, the people on these forums are generally giving their time for free.  So take care to craft your question, make sure it’s clear and concise.  List all the things you’ve tried, and provide screenshots, code segments and example workbooks.
  4. Use Excel Rescue, who are my consultancy partner.   They help by providing solutions to smaller Excel problems.

What next?
Don’t go yet, there is plenty more to learn on Excel Off The Grid.  Check out the latest posts:

Save

Leave a Reply

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