Why does Excel’s calculation mode keep changing?

Calculation mode changes

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

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

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

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 aborts 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 the 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 saves.  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 saves, 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.
  2. The workbooks saved during that session are now set to manual.

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

Download the 100 Excel Macros ebook

100 Excel Macros Book

  • Contains 100 Excel VBA macros
  • Learn VBA by following along with the example codes
  • Apply to your macros, automate Excel, save time.

Download the ebook today!

Click the button below to subscribe, you’ll gain access to the subscriber area in which you can download the ebook.

Download Icon (on mid-green background)

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 are a few options:

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.

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:

13 thoughts on “Why does Excel’s calculation mode keep changing?

  1. Mitchell Allen says:

    Thanks for sharing this, Mark. This seems to fall under a class of related Workbook properties (I’m not aware of an official collection, but I run into a lot of weirdness like this.)

    You can hide grid lines and row/column headings but you can’t keep another person’s ribbon collapsed.

    Speaking of headings, the workbook will remember if it is A1 mode or R1C1 mode. That’s very disconcerting to the unaware.

    Finally, CustomProperties, which you set in VBA, persist until you change or delete them.

    These things are relatively easy to control if you are writing VBA, but it’s too bad average users have to deal with such inconsistencies.



    • Excel Off The Grid says:

      Hey Mitch – you’re right, it is certainly not clear for most users what impact these settings have. Some apply to the worksheet, some to the workbook and some to the application itself. But how is an average user meant to know.

      I recently saw a workbook which hid the formula bar on open, but did not re-instate it on close. All the users were confused as this is an application level setting, so hide the formula bar permanently. They all had to go into Excels settings and manually re-instate it.

  2. Pat Antonelli says:

    I just got upgraded from Office 2010 to 2016 yesterday, and this is not good news for me. I have a couple of very large, complex workbooks. Whenever I would work on them in Excel 2010, I would open a separate session (I hope my terminology is correct) where I would have the Calculation Options set to “Manual”, while all workbooks open in the other session would be set to Automatically calculate. In 2016 if I open a separate session as I would have in 2010, when I change the Calculation Option in one workbook, it changes it in all of them (grrrrrrr). This also means that, when the complex workbook is now manually calculating, I have to wait for that process to be over before I can do anything with the other workbooks. I’ve been searching for an answer as to how I can have different Calculation Options in separate workbooks that are open at the same time, and can’t find anything. Someone sent a link to this post to me. So does anyone know if it’s still possible to have different “sessions” open, with each having their own calculation options?

    • Excel Off The Grid says:

      Hi Pat,

      I can certainly understand your issue.

      The good news is that it’s still possible to open up separate sessions, the challenge is ‘how’ to open up those sessions. The previous methods all seem open within the same session, which you’ve discovered.

      But I created the macro below which when run will open up a new Excel session with a blank workbook inside it. If you kept the code within a Personal Macrobook you could then launch a new session any time you required.

      Sub CreateNewSession()

      Dim xlApp As Excel.Application
      Set xlApp = New Excel.Application

      xlApp.Visible = True

      Set xlApp = Nothing

      End Sub

      • Pat Antonelli says:

        Thanks Mark – this is very much appreciated, as it’s nothing I would know how to do at all! I will definitely be giving it a try and then using it very frequently.

      • Pat Antonelli says:

        Mark, I had a need to open a separate Excel session this morning for a workbook that I leave on Manual Calculation, and all I can say is: This is a beautiful thing! Thank you so very much!

  3. Dean Dolić says:

    Hi Mark,

    Thank you for the great Excel topics and posts.

    Can you please help me understand the following situation.

    I have excel file with multiple sheets which contains a lot of formulas and external data sources (pulling data from a external DB).

    This file is located on the network (LAN)

    Im accessing this file from 2 different PCs both running Excel 2010 and both being 32-bit versions.

    On one PC, calculation mode is is determaned by the last save and on other by condidions like you are describing in this post.

    So on PC1, I open this file, change mode to manual calculation and save the file. Then I open blank spreadsheet, set it to automatic and then open previous file. It should use automatic calculation right, but its not. It set to manual just like I saved it.

    On PC2 this is not the case…

    I have checked all other settings in file-options-advanced and file-options-formulas and on both PCs I have the same settings.

    Hows this posible?

    • Excel Off The Grid says:

      Hi Dean,

      Apologies for the late response. Unfortunately, your message was caught by the spam filter – not sure why.

      I’m not sure that I have a good answer to your question. I’ve just got a few more questions you could investigate.

      Do you have a Personal Macrobook, or any Add-ins installed?

      When you save and close the workbook are there any other workbooks open at all?

      If you open a blank workbook after closing what calculation mode does it open with?

      Hopefully one of these will lead to some progress.

    • MV says:

      I tried following:
      Private Sub Workbook_Open()
      Application.Calculation = xlCalculationManual
      End Sub
      Private Sub Workbook_Activate()
      Application.Calculation = xlCalculationManual
      End Sub
      Private Sub Workbook_Deactivate()
      Application.Calculation = xlCalculationAutomatic
      End Sub
      Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
      Application.Calculation = xlCalculationAutomatic
      End Sub
      Seems too work.

  4. Dean Dolić says:

    Hi Mark,

    No worries it happens 🙂

    Let me answer your questions:

    1. I dont have any personal Macros but we do use certain add-ins on both these PCs…

    2. Yes there are sometimes but I tested both cases.

    3. I have tested all situations and on one PC no matter if I open single or multiple files, when I choose computation mode and save that file, whenever I open it again it will always be in the saved mode. I can open other files and save them in other mode, reopen them again and then open the previous one… it will always remain in the mode in which I have saved it.

    This seems good but I think its not behaviour that is actually normal in Excel.

    Btw we are using 2010 version.

    • Excel Off The Grid says:

      Hi Dean,

      Hmmm… I’m stumped. Maybe it’s a question for a wider forum such as Mr Excel or the Microsoft Answers community.

  5. Gabriel says:

    Thanks for posting this! . The sudden changes from automatic to manual calculation are terrible. It confuses all my staff for weeks… I hope you can report it to windows. Thanks for posting the solution.

Leave a Reply

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