Jump to content


Calculation Options

Auto vs Manual

  • Please log in to reply
1 reply to this topic

#1 JPK1066


    Advanced Member

  • Members
  • PipPipPip
  • 101 posts
  • LocationHilton Head SC

Posted 22 April 2018 - 11:08 AM

So many times I find my WS's not updating the calculations the formulas generate only to find that the Calculation Option in the Formulas Tab is set to Manual. I never want that to be the case. I always want it set to Automatic. Is there a setting that would allow me to do that?





#2 Jonathan


    Forums Administrator

  • Administrators
  • 888 posts

Posted 22 April 2018 - 09:03 PM



You have run afoul of a quirk of Excel's calculation options - the setting is saved at Workbook level, but applies to Excel at Application level. You can see a more in-depth explanation in Expert Skills Session 3: Advanced Functions (Lesson 3-17: Understand calculation options (manual and automatic)).


In a nutshell, Excel's calculation mode is set that that of the last workbook that was opened and affects all open workbooks. This means that if you open a workbook that was saved with Manual calculation mode, all open workbooks will be set to Manual calculation. If you then open a workbook set to Automatic, all open workbooks will use Automatic mode.


To make matters worse, if you save any of your open workbooks they will be saved with the current calculation mode, so you can very easily get tied up in knots and start to think that the calculation mode is changing at random.


In your case it sounds like you have one or two workbooks in particular that have been saved with Manual mode and they are the cause of the mode switching when you open them. If you can locate these workbooks and re-save them with Automatic calculation mode you should find that the problem disappears.

Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users