Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Office 365. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Setting the Calculation Default.
Durward wrote concerning a problem he was having with calculation settings in his Excel. He indicated that according to all he had read, the calculation setting for Excel (Manual or Automatic) defaults to Automatic. Yet somehow, when Durward opens a new workbook, the calculation setting is set to Manual. This happens on his work system as well as his home system. He wonders if there a way to reset the calculation setting back to Automatic.
Testing has shown that the calculation setting is set to Automatic by default. It will only be set to Manual if (1) you have changed the default workbook to one that has the calculation mode set to Manual; (2) if there is some sort of AutoOpen macro that sets the calculation mode; (3) if you have some automatically loading workbooks (XLSX or XLTX, including the Personal workbook) that have calculation set to Manual; or (4) if you start Excel by double-clicking, in Windows, on a workbook that has calculation set to Manual.
Note, especially, conditions 3 and 4. Excel may very well be starting with the calculation mode set to Automatic, but it is overridden by the setting within the file that is first opened. If that workbook has calculation mode set to Manual, then Excel presumes you want Manual as your default calculation mode for that session. The only solution to this problem is to open those workbooks, change the calculation mode in them, save them, and restart Excel.
The other option is to add an AutoOpen macro to any of your workbooks that absolutely must be opened with calculation mode set to Automatic, no matter what. This can be a simple macro, such as the following:
Please Note:
This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Office 365. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: 
Private Sub Workbook_Open()
Application.Calculation = xlCalculationAutomatic
End Sub
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (9310) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Office 365. You can find a version of this tip for the older menu interface of Excel here: Setting the Calculation Default.
Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!
When you make changes in a worksheet, Excel automatically recalculates everything that may be affected by that change. If ...
Discover MoreYou can configure Excel to specify what happens when you press Enter in a cell. This is normally done on a global basis, ...
Discover MoreThe Formula Bar is a regularly used feature in the Excel interface. You can, however, modify whether Excel displays the ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2018-10-12 12:54:05
Mark
Not sure I agree. I repeatedly find spreadsheets where the formula auto recalculation has apparently changed ON ITS OWN.
Perhaps under certain conditions, like broken links or converted from older version of Excel, Excel automatically changes formula auto-recalculation to Manual on its own. Could also be SharePoint-related since SharePoint is not fully compatible with Office.
I've been using spreadsheets since Lotus 123 (1986?). I have been designing and writing application code since 1996. I am a technology consultant and specialize in testing, so I know how to test, debug and validate. Believe me, its not always user error. I don't run macros, open one spreadsheet from another, don't have a startup template, and certainly would never change formula to manual recalc.
2018-09-09 01:38:30
Leo Doyle
I have written macro driven spreadsheets that roll forward activities from month to month, but I need to develop a sheet that will roll forward activities that are assigned to a day of a calendar week (eg: from the 2nd Tuesday of one month to the 2nd Tuesday of the next month), and display the date for that event.
Is there a formula that will do this, or do I need to have a macro that will work on lookup tables?
2016-02-24 10:10:23
Odili Victor
how do i change my default from automatic to manual
2015-03-26 17:10:37
JDR
I used to call this the "Manual Recalc Virus," because it would infect certain spreadsheets and it was hard to get rid of it once it started. It raised its ugly head again recently.
In this most recent episode, I discovered that my macro sheet, which always loads from XLSTART as a hidden workbook, had inadvertently been set to Manual. I had to unhide it to change it back to Automatic, but once it was resaved, it stopped setting other sheets to Manual.
2014-11-18 21:23:52
Mike
I regularly open Excel documents by clicking on the file in a File Explorer folder (option 4 above) and randomly find that the workbook is set to manual calculation. I NEVER change the setting to Manual, so still can't understand why this should be changed by Excel.
Reading the above, am I right in understanding that, in the event that I discover one of the workbooks is in manual mode, I need to:
1. change this workbook to Autocalc and then close it,
2. check that any other workbooks open at the time are similarly in Autocalc mode and then close them,
3. Close Excel
4. Open the workbook files
And I then won't have the problem appear again?
2014-09-16 00:27:38
Arghya
How do I perform UP FILL command in Excel 2010. I know DOWN FILL by Ctrl+D. Please suggest.
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2019 Sharon Parq Associates, Inc.
Comments