Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. 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.

Setting the Calculation Default

by Allen Wyatt
(last updated August 23, 2018)

6

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:

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

Note:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (9310) applies to Microsoft Excel 2007, 2010, and 2013. You can find a version of this tip for the older menu interface of Excel here: Setting the Calculation Default.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Differences between SEQ and LISTNUM Fields

Word provides several different fields you can use for custom numbering in a document. Two of the most commonly used are ...

Discover More

Understanding Views

Want to see how your document will look before it's printed? Or, do you want to see what things will look like if you put ...

Discover More

Resizing a Text Box in a Macro

Text boxes are easy to add to a document and manually resize, as needed. If you want to resize the text box in a macro, ...

Discover More

Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!

More ExcelTips (ribbon)

Adding an Equal Sign Tool

In older versions of Excel (97 and 2000) the program had an equal sign tool that appeared right next to the Formula bar. ...

Discover More

Changing Input Conventions

Different cultures have different conventions for displaying numbers and for parameters in Excel's worksheet functions. ...

Discover More

Turning Headers On and Off

Normally Excel displays row and column headers in a worksheet. If you prefer, you can turn these navigational aids off ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 9 - 6?

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.


This Site

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.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.