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, 2014)

4

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

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

Editing Individual Cells

Need to edit the data within a cell? There are any number of ways you can perform the edit; this tip documents them all.

Discover More

Quickly Entering Dates and Times

Excel provides keyboard shortcuts for a variety of purposes. This tip examines two such shortcuts, designed to allow entering ...

Discover More

Determining Sorting Criteria

If you need to know how a range of data is sorted, the task is not as easy as you might at first think. This tip examines why ...

Discover More

Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!

More ExcelTips (ribbon)

Displaying Excel's Developer Tab

The Developer tab of the ribbon is the gateway to many advanced features in Excel, including those features related to ...

Discover More

Changing an Invalid Autosave Folder

Excel allows you to specify where it stores various files used by the program. One location you can specify is where Autosave ...

Discover More

Controlling How Excel Interprets Percentages

When entering data in a worksheet, Excel tries to figure out how your entry can best be shown on the screen. When it comes to ...

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. 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 + 4?

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.