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

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

Turning Off Error Checking

A little green triangle in the corner of a cell means that Excel thinks there is an error with the cell contents. If ...

Discover More

Get Rid of Web Stuff

When you copy information from a Web page and paste it into a worksheet, you can end up with more than you bargained for. ...

Discover More

Calculating Weekend Dates

Do you look forward to the weekend? Well, you can use Excel to let you know when the next weekend begins. Here's how you ...

Discover More

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development 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

Displaying Page Breaks

Page breaks can be added to a worksheet manually or automatically. If you want to see where Excel places page breaks, ...

Discover More

Problem with Missing Context Menu Option

When you right-click a cell, does it seem that the Context menu is missing an item or two? Here's how to get those items ...

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 five more than 8?

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.