Auto Calc

Post Reply
User avatar
Martin Ryan
Site Admin
Posts: 1988
Joined: Sat May 10, 2008 9:08 am
OLAP Product: TM1
Version: 10.1
Excel Version: 2010
Location: Wellington, New Zealand
Contact:

Auto Calc

Post by Martin Ryan »

Everytime I open Excel, AutoCalc is helpfully turned on for me. Disaster when I open a workbook full of DBS formulae.

When I remember, I go to Tools -> Options and turn it off, but when I restart Excel it's back on again.

Remind me where I turn this off permanently?

Cheers,
Martin
Please do not send technical questions via private message or email. Post them in the forum where you'll probably get a faster reply, and everyone can benefit from the answers.
Jodi Ryan Family Lawyer
User avatar
Eric
MVP
Posts: 373
Joined: Wed May 14, 2008 1:21 pm
OLAP Product: TM1
Version: 9.4
Excel Version: 2003
Location: Chicago, IL USA

Re: Auto Calc

Post by Eric »

That usually does it. However, a file can be saved with auto calc on, which will override your default setting. This also applies t you personal.xla file. If your setting is Manual but the personal.xla is auto it will overwrite the setting each time you open excel.
Regards,
Eric
Blog: http://tm1-tipz.blogspot.com
Articles: http://www.google.com/reader/shared/use ... /label/TM1


Production: 32 bit 9.0 SP2, Windows 2000 Advanced Server. Web: 32 bit 9.0 SP2, Windows 2000 Server. Excel 2003
User avatar
Alan Kirk
Site Admin
Posts: 6606
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2.0.9.18 Classic NO PAW!
Excel Version: 2013 and Office 365
Location: Sydney, Australia
Contact:

Re: Auto Calc

Post by Alan Kirk »

Martin Ryan wrote:Everytime I open Excel, AutoCalc is helpfully turned on for me. Disaster when I open a workbook full of DBS formulae.

When I remember, I go to Tools -> Options and turn it off, but when I restart Excel it's back on again.

Remind me where I turn this off permanently?
You can't, exactly. It's not one of Excel's brightest features. The issue is discussed here:

http://support.microsoft.com/kb/214395/en-us

In short,
The first document opened uses the calculation mode with which it was last saved. Subsequently opened documents use the same mode.
Eric got close to the correct answer; unless you specify a default template, and assuming that you have no other files in your startup folder, personal.xls will often be the first file opened and ergo will set the calculation mode for the session. If you DO have other startup files, from memory I don't think that you can predict the opening order.

However I've found creating my own default template to be the best solution, since it seems to load before anything else. (Though whether this is version specific, I'm not sure.) This is discussed in:

http://support.microsoft.com/kb/822107/en-us
If you save a workbook named Book.xlt, and then put it in a startup folder location, that workbook is the default workbook when you start Excel or open a new workbook.
Typically I include a couple of non-default styles that I like to use as well, and obviously set the calc mode to manual before saving book.xlt. I haven't had the problem since.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
Post Reply