Excel 2008 : [Solved] How to always open spreadsheets with auto-calc disabled

Hi All,

I have done some searching through various forums trying to find a solution for this, but have only seen information applying to individual sheets and books. Apologies if the answer is out there and I overlooked it. As an aside, I work in a support role and am trying to solve this for a client. I'm not much of an excel user myself, so apologies if my terminology is off.

I'm looking for a way to permanently configure auto-calc so that it is always set to manual, and disable 'calculate before saving'. Currently, it appears that this setting stays with the spreadsheet, and is not a general always-use setting for the application. Is there a way to override this? Perhaps prevent excel from checking for that setting when opening new spreadsheets (spreadsheets received from coworkers)?


Thanks in advance ~

Calculation is set an App level setting and is in effect based upon the calculation state of the first file opened in any given session (itself based on state as and when saved).
see: http://www.decisionmodels.com/calcsecretse.htm

You could create a personal.xls * file saved with the calc settings you require.

This file will be opened automatically as and when XL instance is initiated and calc settings will be applied.

* The steps for doing this will be based upon your client's XL version: http://www.rondebruin.nl/personal.htm


(worth adding that VBA can amend these settings at any time - if coded to do so - if running 2008 this isn't a problem )

Disregard the above ... doesn't hold true... at least not in XL2007 (for me)

Having tested further...

The process should work as long as the personal file is saved with an extension appropriate to version - ie post XL2007 you can't use an .xls file (which was I was testing with).

With an .xls file in 2007 - though it will adjust the calc settings on open the calc settings in subsequent files override - ie because of Compatibility Mode the settings of personal don't persist (seemingly).

I hope that makes some sense... sorry for confusion.

If you follow the great Ron de Bruin's advice you should be ok (I think).

Let us know if you resolve your issue.... if running 2008 create the .xlsb file as outlined on Ron's page.

Incidentally - this 2007 issue seemed interesting so I investigated further.

On further testing it seems that if the first file opened in 2007 is named personal.xls then though the settings of personal.xls are (as expected) applied initially they will not persist as and when subsequent files are opened (the settings are superseded)

If the file is named anything other than personal.xls the settings do persist.

In essence then the issue is solely down to whether or not the first file opened is "personal.xls" and is not as I initially speculated related to Compatibility Mode in any way or shape or form.

Note: the location of personal.xls is seemingly of no consequence
It does not matter where personal.xls is opened from - be it "auto open" c/o either XLStart / designated start up folder (as set via Options) or opened manually from any location.


Apologies for the copious amounts of misinformation provided (thus far...)

In fear of being ridiculed as a Class A1 Muppet I decided to ask Charles Williams about this, he kindly took pity & replied with the following:

Originally Posted by Charles Williams

As far as i know personal.xls and other hidden workbooks like XLAs and Templates have never persisted their application.calculation settings.
(see Controlling Calculation http://www.decisionmodels.com/calcsecretse.htm
although I see that it does not specifically mention Personal.xls, probably because I almost never use it)

So it would seem after a little more testing that in 2007 both personal.xls and personal.xlsb fall into this XLA/Template trap and their settings do not persist.

Conversely the calc. settings of a file by any other name that is opened on start up will persist irrespective of visibility and open process (automatic / manual).

If anyone else has a definitive on this I'd appreciate it as I've been going in circles a little.

NOTE: I was looking exclusively for a non-VBA approach given OP stated use of XL2008 (no VBA support)

There wouldn't be a personal macro workbook because there are no macros in Excel 2008. (In any version of Excel, you would have to create it yourself though - it's not present by default). Just use another startup workbook as DO suggested.