Excel Set Calculation To Manual
In really large Excel 2016 workbooks that contain many completed worksheets, you may want to switch to manual recalculation so that you can control when the formulas in the worksheet are calculated. You need this kind of control when you find that Excel’s recalculation of formulas each time you enter or change information in cells has considerably slowed the program’s response time to a crawl.
My excel file contains a lot of formulas and I therefore want it to set calculation mode to manual as soon as it is opened. Otherwise calculation starts automatically and I have to wait for hours.
By holding off recalculations until you are ready to save or print the workbook, you find that you can work with Excel’s worksheets without interminable delays.
To put the workbook into manual recalculation mode, you select the Manual option on the Calculation Options’ button on the Formulas tab of the Ribbon (Alt+MXM). After switching to manual recalculation, Excel displays CALCULATE on the status bar whenever you make a change to the worksheet that somehow affects the current values of its formulas. Whenever Excel is in Calculate mode, you need to bring the formulas up-to-date in your worksheets before saving the workbook (as you would do before you print its worksheets).
To recalculate the formulas in a workbook when calculation is manual, press F9 or Ctrl+= (equal sign) or select the Calculate Now button (the one with a picture of a calculator in the upper-right corner of the Calculation group) on the Formulas tab (Alt+MB).
Excel then recalculates the formulas in all the worksheets of your workbook. If you made changes to only the current worksheet and you don’t want to wait around for Excel to recalculate every other worksheet in the workbook, you can restrict the recalculation to the current worksheet. Press Shift+F9 or click the Calculate Sheet button (the one with picture of a calculator under the worksheet in the lower-right corner of the Calculation group) on the Formulas tab (Alt+MJ).
If your worksheet contains data tables that perform different what-if scenarios, you can have Excel automatically recalculate all parts of the worksheet except for those data tables by clicking Automatic Except Data Tables on the Calculation Options button’s drop-down menu on the Formulas tab (Alt+MXE).
To return a workbook to fully automatic recalculation mode, click the Automatic option on the Calculation Options button’s drop-down menu on the Formulas tab (Alt+MXA).
In Excel, the workbook calculation of formula is attached to each file, set by File -> Options -> Formulas -> Calculation options -> Workbook Calculation.
However, if Excel is opened directly, not by opening a file, a 'Book1' will be opened and the setting is always 'automatic'. How could I make the default setting 'manual'?
I found these instructions below:
Changing the default is done in the following way:
Create a new Excel document in which all default settings are set e.g. fonts, styles, logo, header, footer, ...
Save the file as book.xlt in the excel system startup folder
File > Save as
Browse to the Excel system startup folder
File name : book
Save as : Template (*.xlt)
Note:Excel searches the following directories to open all files in it when it is started
'User Excel Startup Folder'
c:winntProfilesApplication DataMicrosoftexcelxlstart
c:Documents and SettingsApplication DataMicrosoftexcelxlstart
'System Excel Startup Folder'
c:Program FilesMicrosoft Officeofficexlstart
'Alternate Startup Folder'
However it seems it's talking about Windows NT. My OS is Windows 7.
Gaff2 Answers
Do as Sir Adelaide states above, in his answer, but try saving the new workbook as book.xltx (or .xltm if you want to include macros) in the XLStart folder. Restart Excel and see if it does what you want it to do.
The XLSTART folder should be in a location similar to this for 2010: C:Users<username>AppDataRoamingMicrosoftExcelXLSTART
Where: <username> is the name of your profile on the machine.
Open a new blank workbook.
Change the setting to manual calculation.
Save As, select file type 'Excel Template (*.xltx)'. This will change your explorer location to somewhere near where we want to be.
Don't save yet, but in the 'breadcrumbs' at the top of the Save As explorer window click on 'Microsoft', then browse into the folder 'Excel>XLSTART'. You'll be somewhere like 'C:UsersAthosAppDataRoamingMicrosoftExcelXLSTART'
Now you are in the right spot, but don't save yet. Change the file type back to 'Excel Workbook (*.xlsx)' and save with any file name.
Sir AdelaideSir Adelaide