Controlling Excel Calculations
Look at these options
Tools -->Options-->Calculation tab.
Calculation Settings Keep Changing
Because a number of Excel's calculation settings work at the application level (they are the same for all open workbooks), and are set by the first workbook opened, they may appear to change randomly depending on the sequence in which workbooks are opened. FastExcel Version 2 allows you to solve many of these problems.
Automatic calculation mode means that Excel will automatically recalculate all open workbooks at each and every change, and whenever you open a workbook.
Usually when you open a workbook in Automatic mode and Excel recalculates you will not see the recalculation because nothing will have changed since the workbook was saved.
An exception is when you open a workbook in Excel 2000 that was saved using Excel 97, or you open using Excel2002/2003 a workbook saved in Excel2000: because the Excel calculation engines are different a Full calculation is done.
Press F9 if the status bar shows “Calculate”
Manual calculation mode means that Excel will only recalculate all open workbooks when you request it by pressing F9 or Ctrl-Alt-F9, or when you Save a workbook. For workbooks taking more than a fraction of a second to recalculate it is usually better to set Calculation to Manual.
Excel tells you when the workbook needs recalculation by showing Calculate in the status bar. If this message won’t disappear see Status bar shows Calculate .
Automatic except Tables:
Excel’s Data Tables feature is designed to do multiple calculations of the workbook, each driven by different values in the table. So using Automatic except Tables will stop Excel from automatically triggering the multiple calculations at each calculation, but will still calculate all dependent formulae except tables.
Application level settings:
The following calculation settings are held at the Application level rather than for each workbook, so they apply to all open workbooks.
Calculation Mode (Automatic, Automatic except Tables, Manual, Calculate before Save).
Iteration settings (Iteration On/Off, Max Iterations, Max Change).
Rather confusingly these application level settings are saved in each workbook, but only the settings in the first workbook opened are actioned: the settings in subsequent workbooks are ignored.