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 . |
Calculation Mode operates at Application rather than Workbook level. | When Excel has no workbooks open, or when you start Excel, it sets the initial calculation mode and settings from the first non-template, non-addin, non-Personal.xls workbook that you open This means that the calculation mode setting in subsequently opened workbooks will be ignored, although you can change the mode yourself at any time using Tools-->Options-->Calculation. As soon as you change the calculation mode, it applies to all subsequently opened workbooks. If you want to override the way Excel sets the initial calculation mode you can set it yourself by creating a module in ThisWorkbook (doubleclick ThisWorkbook in the Project Explorer window in the VBE), and adding this code. This example sets calculation to Manual. Note that this will NOT prevent the workbook being recalculated if it is opened in Automatic mode. Private Sub Workbook_Open() Application.Calculation = xlCalculationManual End Sub If calculation is set to Automatic when a workbook containing this code is opened, Excel will start the recalculation process before the Open event is executed. You can bypass this problem by:
If you have a workbook that was saved in Automatic mode, but you want to open it in Manual mode:
If you have a workbook that was saved in Manual mode, but you want to open it in Automatic Mode:
|
Usually it pays to un-check the Iteration box. | If you have intentional circular references in your workbook, these settings allow you to control the maximum number of times the workbook will be recalculated (iterations) and the convergence criteria (maximum change: when to stop). The default should be to un-check the iteration box so that Excel does not try to solve accidental circular references. |