Excel Formula Calculation Modes

By default, Excel is set to recalculate automatically. If you change any of the cells referenced in a particular formula, Excel automatically recalculates that formula so that it returns a correct result based on the changes in its cell references. Also, if the formula that it recalculates is used as a cell reference in other formulas, every formula that is dependent on the newly recalculated formula is also recalculated.

You can imagine that as your spreadsheet grows and gets populated with interweaving formulas, Excel will be constantly recalculating. You may even find that when working with worksheets that contain many complex formulas, Excel slows dramatically as it tries to keep up with all the recalculating it needs to do.

In these cases, you can choose to set Excel’s calculation mode to Manual. You can do this by clicking the Formulas tab in the Excel Ribbon and selecting Calculation Options→Manual.

While working in Manual calculation mode, none of your formulas will recalculate until you trigger the calculation yourself. You have several ways to trigger a recalculation:

  • Click the Calculate Now command on the Formulas tab to recalculate all formulas in the entire workbook.

  • Click the Calculate Sheet command on the Formulas tab to recalculate only the formulas on the currently active sheet.

  • Click the Calculate link on the status bar to recalculate the entire workbook.

  • Press F9 to recalculate all formulas in the entire workbook.

  • Press Shift+F9 to recalculate only the formulas on the currently active sheet.