Excel Formula Calculation Modes – How to stop automatic recalculation in Excel 2013, 2016

By default, Excel is set to recalculate automatically formulas on workbook if you change any of the cells referenced in those formulas. 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 more and more mutually related formulas, Excel will be constantly recalculating. You may even find that when working with worksheets that contain many complex formulas, Excel slows down 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.

Calculation modes

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.

It’s such a simple trick, isn’t it?

Be the first to comment

Leave a Reply