Excel Manual Calculations vs Automatic Calculations and Converting data to Numbers

Excel has the ability to switch from manual calculations to automatic. Sometimes, this happens to sheets at what seems like random. I know there have been times when I’ve been working on a sheet, close it, and the next time I open it, my formulas aren’t updating until I click into the cell and then out of it. When manual mode is enabled, formulas won’t update until you explicitly edit a cell.

Sometimes, that is a huge benefit. Consider the following: You have a sheet with 10 columns x 10,000 rows where each column contains a number stored as text and you need to convert those rows. If you do this with automatic calculations on, it will take a while to run the conversion. With manual formatting, it’s almost instantaneous. Why? Because with automatic conversion, the sheet has to determine what needs to be recalculated after the conversion of every single cell. A brief description from the Microsoft documentation on calculation modes:

Excel performance: Improving calculation performance

The smart recalculation engine in Excel tries to minimize calculation time by continuously tracking both the precedents and dependencies for each formula (the cells referenced by the formula) and any changes that were made since the last calculation. At the next recalculation, Excel recalculates only the following:

  • Cells, formulas, values, or names that have changed or are flagged as needing recalculation.
  • Cells dependent on other cells, formulas, names, or values that need recalculation.
  • Volatile functions and visible conditional formats.

Excel continues calculating cells that depend on previously calculated cells even if the value of the previously calculated cell does not change when it is calculated.

Because you change only part of the input data or a few formulas between calculations in most cases, this smart recalculation usually takes only a fraction of the time that a full calculation of all the formulas would take.

In manual calculation mode, you can trigger this smart recalculation by pressing F9. You can force a full calculation of all the formulas by pressing Ctrl+Alt+F9, or you can force a complete rebuild of the dependencies and a full calculation by pressing Shift+Ctrl+Alt+F9.

A keyboard shortcut (Windows – not sure about Mac) to switch between manual and automatic calculation modes:

  • Alt + M, X, M (to manual)
  • Alt + M, X, A (to automatic)

In order for that to work, you must continue to hold the alt key and then press M, X, then M/A.