What is calculation mode in Excel? Automatic vs manual calculation

Most people meet it the same way: a total that refuses to move after an input changes, and half an hour auditing formulas that were correct all along. Ablebits names the check to run first — "If all of a sudden your Excel formulas have stopped calculating, go to Calculation Options and make sure the Automatic setting is selected."

The three modes

The switch lives in two places. On the ribbon it is Formulas → Calculation → Calculation Options; in the dialog it is File → Options → Formulas, under Workbook Calculation.

The middle option is narrower than its name suggests, and Ablebits flags the confusion worth avoiding: do not confuse "Excel Tables (Insert > Table) and Data Tables that evaluate different values for formulas (Data > What-If Analysis > Data Table). This option stops automatic recalculation of data tables only, regular Excel tables will still be calculated automatically."

Switching to manual has a side effect that is easy to miss: when you select Manual, "Excel automatically selects the Recalculate workbook before saving check box." Clearing it makes saving faster on a formula-heavy file, at the cost of saving numbers nobody brought up to date.

Forcing a recalculation

In manual mode nothing happens until you ask, and the four shortcuts differ in how much they do.

The distinction that matters is changed versus all. F9 recalculates "only those formulas that have changed since the last calculation and formulas dependent on them", where Ctrl+Alt+F9 "forces Excel to recalculate absolutely all formulas in all open workbooks, even those that have not been changed". Ablebits gives the case for the heavier one: "when you have the feeling that some formulas are showing incorrect results, use this shortcut." The ribbon equivalents are Calculate Now and Calculate Sheet.

Why a workbook you did not set opens in manual

This is the behaviour that turns a personal preference into somebody else's problem, and it is documented. Microsoft: "In Microsoft Excel, all currently open documents use the same mode of calculation, regardless of the mode in which they were saved."

The rule that decides which mode wins is first-come: "The first document that is opened uses the calculation mode with which it was last saved. Documents that are opened later use the same mode." Microsoft's own example is the whole behaviour — "if you open Auto1.xlsx and then open Manual1.xlsx, both documents use automatic calculation … If you open Manual1.xlsx and then open Auto1.xlsx, both documents use manual calculation."

Three consequences follow. Changing the mode in one open file changes it everywhere: "if Auto1.xlsx and Auto2.xlsx are both open, changing the calculation mode of Auto2.xlsx to manual also changes the mode of Auto1.xlsx to manual." Every sheet in a workbook shares the mode. And the mode travels with the file on save — so a workbook opened second, inheriting manual from a colleague's model, and saved, is manual for whoever opens it next.

If you genuinely need two files in different modes, Microsoft's advice is procedural: set the open workbook to the mode you want before opening the second, or close everything first.

Spotting a stale workbook

Recent builds of Excel mark the affected cells. Microsoft: "When the underlying data for a formula in a cell changes, however the formula has not been recalculated yet, then that cell is considered stale. In Partial and Manual Calculation modes, Excel denotes a stale cell with a strikethrough to alert users that its value is not yet reliable and should be recalculated to account for recent changes."

Selecting one shows a warning icon offering "options such as triggering calculation or switching to automatic calculation mode", and "once the calculation resumes and completes, the strikethrough formatting will be removed". The feature can be turned off under Formulas → Calculation Options → Format Stale Values, though Microsoft advises against it: "Formatting Stale Values reduces the risk of relying on out-of-date values, so we recommend keeping this option enabled."

Manual mode is a legitimate tool on a model that takes twenty seconds to recalculate — but reach for it after asking why the file is slow. Volatile functions are the usual answer, and removing them fixes the cause rather than hiding it. Iterative calculation, the fourth control in the same dialog, belongs to Circular references.

Google Sheets

Sheets does not present the same three-way switch. What Google documents is a recalculation frequency setting, reached at File → Settings → Calculation, offering "Recalculation: Sets how often certain formulas are updated" and "Iterative calculation: Sets the number of times a formula with a circular reference can occur" — the latter covered under Circular references.

That control is narrower than Excel's. Google explains that ordinary dependency-driven recalculation is automatic — "if other tabs, cells, or formulas refer to A1, then whenever you change A1, a recalculation is automatically triggered" — and that the setting exists for "volatile functions such as TODAY, NOW, RAND, and RANDBETWEEN, because those values inherently change all the time". The Sheets question is not whether formulas will update but how often the clock-driven ones should.

How Formula Foundry handles this

Formula Foundry does not change calculation mode, read it, or warn you about it. The add-on has no calculation engine at all — it parses, formats, explains and translates formulas, and the spreadsheet computes every number. If a total is not moving, the fix is in Excel's Calculation Options or Google's calculation settings, and nothing in the sidebar substitutes for looking there first.

Where it earns its place is the half hour after you have ruled the setting out. A number that is wrong rather than stale means reading formulas somebody else wrote, and that is what the editor is built for: the selected cell's formula opens across multiple lines, indented along the nesting, with functions, ranges, constants and named references each in their own colour. The visual builder renders the same formula as a form with every argument named, and the AI assistant explains it step by step in plain English.

One related habit helps. Workbooks that end up in manual mode are usually the slow ones, and slow ones are usually full of repeated constants and copy-pasted variations of one formula. Global @@variables put a rate or an FX figure in a single named place used as @@TaxRate, and the snippet library keeps the approved version of a pattern so the next person builds from it rather than from a neighbour's cell. The audit walkthrough runs that sequence on an inherited file.

FAQ