If Excel is not automatically updating formula results, set calculation to Automatic: go to Formulas > Calculation Options > Automatic. If that option is already selected, force a recalculation with Ctrl+Alt+F9, then check whether the affected cells contain real formulas rather than text.
What automatic calculation does
Excel normally recalculates formulas when a cell they depend on changes. For example, if a total adds several cells and one of those values changes, Excel updates the total without you having to run a separate command. The calculation setting controls when that updating happens; it does not repair an invalid formula or refresh every kind of external information.
Calculation mode is shared by open workbooks in the Excel application, so one workbook can appear to affect another. A file received from someone who uses Manual calculation may open with results that do not change as expected. This is especially confusing when the sheet looks normal and contains no visible error messages.
Automatic calculation can also be busy rather than broken. Large workbooks, complex formulas, data tables, and links to other files may take time to update. If only one result is wrong, investigate that formula and its inputs; if many unrelated results are stale, check the calculation mode first.
Turn Automatic calculation back on
In Excel for Microsoft 365 on Windows, open the Formulas tab, choose Calculation Options, and select Automatic. You can also open File > Options > Formulas, find Workbook Calculation, select Automatic, and choose OK.
- Save the workbook, particularly if it contains important or complex work.
- Choose Formulas > Calculation Options > Automatic.
- Change an input cell or force a recalculation to confirm that dependent results update.
- Save again after the displayed results are current.
On Excel for Mac, open Excel > Settings > Calculation and select Automatic. Some older releases label Settings as Preferences. The Formulas tab also provides calculation controls in current desktop versions. In Excel for the web, check Formulas > Calculation Options and choose Automatic if the control is available in your version.
If Automatic is already selected, do not keep switching it on and off as a substitute for diagnosis. Use a recalculation command, then check for formula text, dependency problems, errors, or data that needs a separate refresh.
Force Excel to recalculate
A regular recalculation is a useful first test when results seem stale. On Windows, press F9 to calculate open workbooks. Press Shift+F9 to calculate the active worksheet. On some laptops, you may need to hold the Fn key to use the function-key command. In the desktop app, you can also use Formulas > Calculate Now or Formulas > Calculate Sheet.
If those commands do not update the result, try a full calculation with Ctrl+Alt+F9. This recalculates formulas regardless of whether Excel considers their inputs changed. For a more thorough rebuild of the calculation dependencies followed by recalculation, use Ctrl+Shift+Alt+F9 on Windows. These commands can take time in a large workbook.
For a simple arithmetic test, with 12, 8, and 5 in cells B2:B4, enter this formula in B5: =SUM(B2:B4). The expected result is 25. If editing one of the three input cells does not update B5, check the calculation mode. If the test works but another formula does not, focus on the affected formula or its references.
Check whether Excel is showing formulas as text
If cells display formula expressions instead of answers, Excel may be set to show formulas on the worksheet. On Windows, press Ctrl+` (the grave-accent key, usually beside the number 1) to toggle this view, or select Formulas > Show Formulas. The formula bar may still show a formula normally; the key clue is that worksheet cells display expressions such as =SUM(B2:B4) instead of their results.
A single cell may also be stored as text. Select it and inspect Home > Number Format. If the format is Text, change it to General, then make Excel interpret the entry again: press F2 and then Enter on Windows. Merely changing the format may not convert an existing text entry into a working formula. A leading apostrophe before an equals sign can also make an entry text rather than a formula.
Look for other signs of text storage, such as a formula that appears left-aligned or an apostrophe visible in the formula bar. If many cells were pasted or imported as text, select the affected cells, change their format to General, and re-enter the formulas or use an appropriate conversion method. Check a few results before applying a bulk change to a large range.
Inspect the formula and its dependencies
Automatic calculation updates valid formulas when their referenced inputs change, but it cannot correct a wrong reference. Select the result cell and inspect the formula bar. Confirm that the formula refers to the intended cells, that copied formulas have not shifted to an unintended range, and that the cells being edited are actually among its precedents.
For instance, if a total uses B2:B10 but the new value was entered in B11, the total will remain unchanged because B11 is outside the referenced range. Similarly, a formula that refers to another worksheet may not respond to edits made in a similarly named sheet. Trace Precedents and Trace Dependents on the Formulas tab can help show relationships in supported desktop workbooks.
Check for errors such as #VALUE!, #REF!, or #NAME?. Those indicate a formula or reference problem, not simply a disabled automatic calculation setting. A result that looks unchanged can also be correct: changing an input from 10 to 10, for example, gives the formula no different value to display.
Separate recalculation from data refresh
Not every value in a workbook is recalculated in the same way. A formula that reads cells in the workbook is handled by Excel’s calculation engine. A Power Query result, imported table, PivotTable, linked workbook, or external data connection may instead require its own refresh. Pressing F9 does not necessarily retrieve new information from an external source.
- For a query or connection, use the workbook’s Data tab and its refresh controls, such as Refresh All, where applicable.
- For a PivotTable, use its refresh command to update the summarized source data.
- For a formula linked to another workbook, confirm that the source file is available and that Excel can update the link.
- For a data table used in what-if analysis, check whether the calculation option is set to Automatic except for data tables.
External connections can require network access, credentials, permission, or a compatible source file. A refresh may fail or be blocked even while ordinary worksheet formulas calculate correctly. Treat the connection’s status and prompts as separate clues rather than repeatedly forcing formula recalculation.
Look for circular references and calculation settings
A circular reference occurs when a formula depends on its own result, either directly or through a chain of other cells. For example, a formula in C5 that refers to C5 creates a direct circular reference. Excel may warn about circular references, and the result may not behave like a normal formula. Use the Formulas tab’s error-checking tools to locate a circular reference.
Some specialized models intentionally use iterative calculation to work with circular references. That setting is available under File > Options > Formulas on Windows. Do not enable it just because a formula is not updating: first confirm that the circular relationship is intentional and understand the iteration limits and precision settings. Iteration can make results depend on those settings and should not conceal an accidental reference.
Also inspect the calculation choice for data tables. Excel can be set to Automatic except for data tables, which calculates most formulas automatically but leaves data tables out of ordinary recalculation. If only a what-if data table is stale, this setting may explain the difference; use a suitable calculation command or change the option only if that fits the workbook’s needs.
A practical troubleshooting order
Follow these checks in sequence to distinguish a global setting from a problem limited to one cell or data source:
- Check Formulas > Calculation Options and select Automatic.
- Run Formulas > Calculate Now, or use F9 on Windows.
- Confirm the cell contains a formula, not text, and turn off Show Formulas if the whole sheet displays expressions.
- Inspect the formula’s references, input values, and any error message.
- Check for circular references and the Automatic except for data tables setting.
- Refresh a query, PivotTable, connection, or external link separately if the stale value comes from one.
- If the workbook remains inconsistent, save a copy, close and reopen Excel, and test the copy before making larger changes.
Saving a copy before troubleshooting protects the original while you investigate. Be cautious with downloaded workbooks and macros: calculation settings do not make an unknown file or code safe. Do not enable macros or external connections unless you trust their source and understand the prompt.
Frequently Asked Questions
Why does Excel keep switching to Manual calculation?
Calculation mode is shared across open workbooks in the desktop application, and opening workbooks with different saved calculation settings can make the active mode surprising. Set Automatic, check the other open workbooks, and reopen the intended file if the setting changes again.
Does F9 change the calculation mode?
No. F9 requests a calculation; it does not select Automatic. If the workbook is set to Manual, use F9 when you want to update results, or change the mode through Formulas > Calculation Options.
Why does one formula stay unchanged while other formulas update?
Check whether its referenced inputs actually changed, whether the formula includes the edited cells, and whether the cell is stored as text. Also inspect the formula for errors or a circular reference. A working calculation setting cannot fix an incorrect range.
Will recalculation update a linked file or Power Query result?
Not necessarily. F9 recalculates worksheet formulas, while external links, queries, connections, and PivotTables may need their own refresh command. The source may also be unavailable or require permission.
Why does my formula show in the cell instead of a result?
Show Formulas may be enabled for the worksheet, or the individual cell may be formatted as Text or begin with an apostrophe. Toggle Show Formulas off, or change the cell to General and re-enter the formula.
Related
- Column J Number in Excel: How to Find and Use It Easily
- Excel Column K Number: What It Means and How to Find It
- ICD-10 Z Codes List in Excel: Find, Import, and Check
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- Excel #NAME? Error: How to Fix ItIf Excel displays #NAME?, it cannot recognize text in the formula, such as a misspelled function, an undefined…
- VLOOKUP a ZIP Code Not Working? Fix Common Match ErrorsIf VLOOKUP cannot find a ZIP code, first check whether the lookup value and the ZIP codes in…
- Why Is the YEAR Function Not Working in Excel? FixesThe YEAR function usually fails because the referenced value is text or an invalid date, or because Excel…
- Excel UNIQUE Spill Error: Causes, Fixes, and ChecksAn Excel UNIQUE spill error usually means the results cannot fit in the cells where the formula needs…
- Excel Power Query Stop Auto Refresh? Fix ItYes, you can stop Power Query from auto-refreshing in Excel by disabling background refresh in the query’s properties,…
- Power Query Error Trapping in Excel ExplainedIn Excel Power Query, trap a row-level error with M’s try expression, then decide whether to replace it,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.