If an Excel formula looks correct but does not update, first check Formulas > Calculation Options and select Automatic. If the cell displays the formula itself instead of its answer, change its format to General, then re-enter the formula; those two issues account for many cases where a formula seems not to calculate.
Start by identifying what the cell displays
Different symptoms point to different causes. A formula that displays an old answer may not be recalculating. A cell that shows the formula text may be stored as text or affected by Show Formulas. An error such as #VALUE! or #NAME? means Excel has attempted to evaluate the formula but encountered a problem. A blank result may also be intentional: a formula can return an empty string, or its inputs may be blank.
Before changing anything, select the cell and look at the formula bar. If the formula bar shows a formula beginning with an equals sign but the worksheet cell shows a number, the formula is being evaluated; the result may simply be unexpected or stale. If the worksheet cell shows the formula characters, investigate cell formatting and display settings first. If there is an error, use the error message and its tooltip as clues rather than treating it as a calculation-mode problem.
Check Excel’s calculation mode
In Microsoft Excel for Microsoft 365 on Windows, open Formulas > Calculation Options and choose Automatic. In Automatic mode, Excel recalculates formulas when their precedents—cells used by those formulas—change. In Manual mode, formulas can retain old results until you request a recalculation. A workbook may be set to Manual because someone changed the option while working with a large or complex file.
After selecting Automatic, make a small change to an input cell and see whether the result updates. You can also press F9 to calculate open workbooks, or Shift+F9 to calculate the active worksheet. If a workbook is large and ordinary recalculation does not refresh a result, Ctrl+Alt+F9 forces a full calculation. Ctrl+Alt+Shift+F9 rebuilds the dependency information and then recalculates.
Calculation mode is an Excel setting that can affect other open workbooks, not just the cell you selected. If the setting appears to change unexpectedly, close other workbooks and check the mode again. On Mac, use the Formulas tab’s Calculation Options menu where available; keyboard shortcuts can differ by keyboard and function-key settings, so use the ribbon command if a shortcut does not work. Excel for the web has fewer desktop controls, and its recalculation behavior and available options can differ.
When the formula itself appears in the cell
Excel may be treating a formula as text. Select the cell and check Home > Number Format. If the format is Text, change it to General, then edit the cell and press Enter. Merely changing Text to General after the formula was entered may not be enough; re-entering it prompts Excel to interpret the contents as a formula.
Also check for an apostrophe or an accidental space before the equals sign. Those characters can make an entry literal text rather than a formula. Click the cell and inspect the formula bar, remove the unwanted character, and confirm the entry. Pasting from a website, email, or another application can introduce spaces or other characters that are difficult to notice in the worksheet.
Another possibility is that Show Formulas is turned on. This setting displays formulas in cells instead of their results across the worksheet; it does not mean every formula has stopped calculating. Toggle it from Formulas > Show Formulas. On Windows, Ctrl+` (the grave-accent key, usually near the number 1) is the shortcut. Column widths may need adjusting afterward because formula text takes more room than many results.
Inspect references, inputs, and formula structure
If one formula does not respond while other formulas do, check what it refers to. A reference may point to the wrong row or sheet, or to a cell that contains text rather than a number. For instance, a value that looks like 25 may have been imported as text. Number formatting can make text look numeric, but formatting alone does not convert the underlying value. Try selecting the input and checking whether Excel aligns or handles it as expected, then confirm the source data and convert it appropriately.
For a basic total, with values in cells B2:B4, enter this in another cell:
=SUM(B2:B4)
If B2, B3, and B4 contain 10, 15, and 20 as numbers, the result is 45. If one of those entries is text, SUM may ignore it, so a smaller-than-expected total can be a data-type issue rather than a failure to calculate. Likewise, check that a formula uses the intended relative or absolute references. When copied down, A2 changes by row, while $A$2 remains fixed.
A formula may also depend on a cell that is blank, contains an unexpected value, or is itself linked to a calculation that has not updated. Trace the chain from the final result back to its inputs. On the Formulas tab, Trace Precedents can show arrows to cells that feed the selected formula; Trace Dependents shows cells that use it. These tools are useful in complicated workbooks, though the arrows do not explain whether the underlying data is correct.
Check separators, names, and copied formulas
Excel’s formula argument separator depends on regional settings. Many installations use commas, while some use semicolons. If Excel rejects a formula when you enter it, check the separator used by formulas that already work in that copy of Excel. Do not replace separators blindly: use the syntax appropriate to the computer’s regional configuration.
Function names and references can also be damaged when formulas are copied or edited. A misspelled function name can produce #NAME?; a reference to a deleted cell or invalid range may produce another error. If a formula was copied from a different workbook, inspect sheet names, quotation marks around sheet names that contain spaces, and the start and end of every range. The formula bar is often easier to inspect than a narrow worksheet cell.
Excel calculates a formula from its dependencies, not from how recently someone looked at the cell. A circular reference—where a formula depends on its own result, directly or through other cells—can prevent the intended result. Excel usually warns about circular references, and the status bar or error-checking tools may help locate one. Iterative calculation can be enabled through File > Options > Formulas, but it should only be used when the workbook is deliberately designed for iterative calculations. Turning it on to hide an accidental circular reference can produce misleading results.
Separate recalculation from data refresh
Pressing F9 recalculates worksheet formulas, but it does not necessarily retrieve new information from every external source. A formula that refers to another workbook, a query, or a connected data source may need that source to be updated as well. For a query or connection, use Data > Refresh All when appropriate. For links to another workbook, check whether the source file is available and whether Excel is prompting you to update links.
This distinction matters when a formula is technically calculating against old input data. For example, a total can recalculate correctly and still show yesterday’s figures if the query feeding its source table has not refreshed. Check the source cells or table first, then refresh the connection, and finally recalculate if the result still appears stale. External-link prompts and refresh behavior may depend on workbook settings, permissions, network access, and the source application.
Use a quick troubleshooting sequence
Work through the likely causes in order instead of rewriting a formula that may already be correct:
- Look at the cell: is it showing formula text, an old value, a blank, or an error?
- For a stale value, select Formulas > Calculation Options > Automatic, then make a test change or press F9.
- For visible formula text, check Show Formulas, the cell’s number format, and any leading apostrophe or space. Set the format to General and re-enter the formula if needed.
- For an error or unexpected answer, inspect the referenced cells, data types, range boundaries, and function spelling.
- If the formula depends on an external workbook or data connection, confirm that the source is available and refresh it where appropriate.
- For a complex workbook, use Trace Precedents and check for circular references before changing calculation settings.
For example, with unit prices in A2:A4 and quantities in B2:B4, the following formula in C2 multiplies the first price by its quantity:
=A2*B2
If A2 contains 12 and B2 contains 3, C2 should return 36. If it does not, check whether A2 or B2 is stored as text, whether the formula is in C2, and whether calculation is set to Automatic. This simple example separates a calculation setting problem from a reference or input problem.
Frequently Asked Questions
Why does my Excel formula show the formula instead of the answer?
Check whether Show Formulas is enabled. If only one cell is affected, check whether it is formatted as Text or has an apostrophe or space before the equals sign. Set the format to General, then re-enter the formula.
How do I make Excel recalculate immediately?
Choose Formulas > Calculation Options > Automatic. To recalculate on demand, press F9 for open workbooks or Shift+F9 for the active worksheet on Windows. If needed, use Ctrl+Alt+F9 for a full calculation.
Why does my formula update only after I press F9?
The workbook is likely in Manual calculation mode. Set it to Automatic if you want Excel to recalculate when inputs change. In a large workbook, Manual mode may have been chosen to reduce delays, so consider workbook performance before changing the setting.
Why is my formula result wrong even though the formula is correct?
Check the values the formula uses, not just its visible syntax. Inputs may be text, references may point to unintended cells, or external data may be out of date. A formula can calculate correctly and still return an unexpected result because its inputs are not what they appear to be.
Does pressing F9 update linked workbooks and queries?
F9 recalculates formulas, but it is not the same as refreshing every external source. Use Data > Refresh All for relevant connections or queries, and confirm that linked workbooks are accessible and updated.
Related
- Excel Named Range to Table: What Works and What Doesn’t
- Excel Consolidated Sum: Combine Data Across Sheets
- Excel: Consolidate Two Columns Into One Without Losing Data
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- VLOOKUP Not Working? Fix Excel Formatting MismatchesYes—VLOOKUP can fail when a lookup value and the matching-looking cell are stored differently, such as the number…
- Excel Stale Value Error: What It Means and How to Fix ItExcel’s “stale value” is usually not a standard formula error such as #VALUE!; it means a displayed result…
- 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…
- Clean Excel Formatting: Remove Clutter and Fix StylesTo remove formatting in Excel for Microsoft 365 on Windows, select the cells, then choose Home > Clear…
- Excel Circular Reference Error: How to Find and Fix ItIf Excel keeps showing a circular reference error, find the formula loop and change one reference so the…
- Excel Print Area Not Working? Common FixesIf Excel is printing the wrong cells or ignoring a print area, select the intended range and choose…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.