If Excel does not calculate a formula, first check Formulas > Calculation Options and select Automatic. If that does not solve it, check whether the cell is formatted as text, whether Excel is showing formulas instead of results, and whether the formula contains an error or an incorrect reference.
How Excel calculates formulas
Excel normally recalculates a formula when a cell it depends on changes. For example, a total can update when you edit one of the values being added. Excel tracks these relationships between cells, so a formula can depend on other formulas as well as on typed values. A workbook that seems frozen may simply be set to manual calculation, but a formula can also be valid and return an unexpected result because its inputs or references are not what you think.
Calculation settings are distinct from formula syntax. A correctly written formula will not update automatically while calculation is set to Manual, and changing that setting will not repair a formula stored as text or one containing an invalid reference. It helps to identify whether Excel is showing the formula itself, showing an old result, or displaying an error such as #VALUE! before trying fixes at random.
Check the calculation mode first
In Excel for Microsoft 365 on Windows, open the Formulas tab, choose Calculation Options, and select Automatic. In Automatic mode, Excel recalculates dependent formulas when their inputs change. The other common option, Manual, waits for a recalculation command; it can be useful for very large or calculation-heavy workbooks, but it often explains why ordinary changes appear not to affect results.
To force a calculation, choose Formulas > Calculate Now to recalculate open workbooks, or Formulas > Calculate Sheet to recalculate the active sheet. On Windows, F9 recalculates open workbooks, while Shift+F9 recalculates the active worksheet. Ctrl+Alt+F9 forces a full calculation, and Ctrl+Alt+Shift+F9 also rebuilds the dependency information before recalculating. Laptop function keys may require the Fn key.
Calculation mode can affect all workbooks open in the same Excel session, so changing it for one file may affect another open file too. If the setting keeps returning to Manual, check other workbooks you open and any macros or add-ins that might change the calculation mode. On a Mac, use the Formulas tab’s calculation controls; the exact keyboard shortcut behavior can depend on the keyboard and function-key settings.
Make sure Excel is not displaying formulas
If many cells show entries such as =A1+B1 instead of calculated values, Excel may have Show Formulas enabled. Select Formulas > Show Formulas to turn that view off. On Windows, Ctrl+` (the grave-accent key, often near the upper-left corner of the keyboard) toggles formula display. The key location varies by keyboard layout, so the ribbon command is a reliable alternative.
Show Formulas changes what is displayed; it does not remove or alter the formulas. When it is enabled, columns may appear wider because formulas take more space than their results. If only one cell displays a formula while nearby cells show answers, Show Formulas is less likely to be the cause. Check that cell’s number format and how the formula was entered instead.
Check whether the formula is stored as text
A formula may look correct but remain uncalculated if Excel treats it as text. This commonly happens when the cell was formatted as Text before the formula was typed, or when an apostrophe was entered before the equals sign. In that case, Excel preserves the characters rather than interpreting them as a calculation. A leading space can also make an entry behave differently from an ordinary formula.
For a formula in C2 that uses the values in A2 and B2, the intended entry is =A2+B2. If A2 contains 7 and B2 contains 5, the result should be 12. If C2 displays the characters instead, select C2, change its number format to General using the number-format list on the Home tab, then press F2 and Enter to make Excel interpret the entry again. Merely changing the format after typing the formula may not be enough; re-entering it is the important step.
For a whole column of text-formatted formulas, select the affected cells, change their format to General, and re-enter each formula. Alternatively, the Text to Columns tool can convert some text entries, but inspect the results before relying on it, particularly if the cells contain mixed data. Also look in the formula bar for an apostrophe before the equals sign, and remove it if it was entered accidentally.
Inspect the formula, its references, and its inputs
When a formula calculates but gives the wrong answer, examine the formula bar and confirm which cells it refers to. A reference may point to a blank cell, a different row, or an unintended sheet. Relative references adjust when a formula is copied; absolute references marked with dollar signs stay fixed. For instance, copying a formula down a column can shift a reference in a way that is correct for one calculation but wrong for another. Check a copied formula in both the first and later rows rather than assuming every reference stayed the same.
For a simple total with values in B2, B3, and B4, enter the following in B5:
With the three values in B2:B4, the formula in B5 is =SUM(B2:B4). If the cells contain 10, 15, and 5, the result is 30.
Compare that result with the source cells. Numbers imported from websites or other programs may actually be text, and dates can be stored or interpreted differently depending on regional settings. A number that looks numeric may also contain spaces or other characters. Check alignment, number format, and whether Excel recognizes the entry as a number before concluding the calculation engine is at fault.
Excel formulas usually use commas between function arguments, but regional settings can make the expected list separator a semicolon. If a formula produces a syntax error after being copied from a different computer or a web page, check the separators and the decimal conventions used by your Excel installation. Use the function’s Insert Function guidance or Excel’s formula suggestions to confirm the required argument structure.
Understand formula errors and circular references
An error value means Excel is evaluating a formula, but cannot return the requested ordinary result. The error itself is a clue:
#NAME?often means Excel does not recognize a function name or text in the formula.#REF!indicates an invalid cell reference, often because a referenced row, column, or sheet was deleted.#VALUE!commonly indicates that an argument has the wrong type, such as text where a number is required.#DIV/0!means a formula is dividing by zero or by a blank cell treated as zero.#N/Aoften appears when a lookup formula cannot find a matching item.
Correct the underlying reference, input, or function rather than hiding an error before you understand it. A formula can also depend on itself, directly or through a chain of other formulas. That is a circular reference. Excel may warn you and, depending on settings, may not produce the result you expect. Find the circular reference through the error message or the Formulas tab’s error-checking tools, then revise the logic. Iterative calculation is available for deliberate circular models, but it should not be enabled as a quick fix for an accidental loop.
Check workbook links, functions, and data updates
Some results depend on information beyond the visible cells. A formula may link to another workbook, use a function that is not available in an older Excel version, or rely on data that has not been refreshed. If a formula contains a reference to another file, confirm that the source workbook is available and that Excel has not prompted you about updating links. A closed, moved, or renamed source file can leave a link unresolved or outdated.
Functions such as XLOOKUP, FILTER, SORT, UNIQUE, SEQUENCE, and LET require Microsoft 365 or Excel 2021 or later. Newer functions including TEXTSPLIT, TEXTBEFORE, TEXTAFTER, VSTACK, and HSTACK require Microsoft 365 or Excel 2024. If a workbook is opened in an earlier release, an unsupported function may show an error or a compatibility indicator rather than calculate as it does in a newer version. Check the Excel version and the function’s availability before rewriting a formula that works for someone else.
For queries and connected data, recalculating worksheet formulas is not necessarily the same as refreshing the source data. Use the workbook’s available refresh command for its connection or query, then allow formulas that use the refreshed cells to recalculate. Similarly, formulas that depend on volatile or external data can behave differently from a simple sum. Identify whether the issue is an unrefreshed input or a formula calculation problem.
A practical troubleshooting order
Work through these checks in order; after each change, make a small edit to an input cell and see whether the result updates:
- Choose Formulas > Calculation Options > Automatic, then use Calculate Now if needed.
- If many cells show equals signs and references, turn off Formulas > Show Formulas.
- If only certain entries appear unchanged, check for Text format, a leading apostrophe, or a space, then set General and re-enter the formula.
- Read the formula bar and verify the cell references, sheet names, parentheses, operators, and argument separators.
- Inspect the inputs for text stored as numbers, blanks, unexpected dates, or values outside the formula’s referenced range.
- Identify any visible error code, circular reference warning, external link, unsupported function, or data connection that needs attention.
For example, suppose a total in D2 does not change when you edit B2 or C2. First confirm D2 actually refers to those cells, then verify the workbook is set to Automatic. If D2 literally displays the formula, investigate Show Formulas or text formatting. If D2 shows an error, follow the error clue. This sequence separates a calculation-mode issue from a bad formula and avoids unnecessary edits to a workbook that may contain important linked or imported data.
Frequently Asked Questions
Why does Excel show the formula instead of the answer?
Show Formulas may be enabled, or the cell may be formatted as Text. Turn off Formulas > Show Formulas; if just one cell is affected, set it to General and re-enter the formula.
How do I make Excel recalculate immediately?
Choose Formulas > Calculate Now, or press F9 on Windows. If formulas still do not update automatically afterward, set Formulas > Calculation Options to Automatic.
Why does Excel calculate some formulas but not others?
The affected cells may contain text-formatted formulas, invalid references, or errors, while other formulas are valid. Compare a working cell with a nonworking one, including the formula bar, cell format, and referenced inputs.
Why is my formula returning zero?
Check that it refers to the cells you intend and that those cells contain numeric values rather than text or blanks. A zero may also be a legitimate result of the formula’s conditions or inputs; inspect the formula before changing number formatting.
Does Automatic calculation slow Excel down?
It can make a workbook with many complex or dependent formulas take longer to respond because Excel recalculates after changes. Manual mode can help with performance in specialized workbooks, but remember to recalculate before relying on results, then restore Automatic when appropriate.
Related
- Excel Circular Reference Greyed Out? Find the Cause
- Excel Named Range Scope Greyed Out? How to Change It
- Excel Cannot Insert Row: Causes and Fixes
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Subtotal Filtered Rows: Formula, Steps, and FixesYes. In Excel for Microsoft 365 on Windows, use the SUBTOTAL function to calculate from rows that remain…
- Sort Excel Numbers in Order: Steps, Formulas, and FixesTo sort numbers in Excel, select a cell in the number list and choose Data > Sort &…
- COUNTIF in Excel: Count Empty Cells and Blank Cells EasilyIf your cells are in A2:A20, use =COUNTIF(A2:A20,"") to count empty cells. COUNTIF with an empty-string criterion counts…
- How to Add Page Numbers in Word Bottom Right CornerTo add page numbers at the bottom right in Word for Microsoft 365 on Windows, select Insert >…
- Excel Attendance List: Setup, Formulas, and Practical TipsAn Excel attendance list is a worksheet that records who attended each class, meeting, or event and makes…
- Freeze Function in Excel: Freeze Panes or FormulasTo keep headings visible while you scroll, select the cell just below and to the right of the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.