Excel Formulas Not Calculating? Fix the Common Causes

If Excel formulas are not calculating, first choose Formulas > Calculation Options > Automatic, then press F9 to recalculate. If only one cell is affected, check that it is formatted as General or a number, remove any leading apostrophe, and re-enter the formula; a formula stored as text will display instead of calculating.

How Excel calculation works

Excel normally recalculates formulas when a value they depend on changes. For example, a total in one cell updates after you edit one of the cells it adds. Calculation mode controls whether Excel performs that work automatically or waits for you to request it. Automatic is the usual setting for interactive workbooks. Manual mode can be useful in a very large workbook that would otherwise recalculate slowly, but it can make results appear stale until a calculation is triggered.

Calculation mode is generally shared across open workbooks in an Excel session, so opening a workbook created while Excel was in Manual mode can affect other workbooks you have open. After switching to Automatic, check important results rather than assuming every displayed value has already refreshed. A workbook may also contain formulas that depend on external links, data connections, or volatile functions, which can complicate when values update.

To recalculate the active worksheet, press Shift+F9. To recalculate all open workbooks, press F9. If results still look out of date, Ctrl+Alt+F9 forces a full calculation of all formulas in open workbooks. On some laptops, you may need to hold the Fn key to use function keys. On Mac, use the Formulas tab’s calculation commands if the keyboard function keys behave differently; the key combination can vary with keyboard settings.

Check whether the cell contains text instead of a formula

A common cause is that the cell is formatted as Text before a formula is entered. Excel then treats the equals sign and following characters as text. The cell displays something like =SUM(A2:A3) instead of returning a number. Changing the format afterward does not always convert an existing text entry into a working formula; you generally need to edit and confirm it again.

Select the affected cell and choose Home > Number Format, then select General or an appropriate numeric format.
Click in the formula bar or press F2, then press Enter to make Excel interpret the entry again. Alternatively, delete and retype the formula.
Look for an apostrophe before the equals sign. An entry such as '=A1+B1 is deliberately stored as text; remove the apostrophe if it should calculate.
Check whether there are spaces before the equals sign. A leading space can stop Excel recognizing the entry as a formula.

For a simple test, with 12 in A2 and 8 in A3, enter this in A4:

With 12 in A2 and 8 in A3, this formula in A4 returns 20: =SUM(A2:A3)

If A4 shows the formula characters rather than 20, inspect the format, leading characters, and Show Formulas setting. If the formula is correct but returns an unexpected value, check the referenced cells and calculation mode next.

Make sure Show Formulas is off

When Show Formulas is enabled, Excel displays formulas in cells instead of their calculated results. This can make an entire sheet look broken even though the formulas are working. Toggle the setting from Formulas > Show Formulas. On Windows, Ctrl+` (the grave-accent key, often beside the number 1) also toggles this view. The column widths may change when the setting is turned on because formulas take more room than many results.

Show Formulas is a display setting, not a calculation setting: it does not convert formulas to text or stop them from updating. If only one cell displays a formula while the surrounding cells show results, suspect that individual cell’s text format or an apostrophe rather than the sheet-wide display option.

Verify calculation mode and recalculate

Open Formulas > Calculation Options and select Automatic. If you intentionally use Manual mode for performance, select Calculate Now on the Formulas tab or use the calculation shortcuts when you need refreshed results. Excel may not visibly flag every stale result, so a workbook left in Manual mode can produce decisions based on old values unless the user recalculates it.

Some workbooks are large because they contain many formulas, long dependency chains, or formulas that recalculate frequently. In that situation, Manual mode may be a deliberate trade-off rather than a fault. A safer working habit is to recalculate before reviewing, printing, or sharing results, and to restore Automatic mode if other people expect changes to update immediately. Avoid changing calculation mode simply to mask slow formulas without understanding what the workbook is doing.

Inspect references, dependencies, and formula syntax

A formula can calculate correctly and still return the wrong result because it points to the wrong cells. Check the colored reference outlines that appear while editing a formula, confirm the sheet names and ranges, and look for an accidentally omitted row. The Formulas > Trace Precedents and Formulas > Trace Dependents commands can help show which cells feed a result and which formulas rely on it.

For example, with prices in B2:B4, confirm whether a total should include all three rows rather than only the first two. In B5, this formula adds B2 through B4:

With 10, 15, and 20 in B2:B4, this formula in B5 returns 45: =SUM(B2:B4)

Also check for absolute and relative references. When copied down a column, a reference such as A2 normally changes to A3 in the next row. A dollar sign locks a row or column, as in $A$2, which can be intentional for a constant input but wrong if each row should use a different value. Inspect the formula bar and compare the pattern with neighboring rows before filling formulas further.

Excel formulas use the argument separator expected by the computer’s regional settings. Many installations use commas, while some use semicolons. If a formula copied from another setup is rejected, replace the separators with those accepted by your Excel installation; do not assume every regional configuration uses commas. Also verify parentheses, quotation marks, and range punctuation. A small syntax difference can prompt an error or cause Excel to interpret a value differently.

Look for errors, circular references, and text values

Error values are clues, not the same thing as a formula that has failed to recalculate. #DIV/0! means a calculation divides by zero or a blank cell; #VALUE! commonly signals an unexpected value type; #REF! indicates an invalid reference, often because a referenced cell or range was deleted. #NAME? can mean Excel does not recognize a function or name. Read the error and inspect the formula rather than hiding it immediately.

A circular reference happens when a formula depends on its own result, directly or through other cells. For instance, a total placed inside the range it sums can include itself. Excel may warn about the circular reference and stop normal calculation for that dependency. Find the incorrect range or reference and correct it. Iterative calculation can be appropriate for specific models designed to converge through repeated calculations, but it should not be enabled merely to silence a warning; it changes how the workbook calculates.

Numbers that look numeric may actually be text, especially when pasted or imported from another system. A text value may be left-aligned by default, but alignment alone is not proof. Check the cell’s format and test the data source; remove unwanted spaces or convert the data using a suitable Excel method. Dates can have the same issue: a date-looking string stored as text may not behave like an Excel date serial value in date calculations or sorting.

Check external links, add-ins, and version compatibility

If a formula refers to another workbook, its result may depend on whether that source file is available and whether links have been updated. Look for workbook names in square brackets within the formula and use Data > Workbook Links when available to inspect linked workbooks. The command’s availability and wording can vary with the Excel build. Do not update links from an unfamiliar source without confirming that the file is trusted and that the linked values are expected.

A function may also be unsupported in an older Excel version. Newer functions such as XLOOKUP and FILTER require Microsoft 365 or Excel 2021 or later; opening a workbook containing them in an older version can result in compatibility problems or an unrecognized-function error. Confirm the version used by the person who created the file before replacing a function. Add-ins and custom functions can create a similar issue if the add-in is disabled or unavailable on another computer.

If formulas recalculate in desktop Excel but not as expected in Excel for the web, check whether the workbook relies on features or connections that the web version handles differently. For a basic formula issue, confirm the cell contents and calculation settings in the application where the workbook is being edited. Keep a copy before making broad changes to links, add-ins, or calculation settings in a shared workbook.

A practical troubleshooting order

Use this sequence to narrow the cause without changing several settings at once:

Check Formulas > Calculation Options and select Automatic unless Manual mode is intentional.
Press F9, then check whether the affected result updates. Use Shift+F9 to calculate only the active sheet.
Check whether Show Formulas is enabled. If every formula is visible, turn it off.
Inspect the individual cell for Text formatting, a leading apostrophe, spaces, or an incomplete formula entry. Set a suitable format and re-enter the formula.
Read any error value, verify cell references and ranges, and look for circular references or text stored in numeric cells.
For formulas linked to other files or newer functions, confirm that the source workbook, add-in, or compatible Excel version is available.

Change one thing at a time and compare the result. If the formula changes after recalculation, calculation mode was likely involved; if re-entering it fixes the cell, text interpretation was more likely. This method is especially helpful when a workbook has several symptoms, because fixing one can otherwise obscure the original cause.

Frequently Asked Questions

Why does Excel show the formula instead of the answer?
Check whether Show Formulas is on, or whether the cell is formatted as Text. A leading apostrophe or space can also make an entry display as text. Turn off Show Formulas if the whole sheet is affected; for a single cell, set an appropriate number format and re-enter the formula.

Why do I have to press F9 for formulas to update?
The workbook or Excel session may be using Manual calculation. Choose Formulas > Calculation Options > Automatic if you want dependent formulas to update as you edit. Manual mode is sometimes intentional in large workbooks, but results must then be recalculated when needed.

Why does a formula return zero when the referenced cells contain numbers?
Check that the formula references the intended cells and that the values are numeric rather than text. A range may omit the cells you expected, or the numbers may have been imported as text. Inspect the formula bar and test a small range before changing the full worksheet.

Can I fix a formula that is stored as text by changing its format?
Changing Text to General is a useful first step, but an existing entry may remain text until it is edited and confirmed. Select the cell, press F2, then Enter, or delete and retype the formula. Check for an apostrophe or leading space if it still does not calculate.

Is a circular reference always a mistake?
Not always. Some models intentionally use iterative calculation, but a circular reference is often an accidental range or cell reference. Locate and understand the cycle first; enable iterative calculation only when the workbook is designed to use it and the result behavior is understood.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.