Excel Formula Not Calculating? Fix the Common Causes

If an Excel formula is not calculating, first check Formulas > Calculation Options > Automatic, then check whether the cell is formatted as Text; if it is, change it to General and press F2, then Enter. These checks fix many cases where a result looks frozen or the formula appears in the cell instead of its answer.

What “not calculating” can mean

People use “not calculating” to describe several different problems. A cell may show an old result after its inputs change, display the formula itself, return an error such as #VALUE!, or remain blank. Those symptoms have different causes, so identify what is actually on screen before changing the workbook.

Click the cell and look at both the cell and the formula bar. If the cell shows a number but the formula bar shows a formula, Excel has a formula and may be showing a stale result. If the cell shows characters beginning with an equals sign, Excel may be treating the entry as text or displaying formulas across the sheet. If the cell shows an error value, the formula is being evaluated, but something in its expression or source data needs attention.

Also check whether the result is genuinely wrong. A formula can calculate successfully and still return an unexpected value because it references the wrong cells, uses text where a number is expected, or follows a different rounding rule than you assumed.

Check Excel’s calculation mode

In Excel for Microsoft 365 on Windows, open the Formulas tab, choose Calculation Options, and select Automatic. In Manual mode, Excel does not recalculate every dependent formula whenever a value changes; a workbook can therefore show an older result even though its formula is valid. Calculation mode can affect other open workbooks too, so check it when several workbooks are open.

After selecting Automatic, edit an input cell or use a calculation command to refresh the results. On Windows, F9 calculates open workbooks, while Ctrl+Alt+F9 forces a full calculation. If a laptop uses its function keys for hardware controls, you may need to press Fn as well. On Mac, use Formulas > Calculate Now or Calculate Sheet; the exact keyboard behavior can depend on the keyboard’s function-key settings.

Manual calculation can be useful for very large workbooks that take a long time to update, but it is easy to forget that it is enabled. If you use it deliberately, recalculate before reviewing or sharing the results. Automatic mode is generally the sensible setting for ordinary workbooks.

Make sure the formula is not stored as text

A cell formatted as Text can keep a newly entered formula as literal characters instead of evaluating it. A leading apostrophe can have the same effect. In that case, changing the cell format alone may not convert the existing entry: select the cell, set its number format to General from Home > Number Format, then press F2 and Enter to make Excel interpret the entry again.

For a range, select the affected cells, change the format to General, and re-enter the formulas. You can often do this by selecting a cell and pressing F2, then Enter, repeating for each entry. If the range is large, Data > Text to Columns can sometimes re-parse text entries, but preview the steps and results first; it is not a universal repair for every mixed-data range.

Look for a leading apostrophe in the formula bar, and check whether the cell is left-aligned when nearby numeric results are right-aligned. Alignment is only a clue, not proof, because formatting can change alignment. A formula typed with a space or other character before the equals sign can also be treated as text rather than as a formula.

If the formula was pasted from a website, email, or another application, retype the equals sign and check for curly quotation marks or unusual spaces. Excel formulas require ordinary syntax characters. Pasted text can look correct while containing characters Excel does not recognize as part of a formula.

Turn off Show Formulas if formulas appear everywhere

If many cells display expressions instead of results, the sheet may be in Show Formulas view. On Windows, press Ctrl+` (the grave-accent key, often near the top-left of the keyboard) to toggle this view, or use Formulas > Show Formulas. On Mac, use the Show Formulas command on the Formulas tab if the keyboard shortcut differs on your keyboard.

Show Formulas changes how worksheet contents are displayed; it does not turn formulas into text or erase their results. Column widths may expand when the view is on, so the sheet can look different after you switch it off. If only one cell shows a formula, check that cell’s text formatting or leading characters instead.

Separate formula errors from calculation problems

An error value usually means Excel did calculate the expression and encountered a problem. Read the error before trying to hide it. #DIV/0! means a formula is dividing by zero or by an empty cell; #REF! points to an invalid reference, often because a referenced row, column, or cell was deleted; and #NAME? can indicate a misspelled function name or unrecognized text in a formula. #VALUE! often indicates an unexpected type of value in an operation.

For example, with 10 in A2 and 0 in B2, the formula below in C2 returns #DIV/0! because division by zero is undefined in Excel.

With 10 in A2 and 0 in B2, enter this formula in C2; the result is #DIV/0!:

=A2/B2

Fix the cause where possible: check whether a denominator should contain a nonzero value, whether a reference still points to the intended cell, and whether a function name is spelled correctly. Wrapping a formula in an error-handling function can be appropriate when a missing result is expected, but it can also conceal a broken reference or bad input. Do not treat a hidden error as proof that the underlying calculation is correct.

Check references, inputs, and formula syntax

Click the formula cell and inspect the colored reference outlines that Excel shows while editing. Confirm that the formula points to the intended rows and columns, especially after copying it. A relative reference changes as a formula is copied; dollar signs lock a row, column, or both. For example, a copied formula may need a fixed tax-rate cell while its sales reference should move down the rows.

Check that inputs are really numbers or dates, not text that merely looks numeric. A number imported from a system may include a leading apostrophe, spaces, or a decimal separator that does not match the workbook’s regional settings. SUM generally ignores text entries in a referenced range, which can make a total lower than expected. Arithmetic that tries to operate directly on text may instead return an error.

For a concrete total, assume cells B2:B6 contain the numeric values 12, 8, 15, 5, and 10. Enter the following in B7; the result is 50:

=SUM(B2:B6)

Excel usually accepts commas between function arguments, but regional settings can make it expect semicolons instead. If a formula copied from another computer is rejected, check the separator your installation uses rather than changing the function name or deleting punctuation at random. Also confirm that paired parentheses and quotation marks are present when your formula needs them.

Look for circular references and blocked results

A circular reference occurs when a formula depends on its own result, directly or through another formula. For example, a total entered inside the range it sums can accidentally include itself. Excel may show a circular-reference warning or stop producing the result you expect. Find the warning and inspect the referenced cells; do not enable iterative calculation unless the workbook intentionally uses a circular model and you understand the iteration settings.

In Microsoft 365, a formula that returns multiple results may need to “spill” into neighboring cells. If those cells are occupied, merged, or otherwise block the output area, Excel can return #SPILL!. Clear the obstructing cells or move the formula to an open area. This is different from a calculation mode problem: the formula is attempting to return results, but there is no room for them.

Formulas linked to another workbook can also behave unexpectedly if the source file is unavailable, moved, or not updated. Check the external reference and the source workbook before assuming Excel has stopped calculating. If a result depends on a connection or imported data, refresh the relevant data source using its available refresh command and confirm that the refresh completed.

A reliable troubleshooting order

Work from the simplest, most common causes toward workbook-specific issues. This keeps a quick display or settings problem from turning into unnecessary edits to a complex formula.

Read the cell and formula bar to determine whether you see a stale result, literal formula, or error value.
Check Formulas > Calculation Options and select Automatic if the workbook should update as inputs change.
If you see formulas rather than results, toggle Show Formulas. If only one formula is literal text, set the cell to General and re-enter it.
Inspect the formula’s references, operators, function names, separators, and source values. Test a small representative case if the expression is complicated.
Resolve specific errors such as #REF!, #VALUE!, or #SPILL! at their source, then recalculate and verify the answer against a known example.

When a correct formula still seems stale, save the workbook, close and reopen it, and check calculation mode again. If only one workbook behaves strangely, compare its settings and formulas with a known-good workbook rather than immediately rebuilding the file. Keep an untouched copy before making broad find-and-replace changes or editing many formulas.

Frequently Asked Questions

Why is Excel showing the formula instead of the answer?

Either Show Formulas is on, or the cell is treating the entry as text. If formulas appear throughout the sheet, turn off Formulas > Show Formulas. If it is one cell, set its format to General and re-enter the formula with F2, then Enter; also check for a leading apostrophe.

Why does my Excel formula show an old value?

Check whether calculation mode is set to Manual. Choose Formulas > Calculation Options > Automatic, then recalculate. If the displayed result remains unexpected, inspect the formula’s references and confirm that the input cells actually changed.

Why does Excel show the formula as text even after I change the format?

Changing Text to General does not always re-interpret a value that was already entered. Select the cell, press F2, and press Enter. For a group of cells, re-enter the formulas after changing the format, and check for apostrophes or spaces before the equals sign.

Does pressing F9 fix every formula error?

No. F9 recalculates, but it does not repair incorrect syntax, bad references, text in place of numbers, or a division-by-zero error. If the formula already shows an error value, diagnose that error rather than repeatedly recalculating it.

Why does SUM ignore a value that looks like a number?

The value may be stored as text, particularly if it came from an import or was entered with an apostrophe. Check its cell format and source characters, then convert it to a genuine number using an appropriate method for the data. Verify the resulting total against the original entries.

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.