Excel Formula Not Calculating? Fix It Step by Step

Errors & fixes · Updated November 21, 2026

If an Excel formula is not calculating, first check that calculation is set to Automatic: go to Formulas > Calculation Options > Automatic. If the cell still shows the formula rather than its result, change its number format to General and re-enter the formula; if it shows an old result, recalculate the workbook and check the formula’s inputs.

Check whether Excel is showing formulas or results

A cell displaying =SUM(B2:B5) instead of a number usually points to a display or input issue, not a broken SUM function. Excel can show formulas throughout a worksheet when Show Formulas is enabled. On Windows, press Ctrl+` (the grave-accent key, often beside 1) to toggle this view, or choose Formulas > Show Formulas. If other formula cells also show their expressions, turn the setting off and check again.

If just one cell shows its formula, Excel may be treating the entry as text. Select the cell and look at the formula bar: an apostrophe before the equals sign, such as '=A1+B1, marks the entry as text. A leading space can have a similar effect. Remove the extra character and press Enter. If the cell was formatted as Text before you typed the formula, changing its format afterward may not be enough: set the cell to General, then re-enter the formula by selecting it, pressing F2, and pressing Enter. On Mac, the F2 key may require Fn, depending on keyboard settings; you can also edit the formula bar and confirm the entry.

Set calculation to Automatic

Excel can use Automatic or Manual calculation. In Manual mode, formulas may keep displaying previous results until you request a recalculation. To check the setting in Excel for Microsoft 365 on Windows, select Formulas > Calculation Options > Automatic. The setting can affect other open workbooks as well, so a workbook may appear to behave differently depending on which files are open.

After changing the setting, give Excel a moment to recalculate. If you need to refresh results manually, press F9 to calculate worksheets in all open workbooks, or Shift+F9 to calculate the active worksheet. Ctrl+Alt+F9 forces a full calculation of all formulas in open workbooks, including formulas Excel might otherwise consider unchanged. On Mac, function keys can depend on macOS keyboard settings; use the Formulas tab’s calculation commands if the shortcut controls brightness or another system feature.

Manual calculation is sometimes useful in large workbooks because it prevents a pause after every edit. It is also an easy setting to forget: a workbook opened after another file used Manual mode may not update when expected. Return to Automatic when you want formulas to recalculate as their referenced values change.

Look for text formatting and formula-entry mistakes

Excel formulas normally begin with an equals sign. If you type a calculation without it, Excel treats the entry as ordinary text or a label. For instance, with a starting value in A1 and another value in B1, the addition formula is =A1+B1. If A1 is 12 and B1 is 8, the result is 20. Check that there is no apostrophe, space, or other character before the equals sign.

Some errors produce a result that looks wrong rather than preventing calculation. A cell formatted as a date, percentage, or currency can display a numeric result in an unexpected way. Select the cell and inspect Home > Number Format; choose General or an appropriate format to see the underlying value in a more useful form. Formatting changes how a value is displayed, not the arithmetic Excel performs.

Also check for mismatched parentheses, misspelled function names, and references to the wrong cells. A formula with an unclosed parenthesis will generally prompt an error when entered, but a syntactically valid formula can still point to an empty or unintended cell. Select the formula cell and inspect the colored reference outlines Excel shows while editing. Confirm that each range includes the intended rows and columns.

In some regional settings, Excel expects semicolons rather than commas between function arguments. A formula copied from a site or another computer can therefore be rejected even when its logic is correct. Use the separator Excel accepts in your installation; regional list-separator settings influence this behavior.

Check the formula’s inputs and dependencies

A formula can calculate correctly while returning a surprising value because one of its inputs is blank, stored as text, or outside the referenced range. For a basic total, with numeric values in B2:B5, enter =SUM(B2:B5). If the cells contain 10, 15, 20, and 5, the result is 50. If a value that should be included is in B6, this formula will not include it; change the range to include that row.

Check the cells the formula refers to, not just the formula itself. A number imported from a text file may look numeric but be stored as text, and a formula may omit newly added rows. Click each precedent cell and inspect its value and format. The Formulas > Trace Precedents command can draw arrows from cells used by the selected formula, which helps reveal unexpected links in a complicated sheet.

Relative references also change when formulas are copied. If a formula should always refer to a particular input cell, make that reference absolute by adding dollar signs, such as =$A$1*B2. With a rate in A1 and a quantity in B2, this multiplies the fixed rate by the row’s quantity; copying it down keeps A1 fixed while B2 changes to B3, B4, and so on. Use F4 while editing a reference on Windows to cycle through reference types. On Mac, the shortcut can depend on keyboard settings; inserting the dollar signs directly is an alternative.

Understand errors, circular references, and external links

An error value is evidence that Excel did evaluate a formula, but could not produce the requested result. #DIV/0! commonly means a division used zero or a blank denominator; #NAME? can indicate a misspelled function or an unrecognized name; and #REF! means a reference is invalid, often because a referenced row, column, or cell was deleted. Read the error and inspect its inputs before replacing it with a different formula.

A circular reference happens when a formula depends on its own result, directly or through another cell. For example, if C1 contains a formula that adds C1 to another value, Excel cannot solve the ordinary calculation in the usual way. Excel may show a circular-reference warning or identify a circular reference in the status bar. Find the accidental loop and correct the reference. Some deliberate financial or engineering models use iterative calculations, but enabling iteration changes how Excel handles such loops; do so only when the workbook is designed for it. The setting is under File > Options > Formulas > Enable iterative calculation on Windows.

A formula linked to another workbook may need that source file to be available or the link to be updated. Check whether the formula includes an external workbook reference and whether the source file has moved or been renamed. Use Data > Workbook Links when that command is available for the workbook’s links, and review the source location before changing or breaking a link. If a result depends on a connection or imported data, refresh the relevant connection as well; recalculating a formula does not necessarily refresh every external data source.

Use a quick troubleshooting sequence

When the cause is not obvious, work through these checks in order. This avoids changing several settings at once and makes it easier to identify the actual fault:

  1. Look at the cell: does it show a formula, an old value, a blank, or an error?
  2. If many cells show formulas, turn off Formulas > Show Formulas.
  3. If one cell shows its formula, check for a leading apostrophe or space, set the format to General, and re-enter it.
  4. Confirm Formulas > Calculation Options > Automatic, then press F9 if results remain stale.
  5. Inspect referenced cells, ranges, and number formats; look for omitted rows or values stored as text.
  6. Read any error value, and check for a circular reference or broken external-workbook link.

For a simple test, enter a new formula in an unused General-formatted cell. With 2 in A1 and 3 in B1, enter =A1+B1; the expected result is 5. If this calculates but the original formula does not, the problem is likely specific to the original cell, formula, inputs, or workbook settings rather than basic arithmetic.

Why a formula may show zero or an unchanged answer

A zero is still a calculated result, so investigate what the formula is actually adding or multiplying. For example, SUM ignores text values in a referenced range. If a cell contains the characters 25 stored as text, it may not contribute as expected to a total. Check the individual inputs and correct the data type rather than assuming Excel has stopped calculating.

An unchanged result often points to Manual calculation, but it can also mean the changed cell is not part of the formula’s references. Confirm that the formula includes the edited cell and that the reference did not shift when copied. If the formula uses a function that depends on external data, a calculation command alone may not retrieve fresh source data. Recalculate formulas and refresh connections as separate steps when both are involved.

Do not confuse the displayed result with the value stored in the cell. A narrow column can show a row of number signs for a date or number that does not fit; widen the column to see it. A custom number format can also hide values. These are display problems rather than calculation failures.

Frequently Asked Questions

Why is Excel showing the formula instead of the answer?
Show Formulas may be enabled, or that cell may be stored as text. Toggle Formulas > Show Formulas to check the first possibility. For a single affected cell, remove any leading apostrophe, set its format to General, and re-enter the formula.

How do I make Excel recalculate automatically?
In Excel for Microsoft 365 on Windows, select Formulas > Calculation Options > Automatic. If results still appear out of date, press F9 to recalculate open workbooks or use the calculation commands on the Formulas tab.

Why does my formula update only after I press Enter?
The cell may have been formatted as Text before the formula was entered, or the entry may include a leading apostrophe. Change the cell to General and confirm the formula again. Also check that calculation is Automatic if dependent results remain stale.

Why does Excel show a formula error?
An error such as #DIV/0!, #NAME?, or #REF! means Excel evaluated the formula but encountered a problem. Check the denominator, function spelling or defined name, and referenced cells respectively. The error itself is different from a formula that has not recalculated.

Why is my formula result zero when the cells contain numbers?
Some apparent numbers may be text, or the formula’s range may exclude the cells you expect to include. Inspect the referenced cells, confirm their values are numeric, and verify the range addresses. Also check whether the formula is using the right worksheet and columns.

Does Excel for Mac use the same steps?
The calculation and formula-display controls are available through Excel’s ribbon, though function-key shortcuts may differ because of macOS keyboard settings. Use the Formulas tab for calculation commands if F9 or another key triggers a system feature instead.

Related

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.