Microsoft Excel Formula Not Calculating? Fix It

If a Microsoft Excel formula is not calculating, first check Formulas > Calculation Options and select Automatic. If that does not fix it, check whether the cell is formatted as Text, whether Show Formulas is turned on, and whether the formula contains an error or references the wrong cells.

Start with the quickest checks

Excel normally recalculates formulas when their input cells change. When a result appears stuck, work through these checks before rebuilding the formula. They address the most common causes and help distinguish a calculation problem from a formula that is working but displaying an unexpected result.

Go to Formulas > Calculation Options and choose Automatic.
Select the formula cell and look in the formula bar. Confirm the entry begins with an equals sign and is not preceded by an apostrophe or space.
Check Formulas > Show Formulas. If it is selected, turn it off to display calculated results.
Change a text-formatted formula cell to General, then re-enter the formula by pressing F2 and Enter.
Look for an error value, a circular reference warning, or inputs stored as text.

These steps apply to Excel for Microsoft 365 on Windows. Excel for Mac has the same calculation options on the Formulas tab; some keyboard shortcuts differ. Excel for the web also recalculates formulas, though available commands and workbook features can differ from the desktop app.

Check whether calculation is set to Manual

In Automatic calculation mode, Excel updates dependent formulas when a value changes. In Manual mode, it can leave a formula showing its previous result until you request a recalculation. This is often the reason a workbook appears frozen even though its formulas are valid.

On Windows, open Formulas > Calculation Options and select Automatic. Then press F9 to recalculate open workbooks. To calculate only the active worksheet, press Shift+F9. To force a full recalculation of all formulas in open workbooks, including formulas Excel might otherwise consider unchanged, press Ctrl+Alt+F9. On a Mac, use the Formulas tab’s calculation commands if your keyboard’s function-key settings make those shortcuts inconvenient; some Mac keyboards require Fn with function keys.

Calculation mode is a workbook-level behavior that can affect other workbooks open in the same Excel session. If you open a workbook created by someone else and notice that calculation is Manual, changing it to Automatic can resolve the problem, but it may also affect your other open files. For very large models, a person may deliberately use Manual mode to avoid waiting after every edit. In that case, recalculate after making changes and confirm the setting before sharing the workbook.

When the cell shows the formula instead of the answer

If a cell displays something like =B2*C2 instead of a result, Excel may be showing formulas across the worksheet, or it may be treating that entry as text. These are different issues, and checking the formula bar can help tell them apart.

To switch off worksheet-wide formula display, choose Formulas > Show Formulas. On Windows, Ctrl+` toggles this view; the backtick key is usually near the upper-left of the keyboard. If only one cell displays the formula, select it and inspect its number format and the start of its entry. A leading apostrophe, a leading space, or the Text number format can prevent Excel from interpreting an entry as a formula.

To correct a text-formatted entry, select the cell, choose Home > Number Format > General, then press F2 and Enter to make Excel interpret the existing entry again. Simply changing the format may not convert a formula already stored as text; re-entering it is important. If an apostrophe was intentionally used to preserve text, remove it only if the cell is meant to calculate.

Inspect the formula and its references

A formula can calculate successfully and still produce the wrong answer because it points to the wrong cells, omits part of a range, or uses an operator that does not match the intended calculation. Click the cell and read the formula bar; then compare each reference with the values you expect it to use. If the formula is long, use Formulas > Evaluate Formula to inspect how Excel evaluates it in stages.

For example, with a quantity in B2 and a unit price in C2, enter this in D2:

=B2*C2

If B2 contains 3 and C2 contains 12, the result should be 36. If D2 shows a different result, verify that the values are actually in B2 and C2, and that the formula was entered in the intended row. A reference can shift when a formula is filled down; use dollar signs to make a reference absolute when it must stay fixed, such as a tax rate or lookup input.

For a sum, with values 4, 6, and 3 in B2:B4 and the remaining cells through B10 blank, the following formula returns 13:

=SUM(B2:B10)

Check the range boundaries carefully. A missing row or an accidentally expanded range can make a formula appear not to update correctly when the actual issue is that it is calculating the wrong cells.

Check inputs stored as text and calculation details

Numbers that look like numbers may be stored as text, especially after importing data from a website, CSV file, or another system. Text values can behave differently in arithmetic and summaries. A warning icon may offer a conversion option; otherwise, select the affected cells and use Data > Text to Columns when appropriate, or convert the source data carefully. Check for leading apostrophes, spaces, and inconsistent decimal or thousands separators before converting a large range.

Date values can have a similar problem. Excel stores dates as serial numbers, but an imported date string may remain text, so date subtraction or date-based comparisons can fail or return unexpected results. Confirm that the cell is a real date by changing its number format temporarily to General. A serial number indicates Excel recognizes it as a date value; a date that remains text needs conversion using a method suited to its format and regional settings.

Also consider whether a formula is designed to return the same result after an edit. For example, a formula that references a fixed cell will not change when you edit a different cell. Functions such as NOW and TODAY are time-dependent, but they update when Excel recalculates; they are not a continuously running clock. If a result depends on a data connection or an external workbook, refreshing or updating that source may be necessary in addition to recalculating formulas.

Understand errors, circular references, and blank-looking results

An error value is evidence that Excel is evaluating the formula, but something in the calculation needs attention. #DIV/0! commonly means a divisor is zero or blank. #VALUE! often points to an incompatible value type, such as text where a number is expected. #REF! indicates an invalid reference, often because a referenced row, column, or cell was deleted. #NAME? can mean that Excel does not recognize a function name or a name used in the formula.

A #SPILL! error can occur when a dynamic-array formula needs to place results in neighboring cells but something blocks the spill range. Clear the obstructing cells or move the formula to an open area. Dynamic-array functions such as FILTER and UNIQUE require Microsoft 365 or Excel 2021 or later; availability can vary by Excel version.

A circular reference occurs when a formula directly or indirectly refers back to its own cell. Excel may warn about the reference, and the result may not be what you expect. Find the circular reference through Formulas > Error Checking > Circular References, then correct the references if the loop was accidental. Some specialized models intentionally use iterative calculation, but enabling it changes how Excel handles circular formulas. Do not turn it on as a general fix for a formula that is not updating.

Sometimes a formula returns an empty-looking result because its output is genuinely blank, its result is zero and the worksheet hides zero values, or the cell’s number format conceals the display. Check the formula bar and temporarily apply a standard number format. Also widen the column if a displayed value appears as number signs; that usually indicates the column is too narrow to show the formatted value, not that the formula failed.

Recalculate carefully and prevent repeat problems

Use the least disruptive recalculation option that fits the situation. F9 recalculates open workbooks, Shift+F9 recalculates the active sheet, and Ctrl+Alt+F9 performs a full calculation on Windows. A full recalculation can take time in a large workbook, so it is useful for diagnosing stale results but is not always necessary after every edit. On Mac, use the calculation commands on the Formulas tab if the Windows key combinations do not match your keyboard.

For a more reliable workbook, keep formulas and source values clearly separated, use consistent data types in each column, and avoid pasting imported values over cells containing formulas. When filling formulas down, inspect the first and last few rows to confirm references adjusted as intended. Before sharing a file, check the calculation mode and confirm that important results update when an input changes.

If the workbook is slow, do not assume Manual mode is the only solution. Large ranges, complex formulas, volatile functions, external links, and repeated calculations can all contribute to delays. Check whether formulas include entire columns unnecessarily, whether external links are required, and whether the workbook has data or formatting far beyond its active area. Make a copy before restructuring a complex workbook, and compare key outputs after any changes.

Frequently Asked Questions

Why does Excel show the formula instead of the result?
Turn off Formulas > Show Formulas if formulas are visible throughout the sheet. If only one cell is affected, change its format from Text to General, then press F2 and Enter. Also check for a leading apostrophe or space.

How do I force Excel to recalculate?
On Windows, press F9 to recalculate open workbooks, Shift+F9 for the active sheet, or Ctrl+Alt+F9 for a full recalculation. On Mac, use the calculation commands on the Formulas tab or the shortcut supported by your keyboard settings.

Why does my formula update only after I press F9?
The workbook is likely using Manual calculation. Select Formulas > Calculation Options > Automatic to recalculate when inputs change, unless Manual mode is intentional for a large workbook.

Why is my SUM formula ignoring some numbers?
The entries may be stored as text, may fall outside the formula’s range, or may contain characters that prevent Excel from recognizing them as numbers. Check the source cells’ formats and the range references, then convert text values carefully.

Can I fix a formula by turning on iterative calculation?
Only if the workbook intentionally uses a circular reference and is designed for iterative calculation. For an accidental circular reference, correct the formula instead. Iterative calculation is not a general solution for stale or text-formatted formulas.

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.