If Excel is not doing formulas, first check Formulas > Calculation Options > Automatic, then make sure the formula cell is not formatted as Text and that the formula begins with an equals sign. If the formula still does not calculate, check whether Excel is displaying formulas, whether a reference or input is wrong, and whether the cell contains an error.
A formula can appear broken in several different ways: Excel may show the formula characters instead of a result, leave an old result on screen, reject the entry, or calculate a result that looks wrong. The fix depends on which symptom you see. These checks are for Excel for Microsoft 365 on Windows; the same core ideas apply to other desktop versions.
Start with the quickest checks
Before rewriting a formula, inspect the cell and workbook settings. These checks resolve many cases without changing the calculation itself:
- Click the formula cell and look at the formula bar. Confirm the entry starts with
=and is the formula you expect. - On the ribbon, select Formulas > Calculation Options and choose Automatic.
- On the Formulas tab, check whether Show Formulas is enabled. Turn it off to display results.
- If the formula is visible as text, change the cell’s number format to General, then re-enter the formula.
- Look for an error value such as
#VALUE!,#NAME?, or#REF!. Each points to a different kind of problem.
After changing a setting or formula, press Enter to commit the entry. A cell can look selected or edited without the change having been committed yet.
Check Excel’s calculation mode
Excel normally recalculates formulas automatically when their inputs change. If the workbook is set to manual calculation, a formula can remain on an old result even though its referenced values have changed. This is especially noticeable in large workbooks, where someone may have selected manual calculation to avoid delays.
To check the setting in Excel for Microsoft 365 on Windows, select Formulas > Calculation Options > Automatic. If the workbook needs an immediate refresh, press F9 to calculate open workbooks, or Shift+F9 to calculate the active worksheet. To force a full calculation, use Ctrl+Alt+F9. These commands recalculate; they do not repair a bad formula or incorrect input.
Calculation mode can affect more than the one cell you are viewing: it is an Excel application setting that can apply across open workbooks. If changing it seems to affect another workbook, check the setting again rather than assuming each file has an independent mode. On Mac, keyboard shortcuts can differ by keyboard and Excel release; use the Formulas tab’s calculation controls if a shortcut does not work.
When a formula is stored as text
A common cause is that Excel treats the entry as text instead of interpreting it as a formula. This often happens when a cell or column was formatted as Text before the formula was typed, or when an apostrophe was placed before the equals sign. In these cases, the cell may literally display something like =A1+B1 rather than calculating it.
To fix one affected cell, select it, choose Home > Number Format and set the format to General. Then click in the formula bar or press F2, and press Enter. Changing the format alone may not convert an existing text entry; re-entering it makes Excel interpret the formula again. If many cells are affected, test the method on a copy or a small range first, because bulk conversions can also change other text entries.
Also check for an apostrophe or spaces before the equals sign. An apostrophe is sometimes used intentionally to preserve text, but it prevents calculation. If a formula was pasted from a website, email, or another program, retype the leading equals sign and inspect for unusual characters. A formula that starts with a visible space before = is not the same as one that starts with =.
Make sure Excel is not showing formulas
If many cells display their formulas at once, rather than calculated values, Show Formulas may be on. This is a display option; it does not mean the formulas have stopped calculating. On Windows, toggle it from Formulas > Show Formulas. The shortcut is Ctrl+` (the grave-accent key, often near the upper-left corner of the keyboard). Keyboard layouts vary, so the ribbon command is a reliable alternative. On Mac, use the command on the Formulas tab if the Windows shortcut differs.
Show Formulas can also make columns appear too narrow because formulas usually take more space than results. If only one cell shows formula text, check that cell’s format and entry instead; the display setting generally affects the worksheet, not just one formula.
Inspect the formula, references, and error message
If Excel accepts the formula but returns an error or an unexpected value, check the expression and the referenced cells. A formula can be syntactically valid while still pointing at the wrong row, using text where a number is expected, or dividing by a blank or zero value. Select the cell and read the formula bar carefully. If the formula has several parts, inspect the inputs one at a time rather than replacing it with a different formula immediately.
For example, with quantities in B2:B5, a total in B6 can be calculated with =SUM(B2:B5). If those four cells contain 3, 4, 5, and 8, the result is 20. If the total does not match expectations, check that the values are actually in B2:B5 and are numeric. A value that looks like a number but is stored as text may not behave as expected in every calculation.
Common errors offer useful clues:
#NAME?often means Excel does not recognize a function or name, or that text in a formula is missing quotation marks.#REF!means a reference is invalid, often because a referenced row, column, or cell was deleted.#DIV/0!appears when a formula divides by zero or by a blank cell treated as zero.#VALUE!often indicates that an argument has an unexpected type, such as text where a number is required.#SPILL!can occur when a dynamic-array formula cannot place its results because cells in its output area are occupied.
Function names and argument separators can also depend on Excel version and regional settings. The examples here use comma separators, as requested for standard English-language Excel syntax. If Excel rejects an otherwise valid formula while you type it, your regional list separator may be different; check your system’s regional settings rather than changing the calculation mode.
Check inputs and calculation dependencies
Sometimes the formula works, but the source values are not what they appear to be. Dates and numbers pasted from another system can be stored as text. A cell may also contain a space, a hidden character, or a different value than the one shown because of its number format. Click the input cell and inspect the formula bar; changing a display format does not necessarily change the underlying value or convert text into a number.
Relative references are another frequent source of surprises. When a formula is filled down, a reference such as B2 normally changes to B3, B4, and so on. If every row should refer to the same cell, an absolute reference such as $B$2 may be needed. Conversely, copied formulas can keep referring to the wrong range if they were not filled or copied as intended. Compare the formula in a working row with the formula in a problem row.
A circular reference occurs when a formula depends on its own result, either directly or through a chain of other cells. Excel may show a warning or calculate unexpectedly depending on workbook settings. Trace the references to find the loop; do not enable iterative calculation as a quick fix unless the workbook is deliberately designed to use repeated calculations.
A reliable troubleshooting sequence
Work from broad settings to the specific cell. This keeps a simple display or format problem from being mistaken for a broken formula:
- Identify the symptom: formula text, stale result, error message, rejected entry, or incorrect result.
- Check Formulas > Calculation Options and choose Automatic if the workbook should recalculate as inputs change.
- Check Show Formulas and turn it off if formulas are being displayed across the sheet.
- For a single formula displayed as text, change its format to General, edit it, and press Enter. Remove any leading apostrophe or space.
- Read the formula bar and verify each reference, input value, and operator. Follow any error value back to its source cells.
- Try recalculating with F9. If values remain stale, use the full recalculation shortcut or the calculation command on the ribbon.
If the issue began after a paste, import, or workbook conversion, compare a problem cell with a nearby working cell. That often reveals a text format, altered reference, or unexpected character. Save a copy before making broad changes, especially when the workbook contains important data or complex formulas.
Excel for Mac and Excel for the web
The main troubleshooting ideas are the same on Mac and in Excel for the web: check automatic calculation, formula display, cell format, and the formula itself. Ribbon layout and keyboard shortcuts can vary by platform and release. On Mac, use the corresponding calculation and formula-display commands on the ribbon when a Windows shortcut is unavailable or behaves differently.
Excel for the web has a browser-based interface, and some advanced workbook features may behave differently from the Windows desktop app. If a workbook has a complex calculation problem, open it in the desktop version where possible and verify the calculation settings and formulas there. Avoid assuming that a formula is invalid merely because a command or shortcut is located differently in the web interface.
Frequently Asked Questions
Why does Excel show the formula instead of the answer?
If formulas appear throughout the sheet, turn off Formulas > Show Formulas. If only one cell shows formula text, set that cell to General format and re-enter the formula. Also check for a leading apostrophe.
Why does my Excel formula not update when I change a value?
Check that calculation is set to Automatic under Formulas > Calculation Options. In manual mode, Excel can keep showing an earlier result until you recalculate. Also confirm that the formula refers to the cell you changed.
Why is Excel displaying 0 instead of the formula result?
Inspect the formula and its input cells first. A reference may point to a blank or zero-valued cell, or the formula may be using the wrong range. Check whether inputs that look numeric are actually stored as text, and confirm that the displayed zero is not a result the formula legitimately produces.
Why does Excel say there is a problem with this formula?
Check for a missing parenthesis, an incorrect function name, an invalid reference, or a separator that does not match your regional settings. If your Excel setup uses a different list separator, adjust the separators to match that setup; do not assume the formula’s underlying logic is wrong.
Can I force Excel to recalculate everything?
On Windows, Ctrl+Alt+F9 forces a full calculation. Use it after checking the formula and calculation mode, not as a substitute for fixing invalid references or incorrect inputs. On another platform, use the calculation command available on the Formulas ribbon.
Related
- Excel Data Model in the Web: What Works and What Doesn’t
- Excel Data Analysis Toolkit: Setup, Tools, and Practical Uses
- Excel Data Analysis Sampling: Methods, Steps, and Uses
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- Stellar Fix for Corrupted Excel Files: What to KnowYes. Stellar Repair for Excel is a third-party utility designed to recover data from certain damaged Excel workbooks,…
- Dollar Sign Not Working in Excel? Fix Common CausesIf the dollar sign is missing in Excel, first check what you mean: press Shift+4 on a US…
- IFERROR Not Working in Excel? Find and Fix the CauseIf IFERROR is not working in Excel, first check that the formula is written as =IFERROR(your_formula, value_to_show_if_an_error_occurs) and…
- Excel HLOOKUP Not Working? Fix Lookup and Match Errors NowIf HLOOKUP is not working, first check that the value you want to find is in the first…
- Excel Circular References: How to Find and Fix ThemIn Excel for Microsoft 365 on Windows, go to Formulas > Error Checking > Circular References to see…
- Excel Filter Dropdown Not Working? Fix Common CausesIf an Excel filter dropdown is missing, will not open, or shows the wrong results, first select a…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.