If an Excel formula is not calculating, first check Formulas > Calculation Options > Automatic, then make sure the cell contains a formula rather than text. Press F9 to recalculate; if the result still does not update, work through the checks below to find what is preventing Excel from evaluating it.
Start with the quickest checks
Look at the formula bar after selecting the cell. A calculated formula normally begins with an equals sign and the cell displays its result, while the formula bar shows the expression. If the cell displays the expression itself, Excel may be treating it as text or formula display may be turned on. If it displays a number that seems old, calculation mode or a dependency may be the issue. If it displays an error such as #VALUE! or #NAME?, Excel is calculating but the formula has a problem that needs a different diagnosis.
Try this short sequence before changing workbook settings:
- Select the formula cell and check the formula bar for an equals sign at the beginning.
- Choose Formulas > Calculation Options and confirm Automatic is selected.
- Press F9 to recalculate, or use Fn+F9 if your keyboard reserves its function keys for hardware controls.
- Check whether Formulas > Show Formulas is enabled. If it is, turn it off to see calculated results.
- Change a referenced input briefly and check whether the result responds, then undo the change.
These checks distinguish a display problem from a calculation problem. Avoid overwriting the formula while troubleshooting: first make a copy of the workbook if it contains important work, then inspect the cell and its inputs.
Check Excel’s calculation mode
Excel can calculate formulas automatically or wait until you request a calculation. In Microsoft 365 for Windows, open the Formulas tab, select Calculation Options, and choose Automatic. In Manual mode, a formula can remain visible with an old result until Excel recalculates it. This often happens after working with a large workbook or opening a file that was saved while calculation was set to Manual.
After selecting Automatic, press F9. In Excel for Windows, F9 calculates formulas in open workbooks; Shift+F9 calculates the active worksheet. Ctrl+Alt+F9 forces a full calculation, which can help when ordinary recalculation does not refresh results. Use the stronger command only when needed, since a large workbook may take longer to calculate. On a Mac, the Formulas tab also provides calculation controls; depending on the keyboard settings, you may need Fn with a function-key shortcut.
Automatic calculation is generally the right choice for everyday work. Manual mode is useful when a workbook is unusually large and frequent recalculation slows editing, but it can make results look current when they are not. If you use Manual mode, remember to recalculate before relying on results or sharing the workbook. Calculation mode can affect other open workbooks in the same Excel session, so check it again if behavior changes after opening another file.
Make sure the cell is not formatted as text
If Excel shows the formula characters instead of a result, the cell may have been formatted as Text before the formula was entered. This commonly happens when data is imported, pasted from another application, or entered into a column that was previously set to Text. A leading apostrophe can also tell Excel to keep an entry as text.
To correct a text-formatted formula, select the cell, choose Home > Number Format, and change Text to General. Then edit the cell and press Enter; changing the format alone may not cause an existing text entry to be interpreted as a formula. For a range, select all affected cells, change the format, and re-enter each entry or use a suitable conversion method. Check a small sample first so you do not unintentionally alter other text in the range.
With A1 as a test cell formatted as General, entering =1+1 should display 2. If A1 is formatted as Text before entry, it may display the characters instead; changing the format and re-entering the expression should make Excel calculate 2. A cell can also contain a leading space before the equals sign, which prevents Excel from recognizing the entry as a normal formula. Inspect the beginning of the formula bar contents if formatting changes do not solve it.
Look for formula syntax and reference problems
A formula must start with an equals sign. A missing equals sign makes an entry ordinary text or a value, not a formula. Also check for a misspelled function name, a missing parenthesis, an invalid cell reference, or an argument separator that does not match your regional settings. Excel installations commonly use commas between function arguments, but some regional configurations use semicolons. If a formula copied from a website is rejected, compare its separators with a formula Excel creates through the Insert Function dialog.
For a simple sum, assume the numbers 12, 18, and 20 are in B2, B3, and B4. In B5, enter =SUM(B2:B4); the expected result is 50. If the result is not 50, check whether the source cells actually contain numbers, whether the range includes the intended rows, and whether B5 is formatted to show the result appropriately.
References can also point somewhere unexpected after copying a formula. Relative references shift when a formula is copied, while dollar signs make a reference absolute or partly fixed. If a formula works in one row but not another, compare the references in both cells rather than assuming they stayed identical. For example, if a quantity is in A2 and its unit price is in B2, then in C2 enter =A2*B2. With A2 containing 3 and B2 containing 8, the expected result is 24. If copying this formula down changes a reference that should stay fixed, add dollar signs to lock the relevant row, column, or both.
Separate a stale result from an error
A stale value and a formula error are different problems. A stale value is a previously calculated result that has not been refreshed; the calculation-mode check is the first place to look. An error value means Excel evaluated the expression but could not return a normal result. The error text is a clue: #DIV/0! often means a calculation attempted division by zero, #REF! indicates an invalid reference, and #NAME? can indicate an unrecognized function or misspelled name.
#VALUE! often points to an incompatible value type, such as text where a calculation expects a number. A cell may look numeric but still contain text, especially after import. Check alignment only as a hint, not proof: inspect the value, test a small sample, and use the warning or conversion options Excel offers when appropriate. Do not convert a whole column blindly if it contains identifiers such as product codes or postal codes, where leading zeros may matter.
Sometimes the calculation is correct but the displayed value is misleading. A cell formatted as a date, percentage, currency, or rounded number can show a result differently from its underlying value. Select the cell and inspect the formula bar and number format. Widening the column may reveal #### in a numeric or date cell; those hash marks usually mean the column is too narrow to display the value, not that the formula failed.
Check formula display and result formatting
When many cells show formulas instead of results, the Show Formulas setting may be on. Select Formulas > Show Formulas to toggle it off. This setting changes what the worksheet displays, not the formulas themselves. It can be useful when auditing a sheet, but it can make a working spreadsheet look as though every formula has stopped calculating.
If only one cell shows its formula, check its number format and content rather than assuming the whole worksheet setting changed. Also verify that the formula bar displays the expected expression and that the cell is not displaying a result in an unexpected format. For example, a percentage stored as a decimal may appear as a small number if the cell is formatted as General, while a date serial may appear as an integer. The underlying calculation and the visual presentation are separate parts of Excel.
Investigate inputs, links, and circular references
A formula can be valid but depend on an input that has not changed, is blank, or comes from another workbook. Trace its references with Formulas > Trace Precedents to see which cells feed the selected formula. If the result depends on another worksheet, confirm that the referenced sheet and range contain the values you expect. For an external workbook link, check whether the source file is available and whether Excel is showing a prompt to update links. A linked result may reflect the last saved source value rather than a newly edited source file.
Some formulas intentionally depend on their own result, directly or through a chain of other cells. This is called a circular reference. Excel usually warns when one is created, and the status bar may identify a circular reference. Most ordinary formulas should not be circular. If one is accidental, edit the references so the formula depends only on inputs. If a workbook deliberately uses iterative calculations, confirm that the workbook’s iterative calculation settings are appropriate before changing them; enabling iteration can change how a circular model behaves and is not a general fix for formulas that fail to update.
Functions that rely on changing external data, such as some data connections or linked sources, may require a refresh rather than a normal recalculation. Look for Data > Refresh All when the workbook uses queries or connections. Refreshing retrieves source data; recalculating evaluates formulas using the data already in the workbook. Knowing which operation is needed prevents repeatedly pressing F9 when the actual input has not been refreshed.
When recalculation still does not fix it
If one formula remains wrong after the basic checks, isolate the smallest reproducible case. Make a copy of the workbook, copy the formula and the cells it references to a blank worksheet, and see whether the issue follows the formula or depends on the original workbook. Check for hidden rows or columns, merged cells, named ranges, and formulas that refer to a different sheet than expected. Use Formulas > Evaluate Formula to inspect how Excel evaluates a complex expression step by step.
Also consider version compatibility. A workbook using a newer function may not calculate properly in an older Excel release, which may show a #NAME? error or a compatibility warning. Microsoft 365 receives newer functions than perpetual older releases, so confirm the Excel version on the computer opening the file. If the workbook was created in another spreadsheet program, some formulas or features may not transfer exactly. Do not replace a function with a guessed alternative until you understand what the original formula is meant to return.
Finally, save the workbook, close it, and reopen it if the behavior appears limited to one session. If the problem remains, note the formula, its input values, Excel version, and any error message before seeking help. That information makes it easier to distinguish a workbook-specific issue from an application setting or unsupported feature.
Frequently Asked Questions
Why is Excel showing the formula instead of the answer?
Check whether Show Formulas is enabled under Formulas > Show Formulas. If only one cell is affected, change its number format from Text to General, then edit the entry and press Enter. Also look for a leading apostrophe or space before the equals sign.
How do I make Excel recalculate a formula?
Press F9 to calculate, or Shift+F9 to calculate the active worksheet in Excel for Windows. First confirm that Formulas > Calculation Options is set to Automatic if you want formulas to update as inputs change. On some Mac keyboards, use Fn with F9.
Why does my formula update only after I press F9?
The workbook is likely in Manual calculation mode. Select Formulas > Calculation Options > Automatic. Manual mode can be useful for large workbooks, but formulas will not necessarily refresh after each edit until you request a calculation.
Why does a formula return zero when the source cells contain values?
Check the referenced range and whether its values are numbers or text. Confirm that the formula points to the intended sheet and cells, and inspect the cell’s number format in case the displayed result is being rounded or otherwise presented unexpectedly. A formula may also legitimately return zero if its conditions or inputs produce zero.
Why does Excel reject a formula copied from another source?
Check the equals sign, function name, parentheses, and argument separators. Regional settings can mean your Excel expects semicolons rather than commas between arguments. A copied formula may also use a function that is unavailable in your Excel version or references cells that do not exist in the current workbook.
Related
- Excel Slicer as a Dropdown: Options and Workarounds
- Excel Sheet Not Responding? Causes and Safe Fixes Guide
- Excel Show Print Area Lines: View Page Boundaries
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- Excel Cannot Group Dates? Fix the PivotTable ErrorIn Excel for Microsoft 365 on Windows, the PivotTable message “Cannot group that selection” usually means the date…
- Power Query Replace Value Not Working? Find the FixWhen Power Query’s Replace Values command appears not to work, the cause is usually a mismatch in the…
- Why Won’t Data Validation Work in Excel? Common FixesExcel data validation usually seems not to work because the rule is missing or applied to the wrong…
- Excel Scatter Plot Not Plotting X Values? Here’s How to FixIf an Excel scatter plot is not plotting your x values, first confirm that the chart type is…
- Excel Shortcut Not Working? Find and Fix the CauseIf an Excel shortcut is not working, first check that Excel has focus, that you are pressing the…
- Excel Copy Formula Drag Down Not Working? Try These FixesIf dragging a formula down in Excel does nothing, first check that the fill handle is enabled: go…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.