An Excel formula usually fails to calculate because the workbook is set to Manual calculation, the cell is storing the formula as text, or Excel is showing formulas instead of results. Check those three causes first; then verify the formula syntax, references, and any errors in the cells it depends on.
Check whether calculation is set to Manual
Excel can be configured to recalculate formulas automatically or wait for you to request a calculation. In Microsoft 365 for Windows, open the Formulas tab and select Calculation Options. Choose Automatic if it is not selected. With Manual calculation enabled, an edited input cell may change while formulas that depend on it continue to display an earlier result.
After changing the setting, force a recalculation. Press F9 to calculate formulas in all open workbooks, or Shift+F9 to calculate the active worksheet. If a result still appears stale, Ctrl+Alt+F9 recalculates all formulas, and Ctrl+Shift+Alt+F9 rebuilds the dependency information before recalculating. On some laptops, you may need to hold the Fn key to use function-key shortcuts. On Mac, use the Formulas tab’s calculation controls or the corresponding recalculation shortcut, which can depend on the keyboard and macOS settings.
Excel also offers Automatic Except for Data Tables. This setting calculates ordinary formulas automatically but does not automatically recalculate What-If Analysis data tables. If a result belongs to a data table, check this option and recalculate the table as needed. A workbook using Manual mode can affect calculations while other workbooks are open, so check the setting when results seem unexpectedly out of date.
Make sure the cell contains a formula, not text
A formula may look right in the formula bar but be stored as ordinary text. This often happens when a cell was formatted as Text before the formula was entered, or when an apostrophe was typed before the equals sign. Excel then displays the characters instead of evaluating them.
Select the cell and look at the formula bar. If the entry begins with an apostrophe, remove it. If the cell is formatted as Text, select it, choose Home > Number Format, and change the format to General. Changing the format alone does not always convert an existing text entry into a working formula; edit the cell and press Enter to make Excel read the entry again. You can press F2 and then Enter on Windows. On Mac, use the equivalent edit-and-confirm action for your keyboard setup.
For example, with a value in A1, an entry typed into a General-formatted cell as =A1+1 is evaluated. If the same characters were entered while the cell was formatted as Text, Excel may show the formula itself. Re-entering it after changing the format should display the calculated value. A leading space before the equals sign can cause a similar problem because Excel may treat the entry as text.
Turn off Show Formulas
If many cells display expressions instead of results, the workbook may be in Show Formulas mode. This is a display setting, not a calculation failure: the formulas may still be calculating, but Excel is showing their contents in the grid. On Windows, press Ctrl+` (the grave-accent key, often near the upper-left corner of the keyboard) to toggle the view. You can also use Formulas > Show Formulas. On Mac, use the Show Formulas control on the Formulas tab; the keyboard shortcut can vary by keyboard layout.
When Show Formulas is on, columns may appear wider because Excel needs to display the formula text. If only one cell shows an expression while nearby cells show values, the cell is more likely stored as text or has an entry problem. This distinction helps avoid changing calculation settings when the issue is only how the worksheet is being displayed.
Check the formula syntax and argument separators
Excel cannot calculate an expression it cannot parse. Look for a missing opening or closing parenthesis, a misspelled function name, an incomplete cell reference, or an operator in the wrong place. When you begin entering a function, Excel can show a syntax hint under the formula bar. Function arguments are commonly separated by commas, but regional settings can use semicolons as the list separator. If a copied formula produces a syntax error, check which separator your Excel installation expects rather than changing the arithmetic.
Use a small, known example to separate a syntax problem from a data problem. With numbers 10, 15, and 20 in cells B2:B4, the formula in B5 is =SUM(B2:B4); it should return 45. If Excel rejects the formula, inspect the separator and spelling. If it accepts the formula but returns an unexpected number, verify the cells and their contents.
Also check that a formula begins with an equals sign. An entry such as SUM(B2:B4) without the initial equals sign is not interpreted as a formula. Parentheses must be balanced, and text values used by a formula need the correct syntax. If a formula was copied from a webpage or another program, retype suspicious punctuation: curly quotation marks or unusual spaces can differ from the characters Excel expects.
Verify references and input values
A formula can calculate correctly and still produce a result that seems wrong because it points to the wrong cells. Inspect the colored reference outlines that appear while editing a formula, then confirm that the ranges include the intended rows and columns. When copying a formula, relative references move with the copied cell. Absolute references, marked with dollar signs, stay fixed. A reference that was correct in the first row can therefore shift unexpectedly when filled down.
For a simple example, with a price in B2 and a quantity in C2, enter =B2*C2 in D2. If B2 contains 12 and C2 contains 3, D2 should show 36. If the result is different, confirm that the values are actually in B2 and C2, and check whether one of them is text rather than a number. Cells can look numeric while containing imported text, spaces, or other characters that affect calculations.
For totals, inspect the range boundaries. With the intended figures in B2:B4, =SUM(B2:B4) includes those three cells, but not a value in B5. A filtered or hidden row may still be included in a normal SUM calculation. If you want a total that responds to filtered rows, use a function designed for that purpose, such as SUBTOTAL, and check its options against your needs.
Understand errors and circular references
An error value is Excel’s way of signaling that a formula could not produce an ordinary result. Common examples include #DIV/0! when a calculation divides by zero, #VALUE! when an operation receives an incompatible value, #REF! when a referenced cell or range is no longer valid, and #NAME? when Excel does not recognize text in the formula as a function or defined name. Follow the error to the first broken input or reference; changing the final formula without understanding the cause can hide a data problem.
A circular reference occurs when a formula refers to its own cell, either directly or through a chain of other formulas. For instance, a total formula placed inside the range it sums can create a circular reference. Excel may warn you about the circular reference, and the result may be zero, incomplete, or otherwise unexpected depending on workbook settings. Move the total outside the range or correct the references. Iterative calculation is available for intentional circular models, but it should only be enabled when the model is designed to use repeated calculations.
Functions that depend on external workbooks or data sources can also appear not to update. The source file may be closed, the link may be broken, or a query may need refreshing. Check the link or refresh controls rather than assuming that pressing a calculation key will retrieve new external data; recalculation and data refresh are different operations.
Check formatting, dates, and displayed precision
Number formatting usually changes how a result looks, not the underlying calculation. A cell formatted as a date can display a serial number as a date, and a percentage format can display a decimal in percentage form. A narrow column may show hash marks for a value that does not fit; widen the column to see the result. These display issues can make a correct calculation seem broken.
Rounding can also create a mismatch between what is displayed and what Excel stores. A cell might show 1.23 while retaining more decimal places for later calculations. Use the increase/decrease decimal controls or inspect the formula bar to see more precision. The Set precision as displayed option is different: it changes stored values to match displayed precision and can permanently discard detail, so do not use it casually.
Dates deserve special attention because Excel stores dates as numbers and interprets date-like text according to regional settings. An imported date may be text rather than a real date value, or a day and month may be interpreted in the opposite order from what you expect. Check the cell format and test whether the value behaves like a date in a simple calculation. Correct the source data or convert it deliberately instead of relying on appearance alone.
A practical troubleshooting order
Work from the fastest, least disruptive checks toward more detailed formula inspection. This order helps identify whether the issue is workbook-wide, limited to one cell, or caused by a particular input:
- Check whether the cell shows a result, a formula string, or an error value.
- If formulas appear throughout the sheet, turn off Show Formulas.
- Confirm the calculation mode is Automatic, then recalculate with F9.
- Check the affected cell’s number format and re-enter a formula stored as text.
- Inspect the formula’s spelling, punctuation, separators, parentheses, and cell references.
- Check the input cells for text masquerading as numbers, blanks, errors, or unexpected values.
- Look for circular references, broken external links, or data that needs refreshing.
Test a complicated formula in smaller pieces when the cause remains unclear. Select a reference within the formula bar to inspect the referenced range, or temporarily calculate a simpler part of the expression in an unused cell. For example, if a long formula combines several operations, check each input and intermediate result separately. Avoid replacing formulas with typed values as a permanent fix: that may make the visible result look right while removing the automatic link to changing inputs.
Frequently Asked Questions
Why does Excel show the formula instead of the answer?
Show Formulas may be enabled, or the cell may contain text rather than a formula. Toggle Formulas > Show Formulas first. If only one cell is affected, check for Text formatting, an apostrophe, or a leading space, then re-enter the formula.
Why does my formula update only after I press F9?
The workbook is probably using Manual calculation. Select Formulas > Calculation Options > Automatic, then recalculate. If the formula depends on a data table, check whether the setting is Automatic Except for Data Tables.
Why does Excel show 0 when my formula should have a value?
Check the referenced cells, calculation mode, and whether the values are stored as numbers. Also confirm the formula’s range includes the relevant cells. A zero can be a valid result, so trace the inputs before changing the formula.
Why is my formula not calculating after I change the cell format?
Changing a cell from Text to General does not necessarily convert an existing text entry. Edit the cell and press Enter so Excel parses it again. Also check for an apostrophe or space before the equals sign.
Why does my formula return an error instead of a number?
The error code points to the type of problem. Check for zero divisors, incompatible values, invalid references, misspelled function names, and circular references. Correct the underlying input or reference before deciding whether an error-handling function is appropriate.
Related
- Excel Split Cell Data Into Multiple Columns
- Excel Split Cell Into First and Last Name: A Clear Guide
- Excel Split Cell by Space Delimiter: Methods and Tips
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Formulas: Function List, Categories & ExamplesThere is no single finite list of every Excel formula: a formula is any expression you build, while…
- Excel Conditional Formatting Countif FormulaThe direct answer: you cannot use COUNTIF as a standalone conditional formatting rule, but you can apply a…
- How to Share a Calendar in Outlook with the Right AccessTo share a calendar in Outlook, open Calendar, choose the calendar you own, select Share or Share Calendar,…
- Excel Conditional Formatting Formula if BlankThe formula to highlight blank cells in Excel conditional formatting is =ISBLANK(A1) (or =A1=""), applied to the range…
- Excel Formula to Find All Tab Names in Your WorkbookExcel has no built-in worksheet formula that directly returns every tab name. In Microsoft 365 for Windows, you…
- Excel COUNTIFS With Multiple Criteria in One RangeYes. Excel’s COUNTIFS can apply more than one condition to the same range by repeating that range for…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.