Excel Formulas Not Working? Causes and Fixes

Errors & fixes · Updated November 19, 2026

Excel formulas usually start working again after you check three things: the cell contains a formula beginning with =, the cell is not formatted as text, and calculation is set to Automatic. If those checks do not solve it, inspect the formula’s references and syntax, then identify any error code Excel displays.

How Excel formulas work

A formula tells Excel to calculate a result from values, cell references, or functions. For example, a formula can add a range of cells, multiply a quantity by a price, or compare values and return a result. Excel normally recalculates formulas when their inputs change, following the dependency chain between cells.

With sales amounts in B2:B6, where the values are 12, 8, 15, 5, and 10, enter =SUM(B2:B6) in B7. The result is 50. If one of the amounts changes, Excel should update the total. A formula that appears not to update may therefore reflect a calculation setting, an input stored as text, or a reference that does not include the changed cell.

Excel formulas can use relative references, such as B2, which shift when copied; absolute references, such as $B$2, which stay fixed; and mixed references, such as B$2. Understanding those reference types helps explain why a formula can work in one row but produce an unexpected answer after being filled down.

Start with the visible symptoms

Look at what the cell actually displays. A formula shown literally, a stale number, and an error message point to different causes. Before editing anything, select the cell and check the formula bar. It shows the underlying formula or value, even when the worksheet display is confusing.

  • The cell shows the formula text: Check whether Show Formulas is enabled or the cell is formatted as Text.
  • The result does not change: Check calculation mode and whether the formula refers to the cells you edited.
  • The result is an error: Read the error code; it often identifies the type of problem.
  • The result is wrong but has no error: Check the range, cell references, rounding, and whether values are stored as numbers.
  • The formula is rejected as you type: Check parentheses, function names, separators, and whether the formula starts with an equals sign.

Make a copy of important workbooks before applying bulk fixes. Changing a whole column’s format or replacing formula text can affect many cells at once.

Fix formulas displayed as text

If a cell displays something like =SUM(B2:B6) instead of 50, first check whether Excel is displaying formulas across the worksheet. On Windows, press Ctrl+` (the grave-accent key, often beside 1) to toggle Show Formulas. You can also use Formulas > Show Formulas. On a Mac, use Formulas > Show Formulas if the keyboard shortcut differs on your keyboard.

If only one cell shows the formula, its number format may be Text. Select the cell, choose Home > Number Format > General, then edit and re-enter the formula. A practical way is to press F2 and then Enter on Windows. Changing the format alone does not always make Excel reinterpret content already entered as text; re-entering it prompts Excel to evaluate it.

Also look for an apostrophe or a leading space before the equals sign. Excel treats an entry such as '=SUM(B2:B6) as text. Remove the apostrophe or space and enter the formula again. If many imported cells are affected, select the range and use Data > Text to Columns, then choose Finish; check a copy first, since conversion can change how imported data is interpreted.

Check calculation mode and recalculate

When results stay unchanged after inputs change, confirm that calculation is automatic. In Excel for Microsoft 365 on Windows, choose Formulas > Calculation Options > Automatic. You can also check File > Options > Formulas and the Workbook Calculation setting. A workbook set to Manual recalculation will not update every dependent formula after each edit.

In Manual mode, press F9 to calculate open workbooks. Ctrl+Alt+F9 forces a full calculation of all formulas, which can help when Excel’s dependency tracking appears out of date. On a Mac, use Formulas > Calculate Now or Formulas > Calculation Options where available; keyboard shortcuts can differ by Mac model and function-key settings.

Manual calculation can be useful in a large workbook that takes a long time to recalculate, but it can make displayed results look current when they are not. After switching back to Automatic, allow Excel to finish calculating. A status-bar message such as “Calculating” means results may still be updating.

Inspect formula syntax and separators

Formulas need an equals sign at the beginning, balanced parentheses, valid function names, and references that exist. For instance, if quantities are in B2:B6 and prices are in C2:C6, a row total in D2 can multiply the first quantity by its price. With B2 equal to 3 and C2 equal to 4.50, enter =B2*C2 in D2; the result is 13.50. If you fill the formula down, Excel adjusts the relative references for each row.

One common point of confusion is the argument separator. Many English-language installations use commas between function arguments, while some regional settings use semicolons. If a formula copied from a website is rejected, Excel’s regional settings may expect a different separator. Use Excel’s function suggestions as you type or check a working formula in the same installation. Do not replace commas inside quoted text or other parts of a formula indiscriminately.

Check for missing operators, mismatched quotation marks, or a misspelled function name. Excel’s formula bar can help reveal extra characters. If a formula has been pasted from a web page or document, retype suspicious punctuation: typographic “curly” quotes and nonstandard spaces can be different from the straight quotation marks and ordinary spaces expected in formulas.

Verify references, ranges, and copied formulas

A valid formula can still return the wrong answer if it points to the wrong cells. Select the formula cell and inspect the colored reference outlines, or click into the formula bar to see which ranges are included. A total that stops one row too early may omit a new entry; a range that includes a heading may cause a calculation or data-type problem.

When copying a formula, decide whether each reference should move. For example, a tax rate stored in F1 should usually remain fixed while row amounts change. If amounts are in B2:B6 and the rate is in F1, enter =B2*$F$1 in C2, then copy it down. If B2 is 100 and F1 is 0.08, C2 returns 8. The dollar signs lock the rate reference; without them, copying down would move the reference from F1 to F2 and then F3.

Excel Tables can make expanding datasets easier to manage because formulas and structured references can follow added rows. However, a formula may not include new data if it uses a fixed range outside a table. Check whether the data was added within the table and whether the formula’s range covers the intended records.

Understand common Excel error codes

Error messages are clues rather than generic failures. Correct the underlying issue instead of hiding the error immediately; wrapping every formula in an error-suppression function can conceal missing data or broken references.

  • #DIV/0!: A formula divides by zero or by a blank cell. Check the denominator and decide how the workbook should handle missing values.
  • #VALUE!: A function received an unexpected type of value, such as text where a number is required. Check imported data and cell contents.
  • #REF!: A referenced cell or range is no longer valid, often because a referenced row, column, or worksheet was deleted.
  • #NAME?: Excel does not recognize part of the formula. Check spelling, quotation marks around text, and whether a name used in the formula is defined.
  • #N/A: A lookup or other calculation could not find a required value. Check the lookup value, the search range, and whether the values match in type and spelling.
  • #SPILL!: A dynamic-array formula cannot place its results because cells in the spill area are occupied or otherwise unavailable. Clear the obstructing cells and check for merged cells in the output area.

Some newer functions are unavailable in older Excel releases. XLOOKUP, FILTER, SORT, UNIQUE, SEQUENCE, and LET require Excel 2021 or Microsoft 365; LAMBDA, TEXTSPLIT, TEXTBEFORE, TEXTAFTER, VSTACK, and HSTACK require Microsoft 365 or Excel 2024. If a workbook uses an unsupported function, an older version may show an unrecognized-function error. Confirm the recipient’s Excel version before relying on newer functions.

Check imported values and workbook settings

Data copied from a web page, text file, or another application may look numeric while being stored as text. Such values can behave unexpectedly in arithmetic, sorting, and comparisons. Check alignment and number formats, but do not rely on appearance alone. Test a few cells by examining them in the formula bar and comparing their behavior in a simple calculation.

Regional settings can also affect how Excel interprets dates and decimal separators. A date such as 03/04/2026 can be ambiguous across date conventions, and a decimal comma may be used instead of a decimal point. When importing data, confirm the column types and locale rather than assuming Excel has interpreted every value correctly.

Finally, a workbook may contain links to other files, defined names, or settings that affect calculation. If a formula depends on an external workbook, confirm that the source file is available and that links are updated as intended. For a shared file, ask whether another person is using a different Excel version or opening it in Excel for the web, since supported features can vary.

A reliable troubleshooting order

Work from simple display issues toward less obvious formula and workbook problems. This order helps avoid changing a correct formula when the actual cause is formatting or calculation mode.

  1. Select the problem cell and inspect its formula bar and displayed result.
  2. Confirm the entry begins with =, and check for a leading apostrophe or space.
  3. Check Show Formulas and the cell’s number format; set it to General and re-enter the formula if needed.
  4. Set calculation to Automatic, then recalculate if the result remains stale.
  5. Read any error code and verify function availability in the Excel version being used.
  6. Trace cell references, range boundaries, copied references, and the actual data types in the source cells.
  7. Test a corrected formula in one cell or a copy of the workbook before applying a broad change.

Frequently Asked Questions

Why is Excel showing the formula instead of the answer?
Show Formulas may be on, or the cell may be formatted as Text. Toggle Formulas > Show Formulas, then check the cell format. If it is Text, change it to General and re-enter the formula.

Why does my Excel formula not update automatically?
Check Formulas > Calculation Options > Automatic. If the workbook is in Manual mode, switch it to Automatic or recalculate with F9 on Windows. Also confirm that the changed input is included in the formula’s references.

Why does Excel say there is a problem with my formula?
Check the equals sign, parentheses, function spelling, quotation marks, and argument separators. Your regional settings may require semicolons instead of commas between function arguments.

Why does SUM ignore some cells?
The cells may contain numbers stored as text, or they may be outside the selected range. Check the imported values and the range shown in the formula. Convert text values carefully, especially when data includes dates or regional decimal formats.

Why does a formula work in one row but not after I copy it?
Relative references change when copied. If a reference must stay on the same cell, use dollar signs to make it absolute, such as $F$1. Review the references in the copied formula before filling the rest of the column.

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.