Excel Formula Not Working? Common Causes and Fixes

Errors & fixes · Updated November 20, 2026

If a formula is not working in Excel, first check whether the cell is formatted as Text, calculation is set to Manual, or the formula contains a typo or invalid reference. Select the cell, inspect the formula bar, and compare what you see with the specific symptom below; that usually identifies the fix faster than retyping the whole formula.

Identify what “not working” means

Excel formulas can fail in several different ways: the cell may display the formula instead of its answer, show an error such as #VALUE!, return a plausible but incorrect result, or keep showing an old result after the inputs change. These symptoms point to different causes. Start by clicking the cell and looking at both the cell itself and the formula bar.

  • The formula text appears in the cell: Check Show Formulas mode and the cell’s number format.
  • The result does not change: Check calculation mode and whether the formula refers to the cells you edited.
  • An error appears: Read the error code and inspect the formula’s inputs, references, and function names.
  • A result appears but is wrong: Check the range, comparison conditions, number formats, and relative references.

On Excel for Microsoft 365 on Windows, select a problem cell and read the formula bar above the worksheet. You can also double-click the cell or press F2 to inspect its contents. On a Mac, use the formula bar or double-click the cell; keyboard shortcuts can differ by keyboard and Excel settings. If the displayed answer is merely rounded, increase the displayed decimal places from Home > Increase Decimal before assuming the underlying calculation is wrong.

Check formula display and basic syntax

If many cells show expressions rather than results, Excel may be in Show Formulas mode. On Windows, select Formulas > Formula Auditing > Show Formulas to turn it off. The Windows shortcut Ctrl+` (the grave-accent key, often beside 1) also toggles the view. On a Mac, use the Show Formulas command on the Formulas tab if the shortcut differs on your keyboard. Column widths may expand in this view, so the worksheet can look very different until you switch back.

If only one cell displays the expression, check its format. A cell formatted as Text can treat a newly entered formula as ordinary text. Select it, choose Home > Number Format > General, then press F2 and Enter so Excel reinterprets the existing contents. Merely changing the format may not convert a formula already stored as text; editing and confirming it prompts Excel to evaluate it.

Most formulas begin with an equals sign. Check for a missing equals sign, an extra apostrophe or space before it, misspelled function names, and unbalanced parentheses. For example, with quantities in B2:B4 equal to 12, 18, and 20, the formula =SUM(B2:B4) returns 50. It adds the three cells; if a value is actually stored as text, the result may not include it as expected. Excel’s formula bar is useful for spotting characters that are hard to see in the grid.

Confirm Excel is recalculating

When formulas show old answers after their inputs change, calculation may be set to Manual. In Excel for Microsoft 365 on Windows, open Formulas > Calculation > Calculation Options and select Automatic. The corresponding calculation controls are on the Formulas tab in Excel for Mac as well, though the ribbon layout can vary. In Manual mode, Excel waits for a calculation command instead of updating every dependent formula immediately.

To request an update in Windows, press F9 to calculate open workbooks, or use Formulas > Calculation > Calculate Now. Shift+F9 calculates the active worksheet. If recalculation changes the answer, return to Calculation Options and check the setting. Manual mode can be useful in very large workbooks that calculate slowly, but it can make results appear stale if you forget that it is enabled.

A formula can also appear not to update because it does not depend on the cell you changed. Select the formula cell and trace its references in the formula bar. For example, if a total is meant to use B2:B10 but its range ends at B9, entering a value in B10 will not affect it. Automatic calculation cannot correct a range that leaves out the new data.

Check whether numbers and dates are really numbers

Values that look numeric may be stored as text, especially after copying data from a website, a text file, or another system. Text numbers can fail comparisons, arithmetic, sorting, or lookups. A leading apostrophe, spaces, nonbreaking spaces, and inconsistent decimal or thousands separators are common clues. The green error indicator may offer Convert to Number, but check that the conversion is appropriate before applying it to a whole column.

Dates can have the same problem. Excel stores recognized dates as serial numbers and displays them using a date format; a date-looking string imported as text may not behave like a date in calculations. Check the source data, regional date order, and cell alignment, but do not rely on alignment alone because formatting can change it. For a simple calculation, with a start date in A2 and an end date in B2, =B2-A2 returns the number of days between recognized Excel dates. If A2 is January 1, 2026 and B2 is January 10, 2026, the result is 9.

Also distinguish the stored value from its display. A percentage formatted as 25% is stored as 0.25, and a currency format does not change the underlying amount. A formula can calculate correctly while the chosen number format makes the result surprising. Select the result cell and check Home > Number Format, then use the formula bar to inspect the underlying value.

Use the error message as a clue

Error codes narrow the search, but they do not identify every cause by themselves. Read the formula and inspect the cells it uses rather than replacing an error with a blank immediately. Hiding an error can make a report look tidy while concealing a broken calculation.

  • #NAME? Excel does not recognize text in the formula. Check function spelling, named ranges, and quotation marks around text. A function available only in newer Excel versions may also fail in an older version.
  • #VALUE! A function received an input of an unexpected type, such as text where a number is needed. Check imported values and any calculation combining text with numbers.
  • #REF! A reference is invalid, often because a referenced row, column, or sheet was deleted. Edit the formula to point to a valid location.
  • #DIV/0! A division uses a zero or blank denominator. Check the denominator and decide how the calculation should handle an unavailable value.
  • #N/A A lookup or another function could not find a required value. Check whether the lookup value exists and whether the values have matching types and spaces.
  • #SPILL! A dynamic-array formula cannot place its results because cells in the spill area are occupied or otherwise unavailable. Clear the obstructing cells, while preserving any data that belongs there.

For example, with a divisor in B2, =10/B2 returns #DIV/0! if B2 is zero or blank. The remedy depends on the intended meaning: correct the input, or explicitly decide what the result should be when no divisor is available. Avoid adding error-handling functions until you know why the error occurs.

Inspect references, ranges, and formula copying

Copying a formula can change its references. A relative reference such as A2 shifts when copied to another cell; an absolute reference such as $A$2 stays fixed. Mixed references, such as $A2 or A$2, lock only the column or row. This behavior is intentional, but it can produce incorrect results if a formula is filled across or down without the right reference type.

With a price in B2 and a tax rate in F1, for instance, a formula copied down a list should keep pointing to F1 while allowing the price row to change. Use =B2*$F$1 in C2; if B2 is 40 and F1 is 10%, the result is 4. Copying that formula to C3 changes B2 to B3 but keeps the tax-rate reference fixed. On Windows, select a reference while editing and press F4 to cycle through reference styles; on a Mac, the equivalent can depend on the keyboard and function-key settings.

Check range boundaries and sheet names too. A formula can be syntactically valid while omitting the latest row, including a header, or pointing to a similarly named sheet. If a formula uses a range on another sheet, confirm that the sheet still exists and that the reference points to the intended cells. When checking a total, compare the selected range with the actual first and last data rows.

Check function availability and workbook context

Newer Excel functions are not available in every older release. XLOOKUP, XMATCH, 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 is opened in an older edition, an unsupported function may display as #NAME? or may not calculate as expected. Confirm the Excel version on the computer that must use the file before choosing a function.

Some workbooks also depend on external links, add-ins, or data connections. A formula referencing another workbook may show an old value or prompt for an update if the source file is unavailable. Check the source path and link-update prompts, and do not enable links from an unknown file without considering whether the source is trustworthy. If formulas use custom functions supplied by an add-in, confirm that the add-in is installed and enabled on the computer opening the workbook.

A practical troubleshooting sequence

Use a consistent order so you do not change several things at once and lose track of the cause:

  1. Click the cell and inspect the formula bar. Note whether you see a formula, a result, or an error.
  2. If the formula itself is visible, turn off Show Formulas; if only that cell shows text, change its format to General and confirm the entry again.
  3. If the result is stale, set calculation to Automatic and calculate now.
  4. If there is an error or wrong answer, check the function spelling, parentheses, input types, references, and range boundaries.
  5. Test with a small known example or compare the formula’s referenced cells with the data you expect it to use.

Make one change at a time and observe whether the result changes. For important workbooks, save a copy before making broad edits to formulas or imported data. This is particularly helpful when replacing text values, filling formulas through a large range, or changing calculation settings in a workbook used by other people.

Frequently Asked Questions

Why is Excel showing the formula instead of the answer?
Check whether Show Formulas is enabled for the worksheet. If only one cell is affected, it may be formatted as Text or contain a leading apostrophe. Set the cell to General, then edit and confirm the entry so Excel evaluates it.

Why does my Excel formula not update automatically?
Check Formulas > Calculation > Calculation Options and select Automatic. If calculation is already automatic, confirm that the formula actually references the cell you changed and that its range includes the new data.

Why does Excel say there is a problem with my formula?
Look for a missing equals sign, misspelled function, unmatched parentheses, or a reference to a deleted cell or sheet. Formula separators can also depend on regional settings; if Excel rejects commas between function arguments, check the list-separator settings used by your system.

Why does SUM ignore some cells?
Some entries may be text rather than numbers, or the formula’s range may not include those cells. Check the stored values and the range boundaries. A number format that looks numeric does not, by itself, convert text into a number.

What should I do if a formula returns #NAME?
Check spelling and named ranges first, then confirm the function is supported in the Excel version opening the workbook. A function introduced in a newer release may not work in an older edition; updating Excel or using a compatible alternative may be necessary.

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.