Excel Formulas Not Working? Fix Common Problems

Errors & fixes · Updated November 19, 2026

Excel formulas usually stop working as expected because the cell is formatted as text, calculation is set to manual, a reference is wrong, or the formula contains an error. Start by checking the formula bar, set calculation to Automatic under Formulas > Calculation Options, and inspect the cell format; then use the specific error or unexpected result to narrow down the cause.

Start with a quick diagnosis

First, click the problem cell and look at the formula bar. If it shows a formula beginning with an equals sign but the cell displays an unexpected value, the formula may be calculating correctly from unexpected inputs, or a reference or setting may be wrong. If the formula bar shows only a value, the formula may have been replaced. If the formula appears literally in the cell, it may be stored as text or Excel may be showing formulas across the sheet.

Try these checks in order, changing one thing at a time so you can identify the cause:

  • Confirm the formula begins with an equals sign and that the referenced cells contain the values you expect.
  • Check whether the cell format is Text and whether an apostrophe appears before the equals sign.
  • Choose Formulas > Calculation Options > Automatic, then recalculate.
  • Look for an error such as #VALUE!, #REF!, or #NAME? and use its meaning to guide the next check.
  • Check for hidden spaces, numbers stored as text, or an unintended relative reference.

Before making broad changes, save a copy of the workbook if it contains important work. A formula repair can affect many linked cells, especially when you fill a corrected formula down a column.

How Excel evaluates a formula

A formula tells Excel to calculate a result from values, cell references, operators, and functions. For example, with 12 in A1 and 8 in B1, the formula in C1 can add those cells. The cell layout assumed here is A1 = 12, B1 = 8, and C1 contains the formula: =A1+B1. The result is 20. If A1 changes to 15, Excel should update C1 to 23 when calculation is automatic.

Functions follow the same principle but apply a named operation to inputs. With 10, 15, and 20 in B2:B4, the formula in B5 can total the range. The cell layout assumed here is B2:B4 = 10, 15, and 20, and B5 contains the formula: =SUM(B2:B4). The result is 45. A correct formula can still produce a surprising result if one input is blank, stored as text, or outside the referenced range.

Excel also tracks dependencies. If C1 refers to A1 and B1, it knows those cells affect C1; changing an input should trigger a recalculation. Workbooks with many formulas, external links, or large data sets may take longer to update, but a delay is different from a formula error. Understanding this distinction helps determine whether to inspect the formula itself or Excel’s calculation settings.

When a formula appears instead of its result

If a cell displays something like =A1+B1 rather than the answer, check whether the cell is formatted as Text. Select the cell, choose Home > Number Format > General, then edit the cell and confirm the formula again. One straightforward method is to press F2 and then Enter on Windows. On a Mac, use Control+U to edit the active cell, then Return; keyboard behavior can vary with the keyboard layout. Changing the format alone does not always make an already-entered text string calculate, which is why re-entering it matters.

Also check for an apostrophe before the equals sign. Excel uses a leading apostrophe to store an entry as text, and the apostrophe may not be obvious in the cell display. Remove it and confirm the formula. If formulas are showing throughout the worksheet, rather than just one cell, toggle Show Formulas from the Formulas tab. The Windows shortcut is Ctrl+grave accent; on Mac, Control+grave accent is commonly used, though the key position varies by keyboard. Turning off Show Formulas restores the results view; it does not change the formulas.

Fix calculation that is not updating

When an input changes but the result stays the same, check the calculation mode. In Excel for Microsoft 365 on Windows, select Formulas > Calculation Options > Automatic. Then select Formulas > Calculate Now to recalculate the workbook, or Formulas > Calculate Sheet to recalculate the active sheet. The F9 key also recalculates open workbooks on Windows. On a Mac, calculation commands are available on the Formulas tab, and the keyboard may require Fn with a function key depending on system settings.

Manual calculation can be useful in a large workbook because it avoids recalculating after every edit, but it can make results look stale. Calculation mode is an application-level setting and can be influenced by other workbooks open in the same Excel session. If a workbook behaves differently after you open another file, check the mode again. Automatic calculation does not fix a bad formula; it only ensures Excel updates calculations when inputs change.

If only one formula is out of date, inspect its dependencies too. A formula may refer to a different worksheet, a fixed range that excludes newly added rows, or an old input cell. Recalculate first, then verify the referenced addresses in the formula bar. This avoids mistaking a valid result based on the wrong cells for a calculation failure.

Understand common formula errors

Error values are clues. They do not all mean the same thing, and replacing every error with a blank can hide a problem that should be corrected.

  • #DIV/0! means a formula divides by zero or by a blank cell. Check the denominator and decide how zero should be handled.
  • #VALUE! often means an operation received an unsuitable type of value, such as text where a number is expected. Check the inputs and any spaces or symbols in imported data.
  • #REF! signals an invalid reference, often because a referenced row, column, or worksheet was deleted or moved in a way that broke the formula.
  • #NAME? can mean Excel does not recognize a function or name. Check spelling, defined names, and whether the function is available in your Excel version.
  • #N/A often means a lookup did not find a match. Check the lookup value, the lookup range, and whether the values differ in type or contain extra spaces.
  • #SPILL! can occur when a dynamic-array formula needs to place results into cells that are not empty. Clear the blocking cells or move the formula.

Some functions and features depend on the Excel version. 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 colleague using an older version sees an unrecognized function, compatibility may be the issue rather than a spelling mistake.

Check references and copied formulas

Copying a formula usually adjusts relative references. If a formula in C2 refers to A2 and B2, filling it down changes those references to A3 and B3, which is normally what you want for row-by-row calculations. But a reference to a fixed rate or assumption may need dollar signs so it remains in the same cell when copied. A copied formula can therefore be syntactically valid and still use the wrong inputs.

For example, with quantities in A2:A4 and prices in B2:B4, the formula in C2 calculates that row’s total. The cell layout assumed here is A2 = 3 units, B2 = 2 per unit, and C2 contains the formula: =A2*B2. The result is 6. If you fill the formula down, the next row should use A3 and B3. Inspect the first and last filled formulas to confirm the references move as intended.

When one reference should stay fixed, add dollar signs to its column and row address. For instance, a tax rate in F1 can be referenced as $F$1 when filling formulas down or across. Mixed references such as $F1 or F$1 lock only one part. In Excel, selecting a reference while editing and pressing F4 cycles through reference styles on Windows; on Mac, the equivalent may be Fn+F4 depending on keyboard settings. Check the formula bar after using the shortcut to verify the reference changed as intended.

Look for numbers stored as text and hidden characters

Imported data can look numeric while being stored as text. This can interfere with arithmetic, sorting, comparisons, and lookups. Warning indicators may offer Convert to Number, or you can inspect a sample value and its alignment, but alignment alone is not conclusive because formatting can change how numbers appear. Check the source and test a small set of values before converting a whole column.

Leading or trailing spaces, nonbreaking spaces, currency symbols, and inconsistent decimal or thousands separators can also cause mismatches. A pasted value that looks like 1,250 may not be interpreted the same way in every regional setting. Excel’s argument separator may also vary by system locale: many installations use commas, while others expect semicolons. If a formula using commas is rejected at entry, check your regional list-separator settings rather than randomly changing the formula’s logic.

Dates are another common source of confusion. Excel stores recognized dates as serial numbers and displays them using a date format. A text string that resembles a date may not behave like a real date in subtraction or sorting. Check the cell format and the original imported data, and avoid converting values blindly when the date order could be ambiguous. For a lookup that fails despite matching-looking values, compare both cells’ underlying types and remove unwanted spaces before changing the lookup formula.

Check formula syntax and workbook structure

Excel formulas need a valid function name, balanced parentheses, valid references, and the argument separator expected by the installation. If a formula is rejected immediately, check for a missing equals sign, an unmatched parenthesis, a misspelled function, or a separator mismatch. If a formula is accepted but returns an error, inspect the inputs and references instead. These are different failure stages and call for different fixes.

Also check whether the workbook contains circular references: a formula that depends on its own result, directly or through a chain of other formulas. Excel may warn about a circular reference, and the status bar or error-checking tools can help locate one. Some specialized models intentionally use iterative calculation, but enabling it is not a general repair. On Windows, the setting is under File > Options > Formulas > Enable iterative calculation. On Mac, look under Excel > Settings for the calculation options. Use iteration only when the workbook design calls for it, with suitable iteration limits, because a circular model can otherwise produce confusing or unstable results.

For workbooks with linked files, a formula may rely on an external workbook that is closed, moved, or unavailable. Check the file path and the workbook’s links rather than replacing a valid formula with a guessed value. In shared workbooks, verify that the relevant cells were not overwritten by pasted values. If the workbook contains macros or complex links, preserve an untouched copy before investigating structural changes.

A safe order for fixing formulas

A methodical sequence reduces the risk of making a workbook less reliable. Start with one affected cell and confirm its inputs, formula text, and number format. Then check calculation mode and the exact error or displayed value. Make one correction, recalculate, and compare the result with a simple hand calculation using the same inputs. If the formula is copied across a table, test several rows, including the first and last, before filling a change across the entire range.

Keep a record of what changed if the workbook is important or shared. Do not remove error values merely to make a report look clean until you know why they occur. If the formula depends on an external source, confirm the source is current. If a repair changes many results, use Undo immediately if appropriate, or restore your saved copy and retry on a smaller sample. Excel’s displayed answer is only as trustworthy as its inputs, references, and calculation settings.

Frequently Asked Questions

Why is Excel showing the formula instead of the answer?
Check whether the cell is formatted as Text, has a leading apostrophe, or whether Show Formulas is enabled. Change the format to General and re-enter the formula if it was stored as text.

Why does my Excel formula not update when I change a value?
Check Formulas > Calculation Options > Automatic. If it is set to Manual, switch to Automatic and recalculate. Then confirm the formula actually refers to the cell you changed.

Why does Excel say there is a problem with this formula?
Check that the formula starts with an equals sign, parentheses are balanced, names are spelled correctly, and the argument separator matches your regional settings. A function unavailable in your Excel version can also be rejected or displayed as an error.

Why does a lookup return #N/A when the values look the same?
The values may differ because one is stored as text, contain hidden spaces, or have different underlying characters. Compare the source and lookup cells, check the lookup range, and clean or standardize the data before revising the formula.

Can I make Excel ignore formula errors?
You can design a formula to handle expected cases, but hiding every error may conceal broken references or bad input. Identify the cause first, then decide how the workbook should represent a genuinely missing or unavailable result.

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.