Excel Formula Is Correct but Not Working? How to Fix It

Errors & fixes · Updated November 20, 2026

If a formula is correct but not working in Excel, first check whether the cell is formatted as Text, calculation is set to Automatic, and the formula is actually entered as a formula rather than displayed as text. Then inspect the result for an error, verify the cell references and separators, and recalculate the workbook if needed.

Start by identifying what “not working” means

A formula can appear not to work in several different ways: Excel may show the formula itself, show an old result, return an error, or calculate a result that is surprising. Those symptoms point to different causes, so note what is visible in the cell before changing anything. If the formula is visible, investigate text formatting or Show Formulas. If the result is stale, check calculation settings. If there is an error value, diagnose that specific error rather than rewriting the whole formula.

Click the cell and look at the formula bar above the worksheet. If the formula bar contains a formula but the cell displays a value, Excel has accepted the formula; the issue is likely its inputs, calculation, or displayed result. If the cell and formula bar both show something like =SUM(B2:B10) as plain text, Excel has not evaluated it. If the cell displays an error such as #REF!, the formula is being evaluated but contains a reference or calculation problem.

Check whether Excel is showing formulas instead of results

When many cells display their formulas, the Show Formulas setting may be on. On Windows, use Formulas > Show Formulas to turn it off. The keyboard shortcut is Ctrl+` (the grave accent, often on the key below Esc). On Mac, use Formulas > Show Formulas; the shortcut can vary with keyboard layout, so the ribbon command is the dependable option. This setting changes what the worksheet displays, not the formulas themselves.

Showing formulas can also make columns look crowded, so a displayed formula may be clipped or seem to run into the next cell. Widen the column if necessary. If only one cell displays formula text while neighboring cells show answers, Show Formulas is less likely to be the cause; check the cell’s number format and whether an apostrophe precedes the equals sign.

Fix formulas that are stored as text

A cell formatted as Text treats newly typed content as text, even when it starts with an equals sign. Select the affected cells, choose Home > Number Format drop-down > General, then re-enter each formula. A quick way to re-enter one is to select the cell, press F2, then press Enter. Changing the format alone may not convert a formula already stored as text; re-entering it prompts Excel to parse it.

Also look for a leading apostrophe or a space before the equals sign. An apostrophe is often used to force text entry, and Excel may not display it in the cell even though it is visible in the formula bar. Remove the apostrophe or extra leading space, then press Enter. For a small number of cells, F2 followed by Enter is straightforward. For many cells, correct the format first and use Find and Replace carefully, checking a copy of the sheet before making a bulk change.

With quantities in B2:B10, for example, a formula intended to total them is =SUM(B2:B10). If that text is sitting in a General-formatted cell but is still not calculated, re-enter it and check for a leading apostrophe, space, or an equals sign that was replaced by a similar-looking character during copying.

Make sure calculation is set to Automatic

Excel can be set to Manual calculation, often to speed up large workbooks. In that mode, edits may not update dependent formulas until you request a calculation. On Windows, choose Formulas > Calculation Options > Automatic. You can also check File > Options > Formulas and select Automatic under Workbook Calculation. On Mac, use Formulas > Calculation Options > Automatic or open Excel > Settings > Calculation; the wording can differ slightly by release.

After switching to Automatic, give the workbook time to recalculate, especially if it contains many formulas, external links, or complex array calculations. If you want to trigger a calculation without changing the setting, press F9 on Windows to calculate open workbooks. Shift+F9 calculates the active worksheet. On Mac, use Formulas > Calculate Now or Calculate Sheet if the keyboard shortcut differs on your keyboard. Calculation mode can affect other open workbooks too, so avoid changing it without considering any large workbooks you have open.

A stale answer is not necessarily a broken formula. For example, if a total depends on a quantity cell that has just been edited, Manual calculation can leave the displayed total at its previous value. Automatic calculation should update it as inputs change; in Manual mode, a recalculation command updates it.

Read the error message as a clue

An error value usually narrows the search. Common examples include:

  • #NAME? often means Excel does not recognize a function name or text name in the formula. Check spelling, named ranges, and whether text in the formula is enclosed in straight quotation marks.
  • #REF! means a reference is invalid, commonly because a referenced row, column, or cell was deleted. Restore the reference or edit the formula to point to the intended cells.
  • #DIV/0! means a calculation divides by zero or by a blank cell treated as zero. Check the denominator and decide how a zero value should be handled.
  • #VALUE! often indicates an argument has the wrong type, such as text where a number is expected. Check for numbers stored as text, unexpected spaces, or incompatible date values.
  • #SPILL! can occur when a dynamic-array formula needs to return results into neighboring cells but those cells are not empty. Clear the obstructing cells or move the formula to an open area.

Do not hide every error immediately with an error-handling function. First determine whether the error reveals bad input or a damaged reference. Hiding an error can make a workbook look tidy while concealing a calculation that needs attention.

Verify references, inputs, and copied formulas

A formula can be syntactically valid but point to the wrong cells. Check each referenced address in the formula bar and compare it with the intended inputs. When a formula is copied down or across, relative references adjust automatically. That is usually helpful, but it can shift a reference that should remain fixed.

With a charge in B2 and a rate in E1, the formula =B2*$E$1 uses the amount in B2 and locks the rate cell when copied. If B2 contains 100 and E1 contains 0.08, the result is 8. The dollar signs make E1 an absolute reference; without them, copying the formula down could shift the rate reference to E2, E3, and so on. Select a cell reference while editing and press F4 on Windows to cycle through reference types. On Mac, the shortcut may depend on keyboard settings; typing the dollar signs directly is an alternative.

Check the source data too. A value that looks like a number may be text, a date may be stored as text, or a cell that looks blank may contain spaces. Excel’s alignment is only a visual hint, not proof of the underlying data type. For dates, verify that the input is recognized as a date and that the result cell has an appropriate date or number format. A formula may calculate a valid serial number but display it in a format that is confusing.

Check separators, names, and function availability

Formula argument separators depend on regional settings. The examples here use commas, as in =IF(A2>0,B2/A2,0). In that example, with a value in A2 and another value in B2, Excel returns B2 divided by A2 when A2 is greater than zero, and otherwise returns zero. Some installations use semicolons as argument separators because of the computer’s regional settings. If a correctly written formula is rejected at entry, check the separator Excel expects; do not assume that changing commas to semicolons fixes a calculation error that appears after entry.

Function availability can also matter when a workbook moves between Excel versions. 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. An older edition may show an unrecognized-function error for a formula that works on a newer installation. If you share a file with people using older Excel, confirm their version or choose a compatible alternative.

Named ranges and table references are another source of apparent confusion. A name may have been deleted, misspelled, or defined with a different scope. In a table, structured references can adjust as rows are added, but they may look unfamiliar compared with ordinary A1-style addresses. Check the Name Manager or the table’s headers if the formula refers to a name rather than a visible cell coordinate.

Distinguish a wrong result from a display issue

Sometimes Excel has calculated correctly, but formatting makes the answer look wrong. A cell with a date format can display a number as a date, while a percentage format displays a decimal such as 0.25 as 25%. A narrow column may show #### for a date or number; widen the column or choose a suitable format. In many cases, #### is a display-width issue, not a formula error. Negative date or time calculations can also produce hashes under some date systems and formats.

Rounding can create another mismatch between what is shown and what is stored. A cell may display two decimal places while retaining more precision for later calculations. If the displayed values appear not to add up exactly to a displayed total, inspect the underlying values in the formula bar and review the number format. Do not use Excel’s setting to change precision to match displayed values unless you understand its workbook-wide effects; it can permanently discard precision from stored numbers.

For formulas returning unexpected results, test the inputs individually and confirm the intended logic. A formula can follow Excel’s rules exactly and still not match the question you meant to ask. For instance, check whether a comparison should include a boundary value, whether a range omits the last row, or whether a blank should count as zero. The formula being accepted proves its syntax is usable, not that its references and business logic are correct.

A practical troubleshooting order

Use this sequence to narrow the cause without making unnecessary edits:

  1. Read the cell and formula bar: is Excel showing a formula, an old value, an error, or an unexpected answer?
  2. If the formula itself appears, turn off Show Formulas and check for Text format, an apostrophe, or a leading space.
  3. If a previous answer remains, set calculation to Automatic or calculate the sheet.
  4. If there is an error, use its type to inspect function names, references, input types, denominators, or blocked spill ranges.
  5. If the answer is merely unexpected, trace the referenced cells, check copied references and source data, and confirm number formatting.
  6. If the formula is rejected when entered, verify parentheses, function spelling, quotation marks, regional separators, and Excel version.

For a workbook received from someone else, make a copy before broad changes. Avoid replacing every error or converting an entire column until you know what the data represents. A focused check of one affected formula and its inputs is often enough to identify a pattern that can then be corrected safely across the sheet.

Frequently Asked Questions

Why does Excel show the formula instead of the answer?
Show Formulas may be enabled, or the cell may be formatted as Text or contain a leading apostrophe. Turn off Formulas > Show Formulas, set the cell to General, and re-enter the formula.

Why does my Excel formula not update when I change a cell?
Check Formulas > Calculation Options and select Automatic. If calculation is Manual, recalculate with F9 on Windows or use the Calculate commands on the Formulas tab.

Why does Excel say there is a problem with my formula?
Check for unmatched parentheses, misspelled function names, invalid references, and the argument separator expected by your regional settings. Some Excel versions also lack newer functions.

Why does a correct formula return zero?
Inspect the referenced cells and the formula’s conditions. Inputs may be blank, stored as text, or outside a condition’s range; the result cell’s number format may also hide a small value by rounding its display.

What does #### mean in an Excel cell?
For many numbers and dates, the column is too narrow to display the value. Widen the column or adjust the format. If the formula calculates a negative date or time, check whether that result can be displayed under the workbook’s date system.

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.