If an Excel formula is not working, first check that the cell is formatted as General, the formula begins with an equals sign, and calculation is set to Automatic. Then look at what Excel shows: a formula displayed as text, an error such as #VALUE!, and a result that has not updated point to different causes.
Start by identifying what Excel is showing
The symptom is often the quickest clue. If you see the formula characters in the cell instead of an answer, Excel may be treating the entry as text or displaying formulas across the worksheet. If you see an error, the formula is being evaluated but something in its syntax, inputs, or references needs attention. If the answer appears valid but does not change after you edit a referenced cell, check calculation settings and dependencies.
Click the problem cell and inspect the formula bar above the grid. A formula bar that begins with an apostrophe or shows an unexpected leading space can explain why the entry is not calculating. Also check whether only one cell is affected or whether many formulas have stopped updating; a workbook-wide issue, such as manual calculation mode, can affect many cells at once.
- Formula visible in the cell: check cell formatting and Show Formulas.
- Error value: read the error and inspect the referenced cells.
- Old or unchanged result: check calculation mode and whether the formula refers to the cells you edited.
When a formula appears as text
Excel may store an entry as text when the cell was formatted as Text before the formula was typed, or when the entry starts with an apostrophe or a space. In that case, changing the number format alone may not convert an existing entry into a working formula. Select the cell, choose Home > Number Format > General, then press F2 and Enter to make Excel re-read the entry. On Mac, use the equivalent Home tab number-format control and edit the cell, then press Return.
Check that the first character is an equals sign, with no apostrophe or space before it. For example, with the values 12, 18, and 30 in A2:A4, enter =SUM(A2:A4) in A5; Excel should return 60. If you instead see the characters =SUM(A2:A4), check the cell format, the first character, and whether Show Formulas is on.
Show Formulas is a worksheet display setting, not a sign that every formula is broken. On Windows, press Ctrl+` (the grave-accent key, often near the number 1) to toggle it. You can also use Formulas > Show Formulas. On Mac, use Formulas > Show Formulas; the shortcut can vary with keyboard layout. If all formulas are visible, toggling the setting off should restore the calculated results.
Check calculation mode and refresh stale results
Excel normally recalculates formulas automatically when their inputs change. A workbook set to Manual calculation can keep showing old results until calculation is requested. In Excel for Microsoft 365 on Windows, select Formulas > Calculation Options > Automatic. If the workbook was opened after another file that uses Manual mode, check this setting rather than assuming the formula itself is wrong.
To recalculate, use Formulas > Calculate Now for the workbook or Formulas > Calculate Sheet for the active worksheet. The function key shortcuts can depend on the keyboard and laptop settings; the ribbon commands are a dependable alternative. On Mac, use the Formulas tab’s calculation controls when available, and check Excel’s calculation setting if results remain stale.
Some formulas intentionally change only when Excel recalculates, and data connections or linked workbooks may need separate refreshing. Recalculation does not automatically repair a broken external link or retrieve unavailable source data. If only a formula that refers to another workbook is out of date, confirm the source file is accessible and use the workbook’s link or data refresh controls as appropriate.
Inspect syntax, separators, and cell references
A formula must begin with = and use valid function names, operators, parentheses, and references. A missing closing parenthesis, an extra comma, a misspelled function, or text that needs quotation marks can cause an error. Excel often highlights part of the formula while you edit it; check the indicated section and use the function’s argument hints as a guide.
With a quantity of 3 in A2 and a unit price of 12 in B2, enter =A2*B2 in C2; the result should be 36. If the formula returns an unexpected result, check that A2 and B2 contain the intended values, not labels or numbers stored as text. Also verify that the formula points to the correct row: a reference to A3 instead of A2 can be syntactically valid but calculate the wrong thing.
Argument separators can differ by regional settings. Many Excel installations use commas between function arguments, while some use semicolons. If a formula copied from a website is rejected at the separator, check which separator your Excel expects and substitute it consistently. Do not confuse the separator between function arguments with a colon, which commonly marks a range such as A2:A4.
Text values inside formulas need straight double quotation marks, and text comparisons must match the data as intended. For example, a formula that tests for an empty cell is different from one that tests for a space character. Curly quotation marks copied from formatted documents can also be rejected; retype the quotes directly in Excel.
Look for reference and copying problems
When you copy a formula, Excel normally adjusts relative references to match the destination. If a formula is intended to keep pointing to one fixed cell, use dollar signs to make a reference absolute. A misplaced dollar sign, an incorrectly selected range, or a reference shifted one row during copying can make a formula calculate without producing the answer you expected.
For example, suppose A2:A4 contains 12, 18, and 30. If a total in A5 should include those three cells, the range must end at A4; ending at A3 leaves out 30 and returns 30 instead of 60. Inspect the colored reference outlines while editing a formula, or click a referenced cell in the formula bar to see which grid cell it points to.
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 placed inside the range it sums can include itself. Excel may show a circular-reference warning or calculate unexpectedly if iterative calculation is enabled. Move the result outside the input range or adjust the references so the formula no longer depends on its own result.
Understand common formula errors
Error values narrow down the investigation, but they do not always identify a single cause. Read the full error and check its inputs before replacing the formula. A formula that returns an error may be working as written while revealing that its source data is incomplete or the calculation is not valid for that row.
- #NAME? often means Excel does not recognize a function or name. Check spelling, named ranges, and whether text in the formula is enclosed in quotation marks.
- #VALUE! commonly indicates an unexpected data type, such as text where a calculation expects a number. Check for labels, stray spaces, or numbers stored as text.
- #REF! means a reference is invalid, often because a referenced row, column, or sheet was deleted or moved.
- #DIV/0! means a calculation divides by zero or by a blank cell being used as the denominator.
- #N/A often means a lookup did not find a match. Check the lookup value, spelling, spaces, and lookup range.
- ##### usually means the column is too narrow to display the result, or a date/time value cannot be shown as formatted. Widen the column before assuming the formula failed.
Do not hide an error immediately just to make the sheet look tidy. First decide whether the error signals a data problem that should be corrected. An error-handling formula can be appropriate for expected cases, but concealing an unexpected error may make a report misleading.
Check data types and function compatibility
Numbers that look like numbers may actually be text, especially after copying data from a website, importing a text file, or using a different decimal separator. A cell can appear numeric while behaving differently in arithmetic or comparisons. Check the alignment and number format, but remember that formatting changes how a value looks; it does not always convert the stored value. Re-entering the value or using Excel’s conversion tools may be necessary.
Dates can cause similar confusion because Excel stores dates as serial values and displays them according to the cell’s number format. A value that looks like a date might be text, or a real date might display as a number if the cell is formatted as General. Confirm the underlying value and choose an appropriate date format rather than rewriting a formula that is calculating correctly.
Newer functions may not be available 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 a function unsupported by the installed version, Excel may show an unrecognized-function error or a compatibility warning. Check the version on the computer opening the file before replacing a formula that works for another user.
A reliable troubleshooting sequence
Work from simple checks to more specific ones. This helps avoid changing a correct formula when the underlying cause is a display setting, a stale calculation, or a data issue. Before making broad edits to a shared or important workbook, save a copy so you can compare results or restore the original.
- Click the cell and inspect the formula bar. Confirm the entry starts with = and contains no leading apostrophe or space.
- Set the cell to General if it is Text, then edit and re-enter the formula.
- Turn off Show Formulas if formulas are displayed throughout the worksheet.
- Set Formulas > Calculation Options to Automatic and recalculate.
- Check the exact references, argument separators, quotation marks, and parentheses.
- Inspect referenced cells for text, blanks, unexpected spaces, errors, or values from the wrong row.
- Confirm that your Excel version supports any newer function used in the formula.
If the formula still fails, try a small calculation in an empty cell using known values. If that works, the problem is more likely specific to the original formula or its inputs. If even a simple formula is displayed as text or does not update, return to the cell format, Show Formulas, and calculation settings. This isolates workbook-wide behavior from a single formula’s logic.
Frequently Asked Questions
Why is Excel showing the formula instead of the result?
Most often, the cell is formatted as Text, the formula has a leading apostrophe, or Show Formulas is enabled. Check the formula bar, set the cell to General, re-enter the formula, and toggle Show Formulas off if the whole worksheet displays formulas.
Why does my Excel formula not update when I change a value?
Check Formulas > Calculation Options > Automatic. In Manual mode, results can remain unchanged until you recalculate. Also confirm that the formula actually refers to the cell you edited and that a linked workbook or data source is not out of date.
Why does Excel say there is a problem with this formula?
Check for a missing equals sign, unmatched parentheses, misspelled function names, curly quotation marks, and the wrong argument separator for your regional settings. If a formula uses commas between arguments, your Excel installation may instead require semicolons.
Why does a formula return #VALUE!?
The formula may be receiving text or another unexpected type where it expects a number or compatible value. Inspect the referenced cells for labels, spaces, or numbers stored as text, and verify that the calculation is appropriate for every row.
Why does a formula work on one computer but not another?
The computers may have different Excel versions, regional separators, calculation settings, or access to linked files. Check whether the formula uses a function that requires a newer version and whether the workbook’s source data and calculation options are available on both devices.
Related
- Contact Number List Template for Excel: Setup Guide
- Contacts List Template Excel: Build a Useful Workbook
- Contact List Excel Template: Free Options and Setup
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- Excel Error 0x80070194: OneDrive Fixes and CausesExcel error 0x80070194 usually means Windows could not reach the cloud-file provider for a workbook stored in OneDrive;…
- Excel Cannot Save or Export XML Data? Fix the ErrorIf Excel says it cannot save or export XML data, first check whether you are saving the workbook…
- Excel AVERAGEIF #DIV/0! Error: Causes, Checks, and FixesExcel’s AVERAGEIF returns #DIV/0! when it has no numeric values to average among the cells that meet the…
- MIN Not Working in Excel? Fix the Formula and DataIf MIN is not working in Excel, first check that the formula points to the cells you intend…
- Excel Cell Outline Not Showing? Fix the Selection BorderIf an Excel cell outline is not showing, first identify which outline is missing: gridlines, a formatted cell…
- Excel Formula Drag Down Not Working? Causes and Fixes in ExcelIf an Excel formula will not drag down, first check that the fill handle is enabled: in Excel…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.