If Excel will not accept or calculate a formula, check that the entry begins with an equals sign, the cell is not formatted as Text, and calculation is set to Automatic. Correct the issue, then re-enter the formula; the right fix depends on whether Excel rejects the entry, displays it literally, or returns an error.
First identify what “not working” means
Excel formula problems tend to fall into a few distinct categories. Separating them saves time: a formula that is displayed as text needs a different fix from one that calculates but returns an unexpected value.
- The formula appears in the cell instead of a result: check the cell format, a leading apostrophe or space, and whether Show Formulas is turned on.
- Excel rejects the entry or shows a warning: look for a missing equals sign, unmatched parentheses, an incorrect argument separator, or a misspelled function name.
- The formula is visible in the formula bar but its result does not change: check calculation mode and whether referenced values are stored as text.
- The cell shows an error such as #VALUE! or #NAME?: the formula has been evaluated, but Excel cannot complete it as written. Read the error and inspect the relevant inputs or function.
Click the cell and inspect the formula bar. If it contains a formula but the worksheet cell shows the same characters, the issue is often display or formatting. If the bar contains only a value, the formula may have been replaced or never entered. If the bar shows a formula and the cell shows an error, troubleshoot the formula’s logic and references instead.
Check the basic formula-entry rules
A formula normally starts with an equals sign. For example, with the numbers 12, 8, and 5 in cells A1 through A3, enter =SUM(A1:A3) in another cell; the result is 25. Excel also evaluates simple calculations, such as =2+3, as formulas. If the initial equals sign is missing, Excel generally treats the entry as ordinary text.
When a formula is rejected, check its punctuation and structure. Parentheses must be paired, text values in a formula need straight double quotation marks, and function arguments are separated by the list separator expected by your regional settings. Microsoft 365 for Windows commonly uses commas, but some regional configurations use semicolons. If a formula copied from a website is rejected, compare its separators with a formula that works in your Excel installation rather than assuming the function itself is unavailable.
Also make sure the formula is in the cell you intended. A cell may be selected but not active in the way you expect if you are editing another cell or the formula bar. Press Escape to cancel an accidental partial edit, select the destination cell, type the formula, and press Enter. In a cell that already contains a formula, F2 starts editing; on some Mac keyboards, you may need to hold Fn while pressing F2.
When Excel displays the formula instead of its answer
The most common cause is Text formatting. A cell formatted as Text stores what you type as a text string, so Excel may display the formula characters without evaluating them. Select the affected cells, choose Home > Number Format > General, and then re-enter each formula. Changing the format alone may not convert a formula that Excel has already stored as text: select the cell, press F2, and press Enter to make Excel interpret it again.
A leading apostrophe can also force an entry to be text. If the formula bar shows an apostrophe before the equals sign, remove the apostrophe and re-enter the formula. A space before the equals sign can have a similar effect. Check the start of the entry carefully; a blank-looking character can be easy to miss when reading a narrow formula bar.
Another possibility is that Show Formulas is enabled. This setting displays formulas in worksheet cells rather than their calculated results. On Windows, press Ctrl+` (the grave-accent key, usually near the top-left of the keyboard) to toggle it, or choose Formulas > Show Formulas. The same ribbon command is available in Excel for Mac; keyboard layouts vary, so the ribbon is the dependable option if the shortcut is unclear. If only one cell shows a formula, formatting or a leading character is more likely than this worksheet-wide display setting.
Make sure Excel is recalculating
If a formula is correct but its displayed result stays old after you change an input, the workbook may be using manual calculation. In Excel for Microsoft 365 on Windows, choose Formulas > Calculation Options > Automatic. Automatic calculation is the normal setting for most workbooks. In Excel for Mac, use the Formulas tab and its calculation options; the exact layout can vary slightly by release. Excel for the web generally recalculates formulas automatically, but a workbook’s state and formula dependencies can still affect what you see.
To request a recalculation, press F9 on Windows. On a Mac, the function-key setting may require Fn+F9. Recalculation can help confirm that calculation mode is involved, but it does not repair a broken formula or turn text into a number. If the result remains wrong, inspect the referenced cells and calculation setting rather than repeatedly pressing the shortcut.
Manual calculation can be useful in a large workbook that takes a long time to update, but it can make results look stale if you forget to recalculate. If you change the setting, consider whether other workbooks or users rely on the existing mode. After switching to Automatic, check important totals and results to ensure they have refreshed.
Read formula errors instead of guessing
An error value is evidence that Excel recognized the entry as a formula and attempted to calculate it. The message narrows the search, although it does not always identify the precise cause. Click the cell and examine the formula bar, then check the cells and ranges it references.
- #NAME? often means Excel does not recognize a function name or a text name used in the formula. Check spelling and whether text is enclosed in quotation marks. An unsupported function in an older Excel version can also be the cause.
- #VALUE! commonly indicates an argument or referenced value has the wrong type, such as text where a calculation expects a number. Check for numbers imported or pasted as text.
- #DIV/0! means a calculation attempted to divide by zero or by a blank cell that is being used as the divisor. Confirm that the denominator contains the intended nonzero value.
- #REF! indicates an invalid cell reference, often because a referenced row, column, or cell was deleted.
- #N/A often appears when a lookup cannot find a match. Check the lookup value and the data for extra spaces, different data types, or a genuinely missing item.
For example, with a payment status in A2, enter =IF(A2>0,"Paid","Due") in B2. If A2 contains 0, the result is Due; if it contains 1, the result is Paid. The example assumes A2 contains a numeric value. If A2 contains the text “1” rather than the number 1, other formulas may behave differently, so confirm the underlying data type before changing the formula.
Check references, data types, and copied formulas
A formula can calculate successfully and still return the wrong answer because it points to the wrong cells. When you copy a formula, Excel normally adjusts relative references. A reference such as A2 can become A3 when copied down. If a reference must remain fixed, add dollar signs to lock its column, row, or both; for instance, $A$1 stays fixed when copied. Press F4 while editing a reference on Windows to cycle through reference styles. On a Mac, the shortcut can depend on keyboard settings; typing the dollar signs directly is a reliable alternative.
Check the boundaries of ranges as well. A total that omits the newest row may be a range-selection issue rather than a calculation failure. Look at the highlighted references while editing the formula and confirm that the first and last cells match the data you intend to include. If the range contains blanks, text, or hidden rows, consider whether the function you chose handles those cells as you expect.
Imported data can look numeric while being stored as text. A number aligned differently from nearby numbers can be a clue, but formatting is not conclusive. Check for leading apostrophes, spaces, or import markers, and use Excel’s available conversion prompts where appropriate. Dates can also be affected by regional date conventions: a date interpreted as text will not necessarily sort or calculate like a true Excel date. Correcting the input data is often better than adding complexity to a formula to compensate for inconsistent entries.
When the function or formula is unsupported
A correctly entered function may fail in an older Excel release if that release does not support it. 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. Availability can also depend on the update channel and version of Microsoft 365.
If a workbook uses a function unavailable in the installed version, Excel may show #NAME? or display an _xlfn. prefix in the formula. Confirm the Excel version before rewriting the formula: a compatibility problem is not necessarily a typing mistake. If you need to share the workbook with people using older releases, choose a function available to those users or agree on a compatible workbook format. Excel for the web and desktop Excel do not always have identical feature availability, so check the specific environment where the workbook must run.
A practical troubleshooting order
Use this sequence to isolate the problem without changing unrelated formulas:
- Click the cell and inspect the formula bar. Decide whether the entry is text, a formula with an error, or a formula whose result is stale.
- For a displayed formula, check Show Formulas, Text formatting, an initial apostrophe, and leading spaces. Set the cell to General if needed, then edit and re-enter.
- For a rejected formula, confirm the equals sign, function spelling, parentheses, quotation marks, and the list separator used by your Excel settings.
- For an error value, read its type and inspect the referenced cells, data types, and ranges.
- For an unchanged result, check calculation mode and request recalculation. Verify that the workbook is not in Manual mode.
- If a function is unknown, confirm that your Excel version supports it and that the workbook is open in a compatible application.
Work on a copy if you are about to make broad changes, such as replacing formulas throughout a workbook or converting a large imported column. A single-cell test can help: enter a simple calculation in an empty cell. If that works while the original formula does not, Excel itself is accepting formulas and the problem is likely specific to the original cell, data, or formula.
Frequently Asked Questions
Why does Excel show the formula instead of the result?
Check whether Show Formulas is on. If other formulas display results normally, format the affected cell as General, remove any leading apostrophe or space, and edit the entry by pressing F2 and then Enter.
Why does my formula work only after I press Enter twice?
The cell may have been formatted as Text before the formula was entered. Change the format to General and re-enter the formula once. Also check for a leading apostrophe or space that is keeping the entry from being evaluated.
Why is Excel not updating a formula when I change a value?
Check Formulas > Calculation Options and select Automatic if the workbook is set to Manual. Then recalculate and confirm that the formula actually references the cell you changed.
Why does Excel say there is a problem with this formula?
Check the equals sign, parentheses, quotation marks, function spelling, and argument separator. Some regional settings expect semicolons rather than commas between arguments. A function from a newer Excel release may also be unavailable in an older version.
Why does my formula return #NAME?
Excel may not recognize a misspelled function or an unsupported function. Check the name and your Excel version, and make sure text in the formula is enclosed in straight double quotation marks.
Related
- Excel Data Validation List: How to Exclude Blanks in Excel
- Excel Data Validation List From Another Sheet: Steps
- Excel Data Validation List From a Table Column Explained
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- Excel Conditional Formatting: Apply Rules and Fix IssuesTo apply conditional formatting in Excel for Microsoft 365 on Windows, select the cells, then choose Home >…
- Excel Error Bars for Each Point: Set Custom ValuesTo give each point in an Excel chart a different error bar, select the chart, choose Chart Design…
- Why Is VLOOKUP Not Working in Excel?VLOOKUP usually fails because its lookup range, match setting, or data types do not match the data you…
- Uncertainty in Excel: Standard Error and ConfidenceTo estimate uncertainty from repeated measurements in Excel, calculate the mean with =AVERAGE(A2:A6), the sample standard deviation with…
- Google Sheets Filter Search: Find Data and Fix IssuesTo search data with a filter in Google Sheets, select your data range, choose Data > Create a…
- Remove a Circular Reference in Excel: Find and Fix ItTo remove a circular reference in Excel for Microsoft 365 on Windows, go to Formulas > Error Checking…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.