Excel Formula Not Working? Find and Fix Common Errors

If an Excel formula is not working, check that the cell contains a formula rather than text, confirm calculation is set to Automatic, and inspect the formula’s references and error message. In Microsoft Excel for Microsoft 365 on Windows, these quick checks resolve many problems; the error or unexpected result usually points to the next step.

Start with the visible result

Before changing a formula, identify what “not working” means. Excel may display the formula itself, show an error such as #VALUE!, return a result that seems wrong, or leave an old result unchanged. These symptoms have different causes, so note exactly what appears in the cell and, if possible, select it to inspect the formula bar.

For a formula that appears to be stuck, press F2 and then Enter to make Excel re-enter it. This can prompt a cell to recalculate, but it does not repair an incorrect formula. If the formula bar shows a leading apostrophe or the entry begins with an apostrophe before the equals sign, Excel is treating the entry as text.

Formula displayed in the cell: check Show Formulas and the cell’s number format.
Error displayed: read the error and check the referenced values or ranges.
Unexpected number or blank: verify the formula logic, cell references, and input data.
Old result: check calculation mode and whether the formula depends on external data.

Check whether Excel is showing formulas

When Show Formulas is enabled, Excel displays formulas in worksheet cells instead of their calculated results. The formulas have not necessarily stopped working; the display has changed. On Windows, press Ctrl+` (the grave-accent key, usually near the number 1 key) to toggle this view. You can also select Formulas > Show Formulas on the ribbon. The same command turns the view off.

If only one cell shows something like =SUM(A1:A5) instead of a total, the cell may be formatted as Text. Select the cell, choose Home > Number Format and set it to General, then press F2 and Enter. Changing the format alone may not convert an existing text entry into a formula, which is why re-entering it matters. Also look for a leading apostrophe, which explicitly tells Excel to store the entry as text.

On a Mac, use Formulas > Show Formulas if the keyboard shortcut differs on your keyboard or the grave-accent key is difficult to reach. In Excel for the web, the ribbon command is also available; keyboard shortcuts can vary with the browser and operating system.

Make sure calculation is set to Automatic

Excel can be set to calculate formulas automatically or only when asked. If a formula’s inputs change but its result does not, select Formulas > Calculation Options > Automatic. In Manual mode, Excel may retain previously calculated results until you trigger a calculation. This setting can affect other open workbooks, so check it before assuming a particular formula is broken.

To request a recalculation on Windows, press F9 for open workbooks or Shift+F9 for the active worksheet. If a laptop uses function keys for hardware controls, you may need to hold Fn as well. On Mac, use Formulas > Calculate Now or the keyboard command available for that Mac and keyboard setup. These commands recalculate; they do not fix an invalid reference or a formula stored as text.

Manual calculation is sometimes intentional in large workbooks, where recalculating after every edit can take time. If you switch back to Automatic, allow Excel to finish calculating before judging the result. A workbook can also contain formulas whose results depend on data connections or external workbooks, which may need a separate refresh or access to the source.

Check the formula’s syntax and separators

Excel formulas normally begin with an equals sign. Check for a missing equals sign, unmatched parentheses, misspelled function names, or a range written incorrectly. For instance, a sum over cells A1 through A5 uses a colon between the endpoints. Excel’s Formula AutoComplete can help identify function names, while the Insert Function tool and on-screen argument prompts can help you check what a function expects.

With values in A1:A5, the formula to add that range is =SUM(A1:A5). If those cells contain 2, 4, 6, 8, and 10, the result is 30. A common mistake is using a comma where a range colon is needed, or leaving out a closing parenthesis when a formula nests functions.

Argument separators can vary with regional settings. Many installations use commas between function arguments, while some use semicolons. For example, the following comma-separated formula returns “Check” when A2 is blank and “Ready” otherwise: with the value being tested in A2, enter =IF(A2="","Check","Ready"). If Excel rejects the commas, use the separator expected by your installation. The formulas in this article use commas as requested, but the local settings on a reader’s computer may require semicolons.

Formula names are localized in some language versions of Excel, and separators are controlled by regional settings. When sharing a workbook, an unfamiliar formula appearance can therefore reflect a language or locale difference rather than a damaged file.

Understand common Excel error messages

Error values are clues, not generic warnings. Read the error in the cell, then follow the dependency chain: select the formula cell and inspect the references it uses. The Formulas tab includes tools such as Trace Precedents and Error Checking that can help identify related cells or likely issues.

#DIV/0!: a calculation divides by zero or by a blank cell. Check the denominator and decide what the result should be when it is zero.
#VALUE!: an argument has the wrong type, such as text where a calculation expects a number. Look for numbers stored as text, spaces, or incompatible inputs.
#REF!: a reference is invalid, often because a referenced row, column, or cell was deleted. Repair the reference or restore the missing structure.
#NAME?: Excel may not recognize a function or name. Check spelling, quotation marks around text, and whether a named range exists.
#N/A: a lookup or other calculation did not find the value it needs. Check spelling, extra spaces, lookup ranges, and whether the sought value is present.
#NUM!: a numeric input or result is invalid for the operation. Check the values and the calculation’s limits.

Some errors depend on the formula and the data, so avoid hiding them until you know what they mean. Wrapping every formula in an error-handling function can make a workbook look tidy while concealing missing inputs or broken references. Correct the underlying cause where possible, and use error handling only when a particular fallback result is appropriate.

Check references, data types, and copied formulas

A formula can be syntactically valid and still point to the wrong cells. Relative references change when you copy a formula; absolute references, marked with dollar signs, stay fixed. Mixed references lock either the row or the column. If copied formulas shift unexpectedly, select the cell and review the reference in the formula bar. In Windows Excel, F4 while editing a reference cycles through reference styles; Mac keyboard behavior can differ.

Suppose prices are in B2:B20 and quantities are in C2:C20. With the price and quantity for one item in B2 and C2, respectively, this formula calculates that item’s total: =B2*C2. Copied down one row, it becomes =B3*C3, as intended. If a tax rate is stored in F1 and should remain fixed as the formula is copied, the reference to that rate should be absolute, such as $F$1.

Numbers stored as text can also cause comparisons, totals, and lookups to behave unexpectedly. A cell may look like a number but remain text because it was imported from another system or entered with a leading apostrophe. Check the number format and the alignment or warning indicators, but do not rely on appearance alone. Converting data can change how it is interpreted, so keep a copy of important source data before applying bulk changes.

For lookup problems, check whether the lookup value and the matching values use the same data type. The number 123 and the text string “123” can look identical but fail to match in some lookup setups. Leading or trailing spaces can cause a similar mismatch. If a result is missing, inspect the actual source values and the selected lookup range before rewriting the formula.

Diagnose circular references and external links

A circular reference occurs when a formula depends on its own result, either directly or through a chain of other cells. For example, a formula in B2 that refers to B2 creates a direct circular reference. Excel may display a circular-reference warning or calculate an unexpected result. Use Formulas > Error Checking > Circular References to locate listed cells, then change the logic so the formula does not feed back into itself.

Excel can calculate some circular models using iterative calculation, but that option is intended for deliberately designed models, not as a quick fix for every warning. Iteration settings control calculation behavior and can affect results, so only enable them when the workbook’s design requires it and the calculation method is understood.

Formulas that refer to another workbook may show stale or unavailable results if the source file was moved, renamed, closed, or cannot be accessed. Check the link path and the workbook’s link update prompts. Do not enable links from an unfamiliar file without considering the source and whether the data should be trusted.

Use formula tools to find the exact problem

When a long formula is difficult to inspect, break the diagnosis into smaller checks rather than replacing it at once. Select the cell and use Formulas > Evaluate Formula to step through how Excel evaluates parts of the expression. You can also use Trace Precedents to see which cells feed into the result and Trace Dependents to see which formulas rely on the selected cell.

For a large worksheet, filter or sort the relevant source data and inspect a few known examples manually. Compare the formula result with a simple hand calculation using visible inputs. If some rows work and others do not, compare a working row with a failing row: look for blank cells, text values, shifted references, or inconsistent formula patterns. The error is often tied to a specific input rather than the whole workbook.

Before making broad changes such as replacing formulas or converting an entire column, save a separate copy of the workbook. This gives you a way to compare the original logic and recover data if a bulk edit changes more than intended.

Frequently Asked Questions

Why is Excel showing the formula instead of the answer?

Show Formulas may be enabled, or the cell may be formatted as Text. Toggle Formulas > Show Formulas off to check the first cause. If only one cell is affected, change its number format to General and re-enter the formula with F2, then Enter. Also check for a leading apostrophe.

Why does my Excel formula not update when I change a cell?

Check Formulas > Calculation Options and select Automatic if the workbook is in Manual mode. You can request a recalculation with F9 on Windows. If the result still does not change, confirm that the formula actually references the edited cell and that the input was entered as the expected data type.

Why does Excel say there is a problem with this formula?

Common causes include a missing equals sign, unmatched parentheses, an invalid function name, or an argument separator that does not match your regional settings. Check the formula syntax and try the separator Excel expects. Also confirm that text values in formulas are enclosed in straight quotation marks.

Why does my formula work in one row but not another?

Compare the working and failing rows. A copied reference may have shifted, a source cell may be blank or stored as text, or a lookup value may contain extra spaces. Select each formula and compare its references, then inspect the underlying inputs rather than relying only on how they look in the grid.

How do I fix a #REF! error in Excel?

#REF! means a formula contains a reference Excel cannot resolve, often because a referenced cell, row, or column was deleted. Use Undo if the deletion just happened and is still reversible, or edit the formula to point to the correct existing range. If the reference was in another workbook, check whether its source or path changed.

When the workbook still will not calculate

If the basic checks do not explain the problem, try the formula in a new blank cell using a small, known set of inputs. This separates a formula issue from a workbook-specific issue. Check whether the workbook opens in a different Excel installation, whether its source links are available, and whether an add-in or data connection is involved. Excel for the web and desktop Excel can differ in available features and connection behavior, so note which version is being used.

For a damaged or complex workbook, preserve the original before attempting repairs. Keep a copy, record the exact error, and note what changed immediately before the problem began. That information makes it easier to distinguish a broken formula from a calculation setting, a source-data issue, or a workbook link that is no longer available.

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.