Excel Auto Formula Not Working? Causes and Fixes

Errors & fixes · Updated November 19, 2026

If an Excel formula is not calculating, select Formulas > Calculation Options > Automatic, then check that the cell is not formatted as Text. If you mean Excel is not filling a formula into nearby rows, use the fill handle or check the relevant AutoFill settings; calculation and formula filling are separate features.

First identify what is not happening

People use “auto formula” to describe several different Excel behaviors. A formula may be present but fail to update when its inputs change; Excel may show the formula characters instead of a result; or Excel may calculate a formula correctly in one row but not copy it into the next rows. The right fix depends on which symptom you see.

  • The result is stale: calculation may be set to Manual, or the formula may depend on a value Excel does not recognize as a number.
  • The cell displays something like =A1+B1: the cell may be formatted as Text, the formula may have a leading apostrophe, or Show Formulas may be on.
  • The formula works in one row but does not appear in later rows: use AutoFill, or investigate a table’s calculated-column behavior.
  • The formula returns an error: inspect the formula syntax, referenced cells, and any error message before changing calculation settings.

Start by clicking the cell and looking at the formula bar. If the formula bar shows a formula but the grid shows an old value, focus on calculation. If both show formula text, focus on cell contents and formatting. If no formula exists in the later row, focus on filling or copying it.

Check Excel’s calculation mode

Excel can calculate a workbook automatically or wait for a manual calculation request. In Microsoft 365 for Windows, choose Formulas > Calculation Options > Automatic. This setting is particularly worth checking if values stopped updating after someone worked with a large workbook: a workbook opened in Manual mode can leave formulas displaying results based on earlier inputs.

With scores in A2:A4 containing 10, 15, and 20, the formula in A5 would be =SUM(A2:A4). It returns 45 when those cells contain those numeric values. If changing A2 does not change A5, select Automatic and see whether the result refreshes. You can also request a recalculation with F9 on Windows. A full recalculation is available with Ctrl+Alt+F9; this can take time in a large workbook.

Calculation mode can affect other open workbooks in the same Excel session, so check it rather than assuming a single sheet is defective. On Mac, the Formulas tab also provides calculation options in current Excel versions; the precise placement can vary by release. Excel for the web calculates formulas automatically and does not offer the same desktop calculation controls, so a workbook that behaves differently there may need investigation in the desktop app.

When Excel displays the formula instead of the answer

A frequent cause is that the cell was set to Text before the formula was entered. Excel then treats an entry beginning with an equals sign as text rather than evaluating it. Select the affected cells, choose Home > Number Format > General, and re-enter each formula. Changing the format alone may not convert an existing text entry into a working formula: press F2 and then Enter in a cell, or edit and confirm the formula again.

Also look for an apostrophe before the equals sign. An entry such as '=A1+B1 is deliberately stored as text; remove the apostrophe if it was not intended. A leading space can also prevent Excel from recognizing the entry as a formula. Another possibility is that Show Formulas is enabled, which displays formulas across the worksheet instead of their calculated results. Toggle it from Formulas > Show Formulas. On Windows, Ctrl+` (the grave-accent key, often shared with ~) toggles this view; on Mac, the keyboard shortcut can depend on the keyboard layout, so the ribbon command is the dependable option.

Check whether the formula bar begins with an equals sign and whether the cell’s format is General or an appropriate number format. Do not replace a working formula merely because the displayed result has an unexpected appearance: a number format may be hiding decimal places or showing a date or percentage instead.

When formulas do not fill into new rows

Excel does not always guess that you want to copy a formula just because you typed one in a cell. To fill a formula down, select the cell and drag its small fill handle—the square at the lower-right corner—through the destination rows. You can also copy the formula and paste it into the target range. Double-clicking the fill handle often fills down alongside a neighboring column of data, but it depends on the nearby data being continuous.

If the fill handle is missing, open File > Options > Advanced on Windows and make sure Enable fill handle and cell drag-and-drop is checked. Excel for Mac has corresponding fill-handle controls in its settings, though labels and locations can differ between releases. If the handle is visible but does not produce the expected results, inspect the destination cells for existing content, merged cells, or worksheet protection.

When a formula is copied, relative references normally adjust to the new row. For example, with a quantity of 12 in A2 and a unit price of 5 in B2, the formula in C2 would be =A2*B2, returning 60. Filling it down one row changes the references to A3 and B3. To keep a reference fixed when copying, use dollar signs: a reference such as $B$1 stays pointed at B1. This is useful for a tax rate or other constant stored in one cell, but make sure the fixed reference is intentional.

Use an Excel table for repeating row formulas

If you regularly add records to a list, converting the range to a table can make formula filling more predictable. Select the data and choose Insert > Table, confirming whether the range has headers. In a table, a formula entered into a calculated column is generally propagated to the other rows in that column, and new table rows can inherit the calculated-column formula. This is different from calculation mode: a table helps extend formulas, while calculation mode controls when Excel evaluates them.

For example, if a table has Quantity and Price columns, you can add a Total column and enter a row formula using structured references. Excel uses the table’s column names, which can make the formula easier to understand than cell addresses. Check the formula in a few rows after creating the column, especially if the table already contained inconsistent values or formulas. A table will not fix incorrect references, text-formatted values, or a formula that is logically wrong.

Some workbooks have automatic calculated-column formula propagation disabled or have formulas intentionally replaced by values in selected cells. If only part of the column fills, inspect those cells and the table boundaries. A range that looks like a table may simply be formatted cells rather than an actual Excel table.

Check formula syntax, references, and data types

Excel formulas need valid syntax. Check the opening equals sign, parentheses, function spelling, and cell references. A formula with a missing closing parenthesis or a reference to a deleted range may produce an error rather than a result. If you copied a formula from a website or another program, retype unusual punctuation: curly quotation marks and nonstandard characters are not interchangeable with Excel’s normal formula characters.

Arguments in many formulas are separated by commas in the standard English-language setup, but regional settings can use semicolons. If a formula copied from another computer is rejected at the separators, check the locale and the separators Excel expects rather than changing the calculation mode. Also check whether values that look numeric are actually text. Imported amounts can contain leading apostrophes, spaces, or currency symbols stored as characters. Text values may be ignored by some calculations or cause an operation to return an error.

Look at the referenced cells, not just the formula cell. A formula can be syntactically correct yet refer to the wrong row after copying, point to a blank cell, or include only part of the intended range. Clicking a cell reference in the formula bar highlights the referenced range, which helps verify that it covers the data you meant to include.

Errors, circular references, and external links

An error value is a clue rather than proof that automatic calculation is broken. #VALUE! often signals an incompatible value or argument; #REF! indicates an invalid reference; and #NAME? can mean Excel does not recognize text in the formula, such as a misspelled function name or an unquoted text value. Read the error and inspect the inputs before rebuilding the formula.

A circular reference occurs when a formula directly or indirectly refers back to its own cell. For example, adding a total in a cell that is also included in the total’s sum range can create a loop. Excel may warn about the circular reference, and the result may not be what you expect. Correct the range so it excludes the formula cell unless iterative calculation is deliberately required for a specialized model.

Formulas linked to another workbook can also show old or missing values if the source file is closed, moved, or unavailable. Check for an update-links prompt and confirm the source path before trusting a linked result. When a workbook is shared, ask whether collaborators changed formulas to fixed values or altered calculation settings; a visible number alone does not prove the cell still contains a formula.

A practical troubleshooting order

Work from simple checks to more disruptive changes. This makes it easier to find the cause and avoids rewriting formulas that are already correct.

  1. Click the cell and inspect the formula bar to confirm whether a formula exists.
  2. If the result is stale, set calculation to Automatic and recalculate.
  3. If formula text appears, check Text formatting, a leading apostrophe or space, and Show Formulas.
  4. If a formula is missing from later rows, use the fill handle or verify that the data is an actual table.
  5. If Excel reports an error, inspect syntax, references, data types, and any circular-reference warning.
  6. Test a corrected formula on a small sample, then confirm that copied rows and expected results are sensible.

Before applying a broad change such as replacing values or converting an imported column, save a copy of the workbook. That gives you a way back if the cells contain carefully prepared data or formulas that are not obvious at first glance.

Frequently Asked Questions

Why does my Excel formula show as text?
Common reasons are Text cell formatting, a leading apostrophe or space, or Show Formulas being on. Set the cell to General, re-enter the formula, and check the Show Formulas command on the Formulas tab.

Why is Excel not updating formulas when I change a value?
Check Formulas > Calculation Options > Automatic. If the setting is already Automatic, verify that the formula refers to the changed cell and that the input is a usable number or date rather than text.

How do I make Excel copy a formula down automatically?
Use the fill handle to copy the formula into the rows you need, or put the data in an Excel table and use a calculated column. A table can extend a formula as rows are added, but it does not correct an incorrect formula.

Why does the formula work in one row but not the next?
Compare the cell references in both rows. Relative references change as formulas are copied, while dollar signs lock references. Also check for overwritten cells, blank or text inputs, and whether the second row is actually inside the table or filled range.

Does Excel for the web have the same calculation settings?
Excel for the web normally calculates formulas automatically and does not expose the same desktop calculation controls. If a workbook’s behavior differs, open it in desktop Excel to inspect calculation mode and other workbook-level settings.

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.