Copied Formula Not Working in Excel? Fix It

Errors & fixes · Updated November 19, 2026

If a copied formula is not working in Excel, first check whether the reference changed as intended, the cell is formatted as Text, or calculation is set to Manual. Select the cell and inspect the formula bar; then use the checks below to identify whether the problem is the formula, its result, or the way it was copied.

Start with a quick diagnosis

“Not working” can mean several different things: the cell shows the formula itself, the result is wrong, the result has not updated, or Excel displays an error. Those symptoms point to different causes, so note exactly what appears in the cell before changing anything.

Select the affected cell and look at the formula bar above the worksheet. If the cell shows a calculated value but the formula bar shows a formula beginning with an equals sign, Excel recognizes it as a formula. If both show the formula as ordinary text, check the cell format and any leading apostrophe. If the formula bar shows a different reference from the original cell, copying may have adjusted a relative reference.

  • Formula displayed in the cell: check Text formatting and Show Formulas.
  • Old result still displayed: check calculation mode and recalculate.
  • Wrong number or blank: inspect cell references, input values, and any conditions in the formula.
  • Error such as #REF!: identify the broken or invalid reference before replacing the formula.

If the workbook contains important data, save a copy before making broad changes, especially before replacing formulas across a large range.

Understand how copied references change

Excel normally uses relative references. When you copy a formula to another cell, its references shift by the same number of rows or columns. This is useful when each row should calculate from that row’s data, but it can produce a surprising result when a reference was meant to stay fixed.

For example, with a price in B2 and a quantity in C2, the formula in D2 is:

With the price in B2 and quantity in C2, =B2*C2 returns 72 when B2 is 18 and C2 is 4. Copied one row down to D3, Excel adjusts it to use B3 and C3; if those cells contain 12 and 3, the result is 36.

To keep a row or column fixed, use dollar signs to create an absolute or mixed reference. Pressing F4 while editing a reference cycles through reference styles in Windows Excel; on a Mac, the shortcut can depend on the keyboard and function-key settings, so adding the dollar signs directly is a reliable alternative.

With a price in B2 and a fixed multiplier in F1, =B2*$F$1 returns 54 when B2 is 50 and F1 is 1.08. Copied down one row, it changes B2 to B3 but keeps F1 fixed. In contrast, $B2 fixes the column but allows the row to change, while B$2 fixes the row but allows the column to change.

Compare the original and copied formulas in the formula bar. If the references shifted incorrectly, edit the first formula to use the needed dollar signs, then copy it again. Do not make every reference absolute automatically: doing so can cause every copied row to use the same input cells.

Check whether the cell contains text

A formula stored as text will not calculate. This often happens when a cell or column was formatted as Text before the formula was entered, or when an apostrophe was typed before the equals sign. The apostrophe may not appear in the cell itself, but it can be visible in the formula bar.

To fix one cell, select it, choose Home > Number Format > General, then enter the formula again. Changing the format alone may not convert an existing text entry: press F2 and then Enter on Windows to re-enter it. On a Mac, use the equivalent edit-and-confirm keys for your keyboard setup, or click in the formula bar and confirm the entry. For a range, change the format first, then re-enter or convert the affected entries rather than assuming the format change recalculates them.

Also check for a leading space or apostrophe before the equals sign. A genuine formula begins with = as its first character. If you see the formula text in many cells at once, Show Formulas may be on rather than the cells being stored as text. Toggle it from Formulas > Show Formulas, or use Ctrl+` on Windows. On some Mac keyboards the backtick shortcut differs, so use the ribbon command.

Make sure Excel is recalculating

Excel may be set to Manual calculation. In that mode, changing an input does not necessarily update dependent formulas immediately. Open Formulas > Calculation Options and select Automatic. Calculation settings can affect other open workbooks too, so check the setting when results seem stale across more than one file.

After switching to Automatic, use Formulas > Calculate Now to recalculate the workbook. F9 recalculates formulas in open workbooks on Windows; the Mac equivalent is typically Fn+F9, depending on the keyboard’s function-key settings. If only one formula is stale, select its cell and re-enter the formula, but do not treat that as a substitute for correcting Manual calculation when the whole workbook is affected.

Calculation mode is not the same as a formula being wrong. A correct formula can show an old answer if Excel has not recalculated it. Conversely, recalculating will not repair a broken reference or incorrect calculation logic.

Read the error before changing the formula

An error message is a clue, not a reason to replace the formula blindly. Follow the references used by the copied formula and check the cells they now point to. Common errors include:

  • #REF! means a reference is invalid, often because a referenced row, column, or worksheet was deleted. Restore the correct reference or rebuild the formula.
  • #DIV/0! means the formula divides by zero or by a blank cell being treated as zero. Check the denominator and decide how a zero should be handled.
  • #VALUE! often points to an unexpected type of input, such as text where arithmetic requires a number. Check for numbers stored as text or stray characters.
  • #NAME? can indicate a misspelled function or an unrecognized name. Check spelling and whether a named range still exists.
  • #SPILL! can occur with dynamic-array formulas when cells in the intended output area are occupied. Clear or move the obstructing content, while preserving anything important.

For a concrete check, with values in A2:A6 of 12, 8, 15, 5, and 10, =SUM(A2:A6) returns 50. If the copied formula instead includes an unintended blank or stops at A5, inspect its range rather than assuming SUM itself has failed. A formula can also return an apparently wrong answer because the referenced inputs are wrong or because the result cell’s number format hides decimals.

Choose the right way to copy or fill

Copying and pasting a formula normally copies its relative-reference behavior, and may also copy formatting. If you want only the calculation without the source cell’s appearance, use Paste Special > Formulas from Home > Paste > Paste Special. If you want the displayed result rather than a live formula, choose Values instead; that result will not update when the source data changes.

For a formula intended to repeat down adjacent rows, enter it in the first row and drag the fill handle, the small square at the cell’s lower-right corner. You can also select the formula cell and destination cells, then use Home > Fill > Down. Windows shortcut Ctrl+D fills down; on Mac, Command+D is commonly used. Copy and paste use Ctrl+C and Ctrl+V on Windows, and Command+C and Command+V on Mac.

When copying sideways, confirm that column references should move. When pasting into a filtered or partially selected range, verify which cells received the formula; a normal paste may not match the visible rows in the way you expect. Merged cells can also interfere with selecting and filling a range. Unmerge only if doing so is appropriate for the worksheet, since it changes the layout.

Look for worksheet and workbook complications

If Excel will not let you edit or paste into the destination, the worksheet may be protected. Check Review > Unprotect Sheet; a password may be required, and protection should only be removed if you are authorized to do so. A protected worksheet can still allow editing in unlocked cells, so try an allowed destination if the workbook is designed that way.

In an Excel Table, a formula entered in one cell of a calculated column may automatically fill the other rows. If a copied formula appears to change across the whole column, check whether automatic calculated-column filling is involved and whether the column is meant to use one consistent formula. Structured references, such as references to table columns, can be easier to maintain than ordinary cell addresses, but they may look unfamiliar when you inspect them.

Formulas that refer to another workbook can also behave differently if the source file was moved, renamed, or is unavailable. Check the source workbook and the link in the formula bar before editing a long external reference. If the workbook asks whether to update links, confirm that the source is trusted and current; choosing not to update can leave displayed values unchanged.

Check version and platform differences

Core references, cell formats, calculation settings, and ordinary copy-and-paste work similarly in Microsoft 365 Excel for Windows, Mac, and the web, but ribbon placement and keyboard shortcuts can differ. On Windows, use the named ribbon paths in this guide. Excel for Mac has comparable Formula and Home ribbon commands, although some shortcuts depend on macOS keyboard settings. In Excel for the web, use the ribbon commands available in the browser; desktop-only keyboard behavior may not apply.

If a copied formula uses a newer function, compatibility may be the issue rather than copying itself. XLOOKUP, FILTER, SORT, UNIQUE, SEQUENCE, and LET require Microsoft 365 or Excel 2021 or later. A workbook opened in an older Excel version may show an unsupported-function error. Check the version used by the person opening the file before replacing a modern formula with an older alternative.

Frequently Asked Questions

Why does Excel show the formula instead of the answer?
Check whether the cell is formatted as Text or has a leading apostrophe, and confirm the entry begins with =. If many formulas display at once, turn off Formulas > Show Formulas.

Why does my copied formula change cell references?
Excel adjusts relative references when you copy a formula. Add dollar signs to the row, column, or entire reference that must stay fixed, then copy the corrected formula.

Why does the formula show the old result after I copy it?
Check Formulas > Calculation Options and select Automatic if appropriate. Then recalculate; also verify that the formula points to the intended input cells.

How do I copy a formula without changing its references?
Make its references absolute with dollar signs, such as $A$1, before copying. Alternatively, copy the formula text and edit the destination reference deliberately; do not assume ordinary copy-and-paste freezes references.

Why does a formula work in one row but not another?
Compare the formulas and inputs in both rows. A relative reference may have shifted to a blank or incorrect cell, or the second row may contain text, a zero denominator, or another value that triggers an error.

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.