If a formula you pasted into Excel is not calculating, first check Formulas > Calculation Options > Automatic, then check whether the cell is formatted as Text or Excel is displaying formulas instead of results. Set calculation to Automatic if needed; for a text-formatted formula, change the cell to General, press F2, then Enter.
Start by identifying what Excel is showing
The right fix depends on whether the pasted cell shows the formula itself, shows an old result, or displays an error. Look at the cell and the formula bar, and compare the result with what you expect. A formula that looks unchanged is not always failing: it may have calculated correctly using references that shifted during the paste.
- The cell displays something like =A2*B2: Excel may be showing formulas, or the entry may be stored as text.
- The cell displays a number that does not update: calculation may be set to Manual, or the formula may refer to different cells than expected.
- The cell displays an error such as #VALUE! or #REF!: Excel is calculating, but the formula, input data, or referenced range needs attention.
- The cell appears blank: the formula might return an empty string, refer to blank cells, or be hidden by formatting.
These distinctions help avoid changing a working formula unnecessarily. For example, a result of zero can be correct if the referenced input cells are empty, depending on the formula.
Check whether Excel is in Manual calculation mode
In Automatic calculation mode, Excel normally recalculates formulas when their inputs change. If calculation is set to Manual, a pasted formula can appear to stay blank or show an outdated result until you request a recalculation. This setting can affect other open workbooks too, so a workbook may behave differently after another file has been opened.
- Open the Formulas tab.
- Select Calculation Options.
- Choose Automatic.
- If the displayed result still seems stale, press F9 to recalculate open workbooks.
On Windows, Shift+F9 recalculates the active worksheet. Some laptops require the Fn key with function-key shortcuts, depending on their keyboard settings. On Mac, use the Formulas tab’s calculation commands if the keyboard shortcut is intercepted by macOS or the keyboard requires Fn. Excel for the web generally calculates automatically, but a workbook’s calculation behavior can depend on its saved settings and formulas.
Manual mode is useful for very large workbooks when recalculating after every edit would be slow. It is easy to forget it is enabled, however. Before relying on results for a report or decision, switch back to Automatic or deliberately recalculate and confirm the displayed values.
Fix formulas pasted into cells formatted as Text
If a cell is formatted as Text before a formula is entered, Excel may keep the entry as a text string rather than interpreting it as a formula. This can happen when a copied destination range was previously used for IDs or other text. A leading apostrophe can also tell Excel to treat an entry as text. In either case, changing the number format alone may not convert an existing text string into a working formula.
- Select the affected cell or range.
- On the Home tab, open the Number Format list and choose General.
- For one cell, press F2, then press Enter. This re-enters the contents so Excel can interpret them.
- Check that the cell now displays a calculated result, and repeat for other affected cells if necessary.
For example, with the quantity in A2 and the unit price in B2, enter =A2*B2 in C2. If A2 contains 12 and B2 contains 3, C2 should return 36. If C2 shows the characters =A2*B2, inspect its number format and whether the entry has a leading apostrophe. When entering a formula, make sure the first character is an equals sign, not a similar-looking character copied from formatted text.
Do not assume that every pasted formula is text just because it appears unchanged. The Show Formulas setting can produce the same visual clue while the cell still contains a valid formula.
Turn off Show Formulas if formulas appear across the sheet
When Show Formulas is enabled, Excel displays formulas in cells instead of their calculated results. This is a worksheet display setting, not evidence that every formula has been converted to text. A useful clue is that column widths may expand to show long formulas, and many formula cells may change appearance at once.
On Windows, press Ctrl+` (the grave accent, often on the key below Esc) to toggle Show Formulas. You can also use Formulas > Show Formulas. On Mac, use the Show Formulas command on the Formulas tab if the keyboard shortcut differs on your keyboard. Turning this view off should restore displayed results without changing the formulas themselves.
Confirm that pasted references point to the intended cells
Excel normally adjusts relative references when a formula is copied or pasted to another location. That behavior is expected, but it can make a formula appear wrong when the destination is not where you anticipated. A reference such as A2 can shift to A3 when the formula moves down one row. References with dollar signs, such as $A$2, are absolute and remain fixed when copied.
Suppose a formula in C2 calculates the product of A2 and B2. If copied one row down, Excel ordinarily adjusts the references to row 3. If you wanted every row to use a fixed tax rate in B1, that reference needs to be absolute; otherwise, copying down may make it refer to B2, B3, and so on. Click the cell and inspect the formula bar to see the references Excel actually used. Press F2 to edit the formula and use the arrow keys to trace its references; press Esc to leave edit mode without keeping changes.
Also check whether the pasted formula refers to another worksheet or workbook. A reference to a different sheet may be valid but return an unexpected value because the source sheet has different data. A link to another workbook may prompt for an update or rely on a source file that has moved. In those cases, the issue is the source or reference, not calculation itself.
Understand errors, blank results, and formulas that seem stuck
If Excel shows an error, it is usually calculating the formula and reporting a problem. Read the error before replacing the formula: #REF! generally indicates an invalid reference, often because referenced cells or sheets were deleted; #DIV/0! indicates division by zero or a blank divisor; and #VALUE! often means a formula received a value of an unexpected type. The specific cause depends on the formula and its inputs.
A formula can also return a result that looks blank. For instance, a formula may be designed to return an empty string when an input is missing, or a custom number format may hide a displayed value. Check the formula bar, its input cells, and the cell’s number format before deciding that calculation failed. If a formula references a cell that itself contains a formula, confirm that the upstream formula has a current result too.
Another common confusion is expecting a pasted formula to reproduce the value from its original location. Normal paste copies formulas and adjusts relative references; it does not necessarily preserve the original displayed result. If you need only the current result rather than a live calculation, use Home > Paste > Values or the Values option in Paste Special. That replaces formulas with their current values, so later changes to the inputs will not update those cells.
Use a reliable paste-and-check workflow
When formulas come from another workbook, a website, or a message, inspect one formula before filling a large range. Confirm that the destination is General format, the formula begins with an equals sign, the references make sense, and calculation is set as intended. Then compare a known row’s output with a manual calculation or a trusted existing result.
- Use a normal paste when you want formulas and their references copied.
- Use Paste Values only when you want fixed results and no ongoing recalculation.
- After pasting, click a formula cell and inspect the formula bar rather than relying only on the displayed result.
- If you paste into a filtered or structured table, verify that the formulas landed in the intended rows and columns.
- Save the workbook after confirming the result, especially if it uses external links or a calculation mode that other users may not expect.
Regional settings can affect how formulas are interpreted. In some locales, Excel uses semicolons rather than commas between function arguments. A pasted formula with separators from a different locale may be rejected or need adjustment. The multiplication example above has no function arguments, so it avoids that separator issue. If Excel reports a formula syntax problem, check the error prompt and the list separator used by your Excel installation rather than replacing punctuation at random.
When to look beyond calculation settings
If Automatic mode, cell format, Show Formulas, and references all look correct, consider whether the workbook contains a circular reference. A circular reference occurs when a formula depends on its own result, directly or through other cells. Excel may warn about it, and iterative calculation can change how such workbooks behave. Do not enable iterative calculation just to make a pasted formula work; first trace the dependency and confirm that the workbook is intentionally designed to use a circular calculation.
Also consider whether the formula depends on a feature unavailable in the Excel version being used, an add-in, or an external data connection. A formula copied from a newer Excel version may not be recognized in an older version. Similarly, refreshing a linked query or data connection is different from recalculating a worksheet formula. If the formula itself is visible and syntactically valid but its inputs come from imported data, refresh or verify those inputs separately.
For collaborative workbooks, ask whether another person changed the calculation setting, edited the source data, or pasted values over formulas. A quick comparison with a known-good copy can help identify whether the problem is limited to a few cells or affects the workbook more broadly. Avoid replacing a large range until you know whether the problem is display, calculation, or formula content.
Frequently Asked Questions
Why does Excel show the formula instead of the answer?
Show Formulas may be on, or the cell may contain text instead of a formula. Toggle Formulas > Show Formulas to check the first possibility. If only certain cells show formula text, set them to General and re-enter the contents with F2, then Enter.
Why does my pasted formula show an old result?
Calculation may be set to Manual. Choose Formulas > Calculation Options > Automatic, then recalculate if needed. Also confirm that the formula points to the inputs you just changed, not to a different row or sheet.
Will changing Text format to General fix every formula?
No. It changes the cell’s format but may not reinterpret content that was already entered as text. After choosing General, re-enter the formula with F2 and Enter. If the formula was already valid and Show Formulas was enabled, re-entering it is unnecessary.
What is the difference between copying a formula and copying its value?
A normal paste usually copies the formula, so relative references can shift at the destination and future input changes can update the result. Paste Values copies the current displayed result instead; it no longer calculates from the original formula.
Why does a pasted formula return zero?
Check its referenced cells and the formula’s logic. The references may have shifted during the paste, inputs may be blank or zero, or the formula may legitimately produce zero. Inspect the formula bar and test the inputs before changing calculation settings.
Related
- Excel ODE Solver: How to Solve Differential Equations
- Free Wedding Budget Spreadsheet: The Knot’s Options
- How to Insert Slide Numbers in PowerPoint: A Quick Guide
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- Office 365 Code Not Working? Fix the Common CausesIf an Office 365 code is not working, first identify what kind of code it is: a product…
- Excel Filter Not Working? Common Causes and FixesWhen an Excel filter is not working, the cause is usually an incomplete data range, a blank or…
- Excel Worksheet Won’t Scroll? Fix the CauseIf an Excel worksheet won’t scroll, first check whether Scroll Lock is on; then check for frozen panes…
- Excel Transpose Paste Not Working? Fix Common CausesIf Excel’s Transpose paste is not working, copy the source cells, select one blank destination cell, then choose…
- Excel Not Displaying Sum? Fix the Status Bar and FormulasIf Excel is not displaying a sum, first select the numeric cells: Excel for Microsoft 365 on Windows…
- Password Protect Excel Not Working? Find the Right FixIf Excel password protection is not working, first identify what you protected: encrypt the workbook to require a…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.