Excel REF Error When Pasting: Causes and Fixes

If Excel shows #REF! after you paste, a formula has ended up with an invalid cell reference; the paste itself may have copied that broken formula or shifted a relative reference beyond the worksheet. Check the formula in the affected cell, identify which reference is invalid, and replace it with the intended cell or range. A message that says Excel cannot paste is a different problem, not a #REF! formula error.

What the #REF! error means

#REF! is Excel’s reference error. It means a formula points to a cell or range Excel can no longer identify. The error is visible in the cell, but the underlying cause is usually in the formula: a referenced row, column, or worksheet was removed, or a copied formula now points somewhere invalid.

For example, a formula might have referred to a cell in column B. If that column was deleted, Excel may replace the reference in the formula with #REF!. The calculation cannot continue because Excel has no address to use. This differs from errors such as #VALUE!, which generally indicate an unsuitable value or argument, and #NAME?, which often means Excel does not recognize text in a formula.

Pasting a value over a cell that another formula uses does not usually create #REF!. The reference remains valid, although the result might change because the cell now contains different data. Similarly, a mismatched paste-area size usually produces a paste warning rather than a reference error. It helps to distinguish a displayed formula error from a message that prevents the paste from completing.

Why #REF! can appear after a paste

These are common explanations when the error appears immediately after copying or pasting:

The copied cell already had a broken formula. Pasting it transfers the problem to the destination. A copied formula may also bring along a reference to a worksheet or range that is not available in the destination workbook.
A relative reference shifted during a copy. Excel adjusts relative references to suit the new location. If a formula is copied far enough toward the edge of the worksheet, a shifted reference can go beyond Excel’s row or column limits and become invalid.
A referenced row, column, or cell was deleted. This can happen before the paste, while reorganizing a sheet, or when the cells being moved or removed are part of a formula’s dependencies.
A formula was pasted into a different layout. Relative references that made sense in the original sheet can point to the wrong location in the destination. That may produce a wrong result, or an error if the adjusted reference is invalid.
A worksheet reference was damaged. A formula that refers to a sheet can become invalid if that sheet is deleted or the reference is otherwise changed. Copying the formula does not restore the missing sheet.

Copying and cutting are not identical operations. When you copy a formula, Excel normally adjusts its relative references for the destination. When you move cells with Cut and Paste, Excel generally preserves the moved formula’s references and updates references that point to the moved cells so they continue to follow the data. Workbook structure and the cells involved can affect the result, so inspect the formula rather than assuming every paste behaves the same way.

Find the broken reference in the formula

Start by selecting the cell displaying #REF! and reading the formula bar. Look for the literal #REF! inside the formula. It may be one part of a longer calculation, such as a multiplication or a SUM range, so the rest of the formula can still provide clues about the intended reference.

Select the error cell and note the formula shown in the formula bar.
Check whether the formula contains #REF!, or whether it refers to a sheet or range that has changed.
Compare it with a neighboring formula that still works. In a consistent table, adjacent formulas often reveal the expected row or column pattern.
Check the source cell before copying, if it is still available. Confirm that its formula is valid and that the destination is in the expected position.
If the error is in many cells, use Home > Find & Select > Find, search for #REF!, and choose to search formulas if needed. This can locate broken references embedded in formulas as well as cells showing the error.

For a second view of the formula’s dependencies, select the cell and use Formulas > Trace Precedents. Excel draws arrows to cells used by the formula when it can identify them. This is useful for valid references, but it cannot recreate an address that has already been replaced by #REF!. If you are unsure what a reference should be, compare a working copy, review the workbook’s layout, or consult the person who created the formula.

Repair the formula without guessing

Replace the invalid reference only after you know which cell or range the calculation is supposed to use. For example, suppose A2 contains a quantity of 3 and B2 contains a unit price of 12. If C2 should calculate the line total, its formula should multiply those two cells. If the current formula in C2 is =A2*#REF!, edit it to =A2*B2. With the stated values, the result is 36.

To edit, select the cell, click in the formula bar or press F2, correct the address, and press Enter. If the same error appears down a column, repair one formula first, verify its result, then fill or copy it to the other rows. Check that the references adjust as intended; use dollar signs when a reference must stay fixed as a formula is copied. For example, $B$1 stays anchored to B1, while B1 is relative.

Do not replace every #REF! with the same cell address automatically. One formula may have lost a reference to a quantity column and another may have lost a tax or lookup range. A technically valid formula can still calculate the wrong thing. Where a deleted range cannot be recovered from the workbook, use a backup, version history, or an authoritative source to establish the intended calculation.

Choose the right paste method

If the source formula is sound but you do not want its formulas copied, use a values-only paste. In Excel for Microsoft 365 on Windows, copy the source, select the destination, then use Home > Paste > Values. This pastes the displayed results rather than the formulas, so it avoids carrying a formula reference into the destination. It also means the pasted results will not update when the source data changes.

When you do want formulas, ordinary paste is appropriate if the destination layout matches the source. Check the first pasted formula and one farther down or across to confirm that relative references shifted correctly. If the formula should keep pointing to one fixed cell, make that reference absolute with dollar signs before copying. Paste Special can also transfer formats without formulas: use Home > Paste > Paste Special and select the relevant option, such as Formats.

These commands are for Excel for Microsoft 365 on Windows. Excel for Mac has comparable paste choices, though the ribbon and keyboard shortcuts can differ; on the web, available options depend on the browser and workbook state. Regardless of platform, choosing Values prevents formulas from being transferred, while choosing formulas or a normal paste can carry them. Do not select a paste mode as a repair for a formula that is already wrong.

Prevent reference errors during workbook changes

A few checks before and after editing can reduce the chance of a broken reference:

Use copy and paste deliberately: decide whether you need formulas, values, or formatting before pasting.
Before deleting a row, column, or worksheet, look for formulas that depend on it. Search for the sheet name or inspect important totals and summaries.
After copying formulas to a new area, inspect representative cells at the top, bottom, left, and right edges. Edge formulas are more likely to expose references that shifted unexpectedly.
Keep a backup before major structural edits. A saved copy makes it easier to compare the original formula if a reference is lost.
Use clear sheet names and consistent layouts. These make formulas easier to read and help you recognize when a pasted formula points to an unexpected place.

Excel’s Undo command can reverse many recent paste and editing actions, so use it promptly if the error followed a change and you are confident the preceding work should also be undone. Undo is not a substitute for saving a backup, particularly after many later edits or when the workbook has been changed by other users.

When it is not a #REF! formula error

Sometimes people describe any paste-related warning as a “ref error,” but the wording matters. A message that the copy area and paste area are not the same size concerns the selection dimensions. A clipboard warning concerns transferring data. A protected sheet, merged cells, or an incompatible selection can also block a paste. Those situations do not mean a formula contains an invalid reference.

Likewise, #SPILL! is a different error associated with a dynamic-array formula that cannot place its results in the required cells. It may appear after pasting a formula, but the remedy is to make room for the array output or address the obstruction, not to replace a reference as though the formula showed #REF!. Read the exact message in the cell and, where present, Excel’s error indicator before changing the workbook.

Frequently Asked Questions

Why does Excel show #REF! only after I paste a formula?

The copied formula may have already contained a broken reference, or Excel may have adjusted a relative reference for its new position. Compare the pasted formula with the source and a working neighboring formula. If the address shifted beyond the worksheet boundary, move the formula nearer the valid range or correct its reference.

Will Paste Special fix a #REF! error?

Not if the formula itself is broken. Pasting Values can avoid transferring a faulty formula, but it replaces it with a fixed result that will not recalculate. If you need a live calculation, repair the reference in the formula or paste a valid formula from an appropriate source.

How do I find every #REF! formula in a workbook?

Use Home > Find & Select > Find and search for #REF!. Search formulas to find the error text inside formulas, not only cells whose displayed result is the error. Review each result before editing because different formulas can have different intended references.

Can I undo a paste that caused #REF!?

Usually, if the paste was the most recent change, press Ctrl+Z on Windows. On Mac, use Command+Z. Undo restores the prior state for that action, but it may also reverse other changes if you have continued editing. If the workbook has since been saved or heavily edited, check a backup or version history instead.

Does #REF! mean my workbook is corrupted?

No. Most #REF! errors mean a formula contains an invalid reference, not that the workbook file is corrupted. Repairing the formula or restoring the missing sheet or range usually addresses the cause. If the file also fails to open or shows other damage, preserve a copy and investigate that separate problem.

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.