Formula Errors

How to Fix Excel #REF! Errors When Copying Formula Results

Maira Mehtab Sep 27, 2026 876 views

Question details

The user wants to know how to avoid getting a #REF! error when copying the results of a formula to another cell.

Product: Excel
Device & OS: not provided
Scenario: Copying a cell containing a calculated formula to another location where the relative references become invalid.
Observed behavior: The destination cell displays a #REF! error instead of the expected calculated result.

Before you start

Before proceeding, determine whether you need the new cell to contain the actual formula with updated references, or just the static text/number result of the original calculation.

Solution 1Recommended

Use the Paste Values Feature

The most common reason for a #REF! error when copying formulas is that the relative cell references are no longer valid in the new location. Pasting just the values solves this.

When you copy a cell with a formula, Excel automatically adjusts the cell references based on the new location. If the formula tries to reference a cell that doesn't exist (such as moving above row 1 or to the left of column A), it throws an invalid reference (#REF!) error.

Copy the formula cell

Select the cell containing the formula result you want to copy and press Ctrl+C.

Select the destination

Right-click the destination cell where you want the copied result to appear.

Paste as values

From the context menu, look under 'Paste Options' and select the 'Values' icon (usually depicted with '123'). Alternatively, you can press Ctrl+Shift+V to paste the result as plain text.

Keyboard Shortcut: Using the Ctrl+Shift+V shortcut is a quick and effective way to paste only the calculated values without carrying over the underlying formula or source formatting.

Efficient Spreadsheet Management

Easily Copy and Paste Formula Results in WPS Office

WPS Spreadsheet provides an intuitive interface for managing complex formulas and pasting values without errors. It is a powerful spreadsheet tool that allows for seamless data manipulation while avoiding reference issues.

1. Copy the target cell: Open your spreadsheet in WPS Office, select the desired formula cell, and copy it using Ctrl+C.
2. Choose destination: Click on the destination cell where you want to paste the data.
3. Use Paste Special: Right-click and choose 'Paste Special' > 'Values', or simply use the keyboard shortcut Ctrl+Shift+V to avoid carrying over broken formulas.

Fully compatible with Microsoft Excel (.xlsx, .xls) files, formulas, and functions.Dedicated Paste Special menu for precise control over values, formats, and underlying formulas to prevent #REF! errors.Free and lightweight alternative for all your data analysis and spreadsheet needs.

microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel show a #REF! error when I delete a row or column?

If a formula refers to a specific cell and you delete the row or column containing that cell, the formula can no longer find the referenced data. Excel replaces the missing cell reference with #REF!, resulting in an invalid reference error.

Can I fix a #REF! error after it happens?

Yes. You can use the Undo command (Ctrl+Z) immediately after the action that caused the error to revert the change. If the error was saved or made earlier, you must manually select the cell, edit the formula in the formula bar, and replace the '#REF!' portion with a valid cell reference.

How do I copy a formula exactly without changing its cell references?

To prevent cell references from shifting when copying, you need to make them absolute. Edit your original formula and add dollar signs before the column letter and row number (e.g., change A1 to $A$1). Once you make the references absolute, you can copy and paste the formula anywhere without triggering a #REF! error.

Excel & Data Problems