How to copy formula without changing its cell references in Excel?

Excel's Replace functionality can help you copy formulas while preserving exact cell references. This is a manual method which is highly flexible, especially for small or moderate-sized data sets:

1. First, select the formula cells you wish to copy. Then, navigate to Home > Find & Select > Replace, or simply press CTRL+H to bring up the Find & Select dialog box.

2. In the Replace dialog, set the Find what field to “=”, and the Replace with field to “#” or another symbol that does not appear in your existing formulas. Click Replace All.
This action effectively transforms the formulas into plain text by preventing Excel from recognizing them as formulas. For example, “=A1*B1” changes to “#A1*B1”, allowing you to copy anywhere in your worksheet with no automatic cell reference changes.
     

3. All “=” signs in the selected area will be replaced, so your formulas become text strings. The replacement summary dialog will show the total number of changes. Review and close the dialog.
Example:

4. Now, you can copy and paste these transformed formulas into the desired cells in your worksheet without worrying about automatic cell reference adjustments.

5. After pasting, select both the original and destination ranges. Then, repeat the replacement in reverse: set Find what to “#”, and Replace with to “=”. Click Replace All again. Your copied formulas will revert from text back to functioning formulas, with their original cell references fully maintained.

This method is particularly suited for users who want full control and need to avoid any add-ins or complex setups. Please note: if your formulas contain the replacement symbol (such as “#”) elsewhere, choose a unique character to avoid errors. Always review your formulas after converting back to ensure expected functionality.

The Replace feature above manually transforms formulas into text, but Kutools for Excel provides dedicated utilities for this workflow: Convert Formula to Text and Convert Text to Formula. These can save you time and minimize errors, especially when copying large ranges of formulas or performing repetitive tasks:

1. Select the formula cells you need to copy, then click Kutools > Content > Convert Formula to Text. See screenshot:

2. The selected formulas become text instantly. Now copy and paste these text strings into your target range.

3. To restore functionality, select the pasted text and choose Kutools > Content > Convert Text to Formula. Your formulas are active again, and their references unchanged.

This method is best for those who work with formula blocks across different sheets or workbooks. Kutools handles the conversion efficiently, ensuring no formula structure is lost. If you work with sensitive formulas, confirm conversion steps carefully to avoid accidental errors.

When formulas update cell references after copying, the reason is likely due to their relative reference format, such as A1 or B1. If you switch these to absolute references, like $A$1 or $B$1, Excel will preserve them regardless of their new position. Kutools for Excel’s Convert Refers utility can convert selected formula references to absolute in just a few clicks:

1. Select the formula cells you wish to convert and copy, then click Kutools > Convert Refers.

2. In the Convert Formula References dialog, choose the To absolute option and confirm with OK.

3. Now, copy and paste these absolute reference formulas anywhere in your worksheet. They will not change or shift references after pasting.

Note: If you ever want to revert to relative references, use Convert Refers again and select To relative. This method gives reliable control, especially useful when formulas interact with static data points rather than dynamic ranges.

If you want a streamlined solution for copying formulas while retaining their original cell references, Kutools for Excel’s Exact Copy feature makes the process simple and convenient:

1. Select the formulas you want to copy, then choose Kutools > Exact copy.

2. In the Exact Formula Copy dialog box, click OK, then specify the first cell of your destination range in the next dialog and click OK again.

Tip: The Copy formatting option can help you preserve all cell formatting after pasting, making it ideal when transferring formulas between differently formatted sheets.
The formulas are then pasted into your specified cells with all references retained.

This approach is ideal for users who frequently copy formula ranges or need to ensure both formulas and formatting are maintained. Be sure the source and destination ranges are compatible to avoid mismatches or overwrite warnings.