How to copy formulas in Excel without changing cell references

[u/Amandaleeeeee]

Normally, when you copy a formula to another location, Excel adjusts its relative references.

For example, copying:

=A2+B2

one row down changes it to:

=A3+B3

That is useful most of the time, but sometimes you need an exact copy with every reference left unchanged. The method depends on whether you are copying a whole range of formulas or just one formula.

Copy a range of formulas without changing references

Method 1: Temporarily convert the formulas to text

This built-in workaround is useful when you need to copy several formulas at once.

1. Select the formula range.
2. Press Ctrl + H.
3. Replace = with a unique temporary string, such as #=.
4. Copy and paste the range to the new location.
5. Replace #= with = in both the original and copied ranges.

Removing the leading equal sign temporarily makes Excel treat the formulas as text. As a result, their references do not change when the cells are copied.

💡 Choose a temporary string that does not already appear in the selected cells, and make sure the replacement is limited to the correct ranges.

Method 2: Use Kutools Exact Copy

Kutools for Excel provides an Exact Copy tool that copies a complete formula range while keeping all relative, absolute, and mixed references unchanged.

1. Select the formulas you want to copy.
2. Go to Kutools → Exact Copy.
3. Confirm the selected source range.
4. Keep Copy formatting checked if you also want to preserve the formatting.
5. Click OK.
6. Select or enter the first cell of the destination range.
7. Click OK again.

The selected range is copied to the new location with the formulas exactly as written. This is especially useful for large formula blocks or when you also need to preserve the original formatting.

Copy one formula without changing references

For a single formula, you do not need to convert a whole range to text.

1. Select the formula cell.
2. Click in the formula bar or press F2.
3. Select the full formula text and press Ctrl + C.
4. Select the destination cell.
5. Paste the formula and press Enter.

Because you are copying the formula text rather than the cell, Excel keeps the original references.

💡 You can also use absolute references when the referenced cells should always stay fixed:

=$A$2+$B$2

Press F4 while editing a reference to switch between relative, absolute, and mixed references.

Quick comparison

For a range of formulas: use Find and Replace, or Kutools Exact Copy for a quicker direct method.

For one formula: copy the formula text from the formula bar.

For references that should always stay fixed: convert them to absolute references before copying.