Copying Without Affecting Relative References

Works for Excel 2013 onwards

There is an easy technique to copy a single formula and paste it without affecting relative references but what if you wanted to paste lots of formulas?

The single formula technique involves copying the formula inside the formula bar and then pasting it in another cell. Because you are copying the formula itself no changes are made to any references when you paste.

Multiple formulas

To achieve a paste without affecting references in multiple formulas requires a few steps. This technique works on Excel 2013 and later versions.

In the image below there are simple formulas in column C multiplying column A by B. Some of the formulas have extra multiplications as well. We want to copy the formulas from column C to another place without changing the references.

We can add a formula in cell D2 and copy it down. The formula is

=FORMULATEXT(C2)

This displays the formula from column C – see image below. This is useful for training and documenting. In our case it captures the formulas.

We can copy the range D2:D8 and use Paste Special Values to paste in another cell – say cell E9. See image below.

This pastes the formulas as text causing them not to calculate. To make them calculate select the range E9:E15 and press Ctrl + H to open the Find & Replace dialog. Replace the = sign with the = sign as below and use Replace All.

Job done!

By replacing the = sign you force Excel to treat the text as a formula.

 

Please note: I reserve the right to delete comments that are offensive or off-topic.