Exact Formula Copy

February 03, 2022 - by Bill Jelen

Problem: I need to make an exact copy of a range of formulas. I do NOT want the cell references to change as I copy. Whoever set up the worksheet did not include dollar signs in the formulas. It is like I want to do a cut and paste, but keep the original formulas there.

This is a common problem. If you copy and paste, the formula references will change. If you cut and paste, the formula references keep pointing at the same place. But, with a cut and paste, the original formulas are no longer there.

Strategy: Use Find and Replace to replace the leading equals sign with a word. This changes the formulas to text. Copy the text to the new location, then use Find and Replace to change the word back to an equals sign. You will now have two identical sets of formulae.

Someone built formulas without the dollar signs, so they are hard to copy. Converting = to a word allows you to copy, then use Find and Replace to return to the equals sign. — Figure 315. Replace = with any word.

After doing that replace, you have text versions of the formulas. Copy the text to a new place.

After doing the copy, change equal back to =. — Figure 316. Change the text back to formulas.

Use Find and Replace on both the original and copied range to change the word back to an equals sign

After converting the word back to the equals sign, the formulas have been copied. — Figure 317. After changing equal to equal, the formulas are pointing to the original cells.

You have an exact copy of the original formulas.

This article is an excerpt from Power Excel With MrExcel

Title photo by Neil and Zulma Scott on Unsplash