Excel: Exact Formula Copy

This page is an advertiser-supported excerpt of the book, Power Excel 2010-2013 from MrExcel - 567 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

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.

  1. Replace = with any word.

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

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

  2. Change the text back to formulas.

  3. You have an exact copy of the original formulas.