"How To Excel" Mini-Tutorials
by TheExcelAddict.com
"Helping Average Spreadsheet Users Become Local Spreadsheet Experts"

Copy Formulas And Maintain The Original Cell References

As you know, when you copy a formula in Excel, the cell references adjust to the new location (unless the formulas have absolute references). For example if you copy a formula that refers to a cell two rows up, when that formula is copied to another location, the new formula will also refer to a cell two rows up from the new location.

However, there may be times when you may want to copy formulas to a new location but leave the original cell references intact. One way to do that is to first convert all the formulas to absolute references before copying. For multiple cells, that could be very time consuming. Another option could be to select the formula in the formula bar and copy them one by one. This would be fine for one or two formulas but wouldn't be practical if you have many formulas to copy.

Here's a neat (and fairly simple) trick to accomplish this task with minimal effort.

  1. Select the cells you want to copy.
  2. From the Edit menu choose Replace.
  3. In the Replace dialog box, enter (an equal sign) in the 'Find what' field
    and enter (the number symbol) in the 'Replace with' field.
  4. Clear the check marks for the other options and click OK.
  5. Now copy and paste your formulas to the new location
  6. With the cells in the new location still selected, replace with .
  7. If you want to retain the formulas in the original location also you will need
    to replace with in those cells too.

    Why not print (CTRL+P) this tip and share it with your friends and associates?

    Get more time-saving tips just like this one delivered to you by email every week in my FREE newsletter "Spreadsheet Tips From An Excel Addict". Subscribe at the www.TheExcelAddict.com/Newsletter.htm.
    You'll be amazed how much time you'll save just by learning a few of these tips.

    Copyright 2003 All Rights Reserved by Francis Hayes (The Excel Addict)

If you came to this page from my Excel Mini-Tutorials page, click here to close this window,
otherwise click here and you'll find lots more time-saving Excel tutorials like this one.