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

Move Formulas Without Changing Their Relative References


If you want to copy a formula without having it's column and row references change, you can first make the formula absolute by inserting dollar signs before the row and column references (i.e. B15 would become $B$15). Now when you copy it, the references will remain unchanged.

Sometimes however, you may want to duplicate a range of formulas from one area of your worksheet to another without having their references change. Rather than edit each formula to give it an absolute reference, here's a neat trick that will make the task much easier.

  1. Select the range of cells containing the formulas
  2. From the Edit menu select Replace (or use the keyboard shortcut CTRL+H)
  3. In Find box enter the = (equals) sign
  4. In the Replace box enter the # (number) symbol
  5. Click Replace All.
  6. Select Edit, Copy, then move to the destination cell and select Edit, Paste.
  7. Now repeat steps 2 to 5 on both the original cells and the destination cells but, this time, switch the = and # symbols.


    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.