"How To Excel" Mini-Tutorials|
"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.
- Select the range of cells containing the formulas
- From the Edit menu select Replace (or use the keyboard shortcut CTRL+H)
- In Find box enter the = (equals) sign
- In the Replace box enter the # (number) symbol
- Click Replace All.
- Select Edit, Copy, then move to the destination cell and select Edit, Paste.
- 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)