"How To Excel" Mini-Tutorials|
"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.
- Select the cells you want to copy.
- From the Edit menu choose Replace.
- In the Replace dialog box, enter (an equal sign) in the 'Find what' field
and enter (the number symbol) in the 'Replace with' field.
- Clear the check marks for the other options and click OK.
- Now copy and paste your formulas to the new location
- With the cells in the new location still selected, replace
- 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)