MrExcel Consulting
Your One Stop for Excel Tips & Solutions

how to copy formulas from one colum to another without excel changing references

Posted by Eduardo Forero on June 14, 2001 8:17 PM

How can I copy one column of formulas to another colum without excel changing the references?
Is like when you move a cell with the mouse, but this time i need them copied. HELP PLEASe

Posted by Ian G on June 14, 2001 10:38 PM

Hi Eduardo
to do this you'll have to change the cell references in the formulas to absolute references, and you do this by selecting the cell reference in the formula you want to be constant (or the whole formula if you want to change all its cell references)and press the F4 key. You'll know it's worked if the cell reference now has $ signs in it (eg $A$1). To undo just press F4 a couple of times until the $signs disappear.
Ian G

Posted by Quick dirty approach on June 15, 2001 6:39 AM

A)Select the column you want to copy
C)Replace the = w\ a MINIUIM of 3 alph characters
(ex xxx)
D)Copy the column any where you want, then select
both columns & EDIT\REPLACE the 3 xxx with =

1-Always use @ least 3 characters or you might
screwup a valid cell reference.
2-If you have any formulas in the new column
that cells within that column, they'll need to
be fixed since they'll still be referencing
the old column.

Posted by Eric on June 15, 2001 7:17 AM

use F2 to select the text of the formula

Lots of ways to do this. The following assumes that the formulas in your column were "copied down" from the first formula in the column.
Select the first cell with the formula, hit F2 to go into edit mode on that cell, then highlight the entire formula by hitting shift+home (or select with your mouse), then CTRL+C to copy, then escape, then go to the column to which you want to move the data, and hit CTRL+V while the cell to which you want the first formula pasted is selected. Then copy down to rebuild your column.