This is a discussion on Copy/Paste exact formula within the Excel Questions forums, part of the Question Forums category; How do you copy and paste a formula over an entire column without Excel changing the formula for each cell?...
How do you copy and paste a formula over an entire column without Excel changing the formula for each cell?
Sorry for the blank reply
1. Select the cell containing the formula to be copied
2. Hit F2 to enter Edit mode
3. Place an apostrophe (‘) before the ‘=’ sign to make it a string
4. Hit Enter
5. Do ^c
6. Select the col. in which you want to paste the formula
7. Hit ^v
8. Keeping the column selected, do Edit>Replace
9. In the dialog box type ‘= in the ‘Replace’ box
10. Type = in the Replace with box.
Mala ! You're the Mala I "know", right ? nice to see you around here.
Juan Pablo González
You could also use absolute referencing. Just place a $ sign before the column and / or row parts of cell references in the formula that you don't want to change when copied. i.e.
=Sum(A1:B6) becomes =Sum($A$1:$B$6)
If you press F2 to edit your formula, you can then use F4 to toggle through the options (A1, $A$1, $A1, A$1) when the cursor is over a cell reference in your formula.
If you play around with this a little, you'll see how it works.
[ This Message was edited by: JayKay on 2002-06-13 10:17 ]
Use dollar signs! =(A1)+(B1) will become = (A2)+(B2) when copied to the next cell down. =($A$1)+($B$1) will stay =($A$1)+($B$1) no matter where you copy it.
Toggling key F4 works very well.