Referencing a cell in Excel?
If I reference a cell like this =A1+92 and then I copy that cell and I paste it one cell lower Excel automatically changes my formula to =A2+92. I need to know how I can copy and paste a formula to multiple cells whilst keeping the referenced cell the same. I want a whole column that has the same formula.
Ideally I would LIKE to be able to do something akin to =C1*A1 in one cell and then =C2*A1 in the next cell, etc. Being able to have ONE cell reference increment and the other be fixed.
Is Excel capable of that?
Answers & Comments
Verified answer
As Greg says, you need to use the dollars ($) sign to "freeze" either the row, the column or both.
This is often required so there is a shortcut for this. With the cursor over the reference you want to change you can use the F4 key. If you hit it once it converts, say, A1 to $A$1. If you hit it again it converts it to A$1, again for $A1 and again it restarts the cycle at A1.
If you have a formula like: =SUM(A1:C9) you can highlight the A1:C9 part (or the whole thing) and F4 will change both at once.
Change it to this:
=C1*$A$1
or
=C1*A$1
The $'s make the reference absolute. The 1 will not change if you have a $ in front of it when copying down.