Paste a function to another cell

  • Thread starter Thread starter cpliu
  • Start date Start date
C

cpliu

Sorry for another newbie question.

When you past a function from one cell to another, the cell number adds one
so you can do the same calculation on multiple columns. What if I want it
to stick to one cell and not to move down as I copy?

Use the example below: If I create "=A1+B1" in C1 and copy/paste the cell
into C2 and C3, A1 automatically becomes A2, A3... What if I don't want it
to change? What if I want it to stick with A1 when copying it to the other
cell?

A B C
5 6 =A1+B1
4 3 =A2+B2
3 4 =A3+B3

Thanks for the help,


cpliu
 
Use a $ in front of the reference you want locked.

ie $A$1 + B1 locks the A1 reference.

A$1 = B1 would lock only the row.
 
Hi

change your formulas to
A B C
5 6 =$A$1+$B$1

the $ sign tells Excel to keep the reference absolute rather than it being
relative.

You can either type the $ or click in the formula and press the F4 key.

if you press the F4 key a number of times you will notice it goes through a
cycle
A1 - both relative
$A1 - column absolute, row relative
A$1 - column relative, row absolute
$A$1 - both absolute

Hope this helps
Cheers
JulieD
 
That's it. Thank you so much for the quick and helpful instruction.

Best regards,

cpliu
 
Back
Top