Copying Formulas

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to copy a formula in Excel. I want to copy the formula but I do
NOT want the formula to change. I also don't want the content simply changed
but the actual formula. Is this possible?
 
If you mean you don't want cell references to change, write the formula
with "$" signs example $C$2 to keep C2 in the all the "copy to"
location. Otherwise your second sentence makes no sense.
 
There are a number of ways to achieve this. I take it that when you say you
do not want the formula to change, you do not want the cell addresses in the
formula to change.

If you use absolute cell addresses, it will not change. You do this by
adding a "$" before each parameter you do not want to change. Say you have a
formula in B1 that reads =SUM(A1:A20). If you copy this one row down, it
will change to =SUM(A2:A21). If you copy it to C1, it will change to
=SUM(B1:B20). Now you start adding $ signs.

If =SUM($A1:A20) is copied one row down, it will change to =SUM($A1:A21)
When copied one column over, it will change to =SUM($A1:B20)

if =SUM($A$1:A20) is copied down, it will change to =SUM($A$1:A21), and if
copied one column over, to =SUM($A$1:B20).

The same applies if $ signs are added before the second part of the formula,
eg =SUM($A$1:$A20) and =SUM($A$1:$A$20)
 
Sorry for the lapsus latina. If =SUM($A1:A20) is copied down, it will change
to =SUM($A2:A21). If =SUM($A$1:A20) is copied down, it will change to
=SUM($A$1:A21)
 
Back
Top