using $ in a formula

[robin]

when in a cell and I type: =$C4, what does that mean vs
=C4?

Also what does =$C$4 mean vs =C$4? what is the
significance of the dollar sign?

Thanks

 

[JulieD]

Hi Robin

it changes the addressing from relative to absolute
e.g. if i have in cell C1 the formula
=A1*B1
and i fill it down to C2 the formula will change to
=A2*B2
or if i fill it across to D1 the formula will change to
=B1*C1
- this is relative addressing and is the default in excel

however sometimes you want to "fix" the cell reference in a formula and this
is where the $ come in
=A1*$B$1
filled down from C1 to C2 will change to
=A2*$B$1
filled across from C1 to D1will change to
=B1*$B$1

however, sometimes i might want the row to stay constant (absolute) and the
column change so i would use
=B$1
othertimes i might want the column to be absolute and the row relative
=$B1

you could also check out the following article
http://www.cpearson.com/excel/relative.htm

Hope this helps
Cheers
JulieD

 

[David McRitchie]

Hi Robin,
Julie mentioned use of "fill handle" but if not familiar with it you can
look it up in Help or in more detail on my site
Fill-Handle, Replication and use of the Mouse
http://www.mvps.org/dmcritchie/excel/fillhand.htm

Some additional information on Absolute Reference:
The absolute reference refers to absolute with regards to fill handle,
copy and paste, drag and drop, and other fill operations, it is not
absolute with regard to inserting and deleting rows / columns.
For something to never change you would probably use something
that places the address within double quotes.

 

[Harald Staff]

when in a cell and I type: =$C4, what does that mean vs
=C4?

To either add further info or to exceed the confusion: The $ does nothing to
the cell or to the math involved in it. It is only used when you copy-paste
the cell to a new location or fill it to other ranges, in those cases it
decides what addresses the target cells will contain. But as purely "static
cell content" it's worthless.

Best wishes Harald