referencing

[Richard]

I don't understand why we use cell referencing at all. Can anybody tell me
what is the different between these references $A1, A$1, and $A$1 or exactly
what they do.

Thanks

 

[JE McGimpsey]

Take a look at the "The difference between relative and absolute
references" topic in the "About cell and range references" topic in XL
Help.

Essentially,

When copying/dragging/filling formulas, putting a $ in front of the
column character(s) preserves that column in the copied cells. Likewise
a $ in front of the row number.

Put these formulae in the indicated cells:

B1: =$A1
C1: =A$1
D1: =$A$1

Now select B11, grab the fill handle (lower right corner of D1) and
drag down a few rows. Look at how the formula changes in rows 2 and
greater.

Now put these formulae in the indicated cells:

A2: =$A1
A3: =A$1
A4: =$A$1

Select A2:A4 and drag to the right. See how the formulae change.

 

[Guest]

One way to explain it is:

$A1 - The column A is absolute, which means it will not change no matter
where you should happen to copy the cell to from where the reference $A1
resides. However, the row will change according to how many rows plus or
minus you eventually paste the $A1 to. It will no longer be $A1 but $A & +
or - numRows.

A$1 - Same as above except that the row does not change and the column does.

$A$1 - This is an absolute reference which means that no matter where you
place it on the worksheet, it means cell A1 or cells(1, 1) or R1C1. The
first cell column A, Row 1.

I could go on, but there is abundant explanation in the help Excel help files.