Using $ in Formulas

  • Thread starter Thread starter Kevin
  • Start date Start date
K

Kevin

What is the difference between using the $ symbol in
front of the cell reference in a formula, and not. I am
working with two workbooks of similar data, one has the $
sign in front of the cell ref. while the other does not.
The one with the dollar sign has a size of only 239 KB,
while the one without the $ sign has a size of 2.85 MB.
Will the $ symbol be the reason for the big difference in
Workbook Size? or should i be looking for another reason?
Thank you.
 
First the Dollar Sign $ is an indicator for an absolute
reference. i.e. $b$2 means look at the second column from
the left of the workbook and the second row down. If you
copy this reference it will not change.

So if C2 has the formula =$B$2+1, and B2 contains the
value 2, C2 will display the result 3 (2+1).
if B3 contains the value 4, and you copy the formula in
C2 to C3, then the result will still be 3, as it will
still look at B2 and add 1.

This formula without the absolute reference =b2+1 and
when copied to c3, would then give the result 5 (4+1) as
the reference would be relative.

A relative reference is one which says look X columns
Left or Right, and Y rows Up or Down. It doesn't matter
where the cell containg the reference is, it looks X left
and Y Down ( or whatever). So by putting the relative
reference =b2+1 in cell C2, we are actually telling excel
to take the value in the cell one column left, and in the
same row and add 1 to that value. When copied, from C2 to
C3, the formula still looks one column left and same row
(B3) and adds 1.

Interesting to note, that note only can the whole
reference be absolute or relative, but you can mix the
relative and abosulte parts of the reference:

$B$2 absolute reference, look at 2nd col, 2nd row
$B2
 
First the Dollar Sign $ is an indicator for an absolute
reference. i.e. $b$2 means look at the second column from
the left of the workbook and the second row down. If you
copy this reference it will not change.

So if C2 has the formula =$B$2+1, and B2 contains the
value 2, C2 will display the result 3 (2+1).
if B3 contains the value 4, and you copy the formula in
C2 to C3, then the result will still be 3, as it will
still look at B2 and add 1.

This formula without the absolute reference =b2+1 and
when copied to c3, would then give the result 5 (4+1) as
the reference would be relative.

A relative reference is one which says look X columns
Left or Right, and Y rows Up or Down. It doesn't matter
where the cell containg the reference is, it looks X left
and Y Down ( or whatever). So by putting the relative
reference =b2+1 in cell C2, we are actually telling excel
to take the value in the cell one column left, and in the
same row and add 1 to that value. When copied, from C2 to
C3, the formula still looks one column left and same row
(B3) and adds 1.

Interesting to note, that note only can the whole
reference be absolute or relative, but you can mix the
relative and abosulte parts of the reference:

In Cell C2
$B$2 absolute reference, look at 2nd col, 2nd row
$B2 Mixed, look at second col, and same row
B$2 Mixed look one col left and in 2nd row
B2 Relative, looke one col left and same row.

While I can't give you a definitive answer as to file
size. I think, and only think, that it may have something
to do with the referencing. There are a number of other
factors, do you have any modules attached to the
worksheets, or substantial code in the workbooks
themselves?

I can see a rationale for absolute references to use less
space than relative references, but as I have said, am
only guessing.

steve
 
Hi

Absolute (preceeded with $) or relative references behave differently when
the formula is copied. Best way to understand is to try it out with all
different possibilities - try to copy the formulas below into some rectangle
range
=A1
=$A1
=A$1
=$A$1

About the size of workbook - it hardly is caused by 1 reference. Try to save
the workbook under different name (Save As) - maybe you have the history of
changes stored there. When the new file size remains big, then you have to
look for hidden worksheets or something alike, or try to save all visible
worksheets into new file {mark all tabs, right-click onto some marked tab,
and then MoveOrCopy - (new book) - Create a copy}.
 
Back
Top