Excel - The F4 key

Asked By Jan :\) on 21-Jun-08 09:13 AM
When I am working on a formula, and have to press the F4 key to change a
cell reference from A6 to $A$6, what is the purpose of the dollar signs in
the formula?  What does it mean in how the formula works?

Jan :)




Don Guillett replied on 21-Jun-08 09:58 AM
Look in the help index for
ABSOLUTE

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com
Pete_UK replied on 22-Jun-08 05:12 PM
If you had a formula which referred to cell A6, eg:

=3D10 * A6

and copied this down the column, it would change to:

=3D10 * A7
=3D10 * A8
=3D10 * A9

and so on in consecutive cells.

If you copied the original formula across the row, the cell references
would change to:

=3D10 * B6
=3D10 * C6

and so on.

There are times, though, when you do not want the cell reference to
change, and you always want the formula to refer to the value in cell
A6. In this case you must "anchor" the cell reference so that the row
and column references do not change when the formula is copied to
another cell, so that it will always refer to cell A6. Putting the
dollar signs in front of the row and column references enables you to
do this - if the formula was:

=3D10 * $A$6

then it would always be this whenever you copy the formula elsewhere.
This is referred to as Absolute addressing (rather than relative
addressing) and use of the F4 key when in edit mode enables you to
toggle through the 4 different combinations:

A6      - relative addressing, both column and row references will
change
$A6    - semi-absolute, the column reference will not change
A$6    - semi-absolute, the row reference will not change
$A$6   - absolute reference, neither column nor row reference will
change.

Hope this helps.

Pete

n
Jan :\) replied on 21-Jun-08 03:22 PM
Yes indeed, thanks, Pete!  That explains it very nicely.  :-)

Jan :)

If you had a formula which referred to cell A6, eg:

=10 * A6

and copied this down the column, it would change to:

=10 * A7
=10 * A8
=10 * A9

and so on in consecutive cells.

If you copied the original formula across the row, the cell references
would change to:

=10 * B6
=10 * C6

and so on.

There are times, though, when you do not want the cell reference to
change, and you always want the formula to refer to the value in cell
A6. In this case you must "anchor" the cell reference so that the row
and column references do not change when the formula is copied to
another cell, so that it will always refer to cell A6. Putting the
dollar signs in front of the row and column references enables you to
do this - if the formula was:

=10 * $A$6

then it would always be this whenever you copy the formula elsewhere.
This is referred to as Absolute addressing (rather than relative
addressing) and use of the F4 key when in edit mode enables you to
toggle through the 4 different combinations:

A6      - relative addressing, both column and row references will
change
$A6    - semi-absolute, the column reference will not change
A$6    - semi-absolute, the row reference will not change
$A$6   - absolute reference, neither column nor row reference will
change.

Hope this helps.

Pete
Jan :\) replied on 21-Jun-08 03:23 PM
Thanks, Don.

Jan :)
Pete_UK replied on 22-Jun-08 05:12 PM
You're welcome, Jan - thanks for feeding back.

Pete