Microsoft Office Tutorials and References
In Depth Information
Finding Where It Is
The formula is cell C17 is almost identical to the one in cell C7 except that the
reference to cell C1 has been made row absolute by placing a dollar sign in
front of the row number. The formula in cell C17 looks like this: =B17×(1 +
C$1). When this formula was dragged down into C18 and C19, the reference
was not adjusted but stayed pointing at cell C1. Note that in this example
only the row part of the reference is made absolute. That’s all that is
necessary. You could have made the reference completely absolute by doing this:
=B17×(1 + $C$1) and the result would be the same, but it’s not required in
Put a dollar sign in front of the column letter of a cell reference to create an
absolute column reference. Put a dollar sign in front of the row number to
create an absolute row reference.
Excel supports two cell reference styles: the good old A1 style and the R1C1
style. You see the A1 style — a column letter followed by a row number —
throughout this topic (D4 or B2:B10, for example). The R1C1 style uses a
numerical system for both the row and the column, such as this: R4C10 —
literally, Row 4 Column 10 in this example.
To change the cell reference style, use File…Options, and check the R1C1
reference style on the Formulas tab. Using the R1C1 format also forces the
columns on the worksheet to display as numbers instead of the lettering system.
This is useful when working with a large number of columns. For example,
column CV positionally is the 100th column. Remembering 100 is easier than
Now let’s get back to the ADDRESS function. It takes up to five arguments:
✓ The row number of the reference.
✓ The column number of the reference.
✓ A number that tells the function how to return the reference. The default
is 1, but can be
✓ A value of 0 or 1 to tell the function which reference style to use.
✓ A worksheet or external workbook and worksheet reference.