Microsoft Office Tutorials and References
In Depth Information
Cell and Range References
About R1C1 notation
Normally, Excel uses what’s known as A1 notation: Each cell address consists of a column letter
and a row number. However, Excel also supports R1C1 notation. In this system, cell A1 is referred
to as cell R1C1, cell A2 as R2C1, and so on.
To change to R1C1 notation, access the Formulas tab of the Excel Options dialog box. Place a
check mark next to R1C1 Reference Style. After you do so, you’ll notice that the column letters all
change to numbers. All the cell and range references in your formulas are also adjusted.
Table 3-1 presents some examples of formulas that use standard notation and R1C1 notation. The
formula is assumed to be in cell B1 (also known as R1C2).
Table 3-1: Comparing Simple Formulas In Two Notations
Standard
R1C1
=A1+1
=RC[–1]+1
=$A$1+1
=R1C1+1
=$A1+1
=RC1+1
=A$1+1
=R1C[–1]+1
=SUM(A1:A10)
=SUM(RC[–1]:R[9]C[–1])
=SUM($A$1:$A$10)
=SUM(R1C1:R10C1)
If you find R1C1 notation confusing, you’re not alone. R1C1 notation isn’t too bad when you’re
dealing with absolute references. But when relative references are involved, the brackets can be
very confusing.
The numbers in brackets refer to the relative position of the references. For example, R[–5]C[–3]
specifies the cell that’s five rows above and three columns to the left. On the other hand, R[5]
C[3] references the cell that’s five rows below and three columns to the right. If the brackets are
omitted, the notation specifies the same row or column. For example, R[5]C refers to the cell five
rows below in the same column.
Although you probably won’t use R1C1 notation as your standard system, it does have at least
one good use. Using R1C1 notation makes spotting an erroneous formula easy. When you copy a
formula, every copied formula is exactly the same in R1C1 notation. This is true regardless of the
types of cell references that you use (relative, absolute, or mixed). Therefore, you can switch to
R1C1 notation and check your copied formulas. If one looks different from its surrounding
formulas, there’s a good chance that it might be incorrect.
In addition, if you write VBA code to create worksheet formulas, you might find it easier to create
the formulas by using R1C1 notation.
 
Search JabSto ::




Custom Search