R1C1 notation

Recently we discussed people’s preference for A1 or R1C1 notation without taking the time to explain how they both work. So here is a bit of an explanation.

For those not familiar with R1C1 notation:

R1C1 means row 1 column 1 ie A1 in the’ other’ notation. Well actually it means $A$1 as its an absolute address. If you copy =R1C1 from any cell to any other it will always to refer to the top left cell (A1).

Relative references in R1C1 are done with square brackets []. so

R[1]C[1] means one cell down and one to the right. In fact to make it easier I did some screen shots. The arrows are show dependents ones. (with hindsight, precedents might have been clearer).

r1c1

And the A1 equivalents:

A1 notation

Roughly speaking relative references are easier to deal with in A1 style notation and Absolute ones are probably easier to deal with in R1C1 style.

If you haven’t tried using the other style, try it, you may find its useful once you are familiar with it.

Oh and I used an internal tool to do the arrows in one click and PUP to convert from relative to absolute in one click.

HTH, the pictures look better if you click on them.

cheers

Simon

Advertisements

8 Responses to “R1C1 notation”

  1. Ross Says:

    FWIT
    >Oh and I used an internal tool to do the arrows in one click and PUP to >convert from relative to absolute in one click.

    http://www.blog.methodsinexcel.co.uk/2006/07/24/auditing-a-whole-range/

    and from my own “I’m lazy” collection

    Sub RefType()
    If Application.ReferenceStyle = xlR1C1 Then
    Application.ReferenceStyle = xlA1
    Else
    Application.ReferenceStyle = xlR1C1
    End If

    added to a keyboad sc. – save 4 clicks! which equates to 0.03 GBP per year! lol!

    Out of interest Simon, what net tool did you use?

  2. Ross Says:

    FWIT?????
    meant FWIW

  3. Simon Says:

    Ross
    Did you mean F.Wit?

  4. Harlan Grove Says:

    To me it’s the opposite of what Simon wrote: A1 addresses make more sense as absolute references, R1C1 as relative references. First, the only difference between the two for absolute references is the column portion. To me, $X$99 is somewhat more meaningful than R99C24. For relative referencing, if I want the formula in the current cell to be the cell above added to the cell to the left, and if the current cell were X99, the A1 formula would be =X98+W99 while the R1C1 formula would be =R[-1]C+RC[-1]. Note that the latter formula would be exactly the same no matter what the cell contained it, while the comparable A1 formula would change to =D5+C6 if it were supposed to be in cell D6. R1C1 style becomes even more useful for mixed references. To sum the cells from row 3 through the row 2 rows above the cell containing the formula (X99), the formulas would be

    A1 =SUM(X$3:X97)
    R1C1 =SUM(R3C:R[-2]C)

    The A1 formula changes depending on the cell in which it’s entered, but the R1C1 formula remains the same no matter where it’s entered.

    It’s far easier to spot inconsistencies in formulas that should be similar if not identical modulo location (e.g., cell X99 containing a formula pasted into X99:AF199) using R1C1 notation than A1 notation, but for me it’s easier to visualize where things are (absolute location) in A1 notation.

  5. Simon Says:

    Harlan
    Sorry , I wasn’t clear. what I meant was the way I have them they are easier to understand (no ugly symbols in the way). In terms of usefulness I agree with you entirely. I was more thinking of people who have never user R1C1 before and didn’t know the []. (it was a search term of what does R[1]C[1] mean that drove me to write the article.)
    You’re right I should have been clearer,. My way = easier introduction for a novice, your way = more useful for a more experienced user.

  6. Ross Says:

    >>Ross
    >>Did you mean F.Wit?

    Well thats more appt i guess :-)

  7. Excel XML Spreadsheet: going the XSLT way « The Wiert Corner – irregular stream of Wiert stuff Says:

    […] article talks more about styles and formulas (XML Spreadsheets store formulas in relative R1C1 style notation which is far easier to parse than the absolute A1 style notation, which most people […]

  8. Petra Elston Says:

    Hi
    It might be worth noting , just too help a beginner, that R[1]C, “defaults” to R[1]C[0], which gives the same results and is Relative
    Alan

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: