Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Office 365. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Understanding R1C1 References.

Understanding R1C1 References

by Allen Wyatt
(last updated February 16, 2019)

5

Excel, by default, uses a reference format known as A1. This simply means that columns are referred to using letters and rows using numbers. References contain a combination of both the column letter and row number, thereby designating a unique cell.

Not all spreadsheet programs use this same method of referring to cells. The other major method of referencing cells is called the R1C1 format. In this notation, both rows and columns are referred to using numbers. The numbers are differentiated by using of the R and C letters, which stand for row and column. Thus, the intersection of row 5 and column 7 would be referred to as R5C7.

Excel allows you to control whether it uses A1 or R1C1 notation for cell references. To specify which notation format you want to use, follow these steps:

  1. Display the Excel Options dialog box. (In Excel 2007 click the Office button and then click Excel Options. In Excel 2010 or a later version display the File tab of the ribbon and then click Options.)
  2. At the left side of the dialog box click Formulas. (See Figure 1.)
  3. Figure 1. The Formulas tab of the Excel Options dialog box.

  4. If you want to use R1C1 format, select the R1C1 Reference Style check box; if you want to use A1 format (the default for Excel), clear the check box.
  5. Click on OK.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (8803) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Office 365. You can find a version of this tip for the older menu interface of Excel here: Understanding R1C1 References.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Converting Text to Comments

One of the strong suits of macros is that they can process the information in a document quickly and reliably. For ...

Discover More

Printing a Process List

There are programs (processes) running in your system all the time. You can view a list of these processes using the Task ...

Discover More

Including Section Numbers in an Index

When you use Word to create your index, you'll normally only include a page number in the index. If you want to create an ...

Discover More

Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!

More ExcelTips (ribbon)

Status Bar Summing No Longer Available

When you select a range of cells, Excel normally displays the sum of those selected cells on the status bar. If the sum ...

Discover More

Where Is that Name?

Want to easily see the location of named ranges in your worksheet? It's easy; all you need to do is use the familiar Zoom ...

Discover More

Drop-Down List Font Sizes

Excel has several features that cannot be customized. The font size in the drop-down lists is one of them. If you need ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is one more than 5?

2019-02-25 05:18:47

David Robinson

Well done on spotting the typo Alan, quite right, the cell to the right of "RC" should be "RC[1]".


2019-02-20 06:13:23

Alan Elston

Hi again David Robinson
i saw your second post , just after I posted...
Yes, the way a lot of editors "eat" spaces is very annoying. Most forum and Blog comment editors do that.
Alan


2019-02-20 06:08:48

Alan Elston

Hi David Robinson
I think I read somewhere that internally somewhere, Excel works in the R C type notation as its “internal language”.
I believe that is where it “comes from”: The macro recorder usually seems to give things in R C notation..
As you say, it seems that formulas can look less confusing if you use R C type notation.
I don’t have a lot of experience with these things. I still personally get a little confused still every time I do something with them. . It could do with a nice clear Blog I think. This one is not too bad
H t t p s: / / powerspreadsheets.com/r1c1-formular1c1-vba/

I try to explain it at the same time as explaining the column letter and row number notation, as I think it is just a case of different ways to show the same basic two ways that Excel holds references..
H t t p s : / /teylyn.com/2017/03/21/dollarsigns/#comment-191

Thanks for adding an explanation.
At first read, I did not quite follow this bit. Or rather I was not sure what you were trying to say with it
R[-1]C[-1] R[-1]C R[-1]C[1]
RC[-1] RC R[1]C
R[1]C[-1] R[1]C R[1]C[1]
And I think there is a typo in it.
Possibly you have it the wrong way around? Or maybe you are not showing anything in particular?

I would explain something like that in this sort of form:
lets say I want to reference the cell B2 from any of those 9 top left cells in a spreadsheet. These would be the eight relative references in the R C notation ( I cannot reference easily the cell that I am in from within the cell , hence 8 and not 9 )
=R[1]C[1] =R[1]C[0] =R[1]C[-1]
=R[0]C[1] XXXXXX =R[0]C[-1]
=R[-1]C[1] =R[-1]C[0] =R[-1]C[-1]
or
=R[1]C[1] =R[1]C =R[1]C[-1]
=RC[1] XXXXX =RC[-1]
=R[-1]C[1] =R[-1]C =R[-1]C[-1]

That little demo of mine is, as I understand it, basically how Excel “sees” =B2 in its “internal language”, when “looking” from the other cells. All those references it “sees” are 8 different “vectors”. If we drag any cell down or across using the little cross at the bottom right of the cell, then it uses the particular “vector” applied to the cell in question, ( when we omit the $ sign ). That is how Excel works to “guess” what different formulas you want when you drag: It is basically just dragging a fixed vector, so as you drag that vector it will point to a different cell. If you use a $ , then use of that $ tells Excel to use the exact grid reference, rather than the vector, for that co ordinate that you drag.

You are possibly trying to show something else? It would be interesting to know, as I was thinking of getting around to doing a short Blog on these things, and so am open to any ideas you have

Alan Elston

P.s. I have got in the habit of writing a lot of formulas in using coding. Initially I did this because I am English but using German Excel: I wrote the formulas in English and then the code automatically puts them in the correct language form. It also automatically adjust all column headers etc., so I don’t need to remember what all the headers are. It varies a bit depending on exactly what I am doing, but on average it is a lot quicker for me to do it like that.


2019-02-20 06:02:15

David Robinson

My white spaces got removed when my last comment went live, so here are those cell reference matrices with dots to space the cells correctly!

So in absolute terms the top-left cells in a spreadsheet are...
R1C1 . . . . . R1C2 . . . . .R1C3
R2C1 . . . . . R2C2 . . . . . R2C3
R3C1 . . . . . R3C2 . . . . . R3C3
...and the relative references work like this...
R[-1]C[-1] . . R[-1]C . . . R[-1]C[1]
RC[-1] . . . . . RC . . . . . . R[1]C
R[1]C[-1] . . . R[1]C . . . . R[1]C[1]

By the way I forgot to say that you can reference whole columns and rows easily. Row 4 is R4. Column F is C6.


2019-02-19 06:37:18

David Robinson

I feel this tip needs more meat on the bone on the "understanding" side. So, here is more on how the R1C1 notation works...

R is followed by a number specifying the row. R1 is row 1. R2 is row 2, and so on. You can alternatively put a number in square brackets and this acts as an offset to the row you're in now. R[1] is one row down. R[-1] is one row up. R[0] is on the same row as you're in, but the zero offset can be removed so you can just type R and it means the same thing.

The same principle applies to columns: C4 is column 4 (i.e. column D in A1 notation), C[5] is 5 columns to the right, C[-2] is 2 columns to the left, and C[0] or C means in this column.

So in absolute terms the top-left cells in a spreadsheet are...
R1C1 R1C2 R1C3
R2C1 R2C2 R2C3
R3C1 R3C2 R3C3
...and the relative references work like this...
R[-1]C[-1] R[-1]C R[-1]C[1]
RC[-1] RC R[1]C
R[1]C[-1] R[1]C R[1]C[1]

Some advantages of R1C1:
- If you're typing a formula in the middle of a spreadsheet you usually don't need to worry about where you are. If you're typing a formula that is processing data on the same row as you're in you can just type "C" instead of, say, "3015" (if you're in row 3015).
- When you copy formulas down or across, the text in the formula is identical. For example, in A1 notation the formula "=$A2 * B$1" will change as you fill down or right, to "=$A3 * B$1" if filling down or "=$A2 * C$1" if filling across, whereas its R1C1 equivalent, "=RC1 * R1C", remains the same. This makes it easier to debug and allows you to use find and replace more easily when changing formulas.
- Some formulas are a pig to write in A1 notation. For example, say you've selected all of column B - er, I mean column 2 - and you want to test if the content of column A is the same as the row above, in A1 notation it's almost impossible to write a simple test for this because there is no row above cell B1, but he same test in R1C1 is dead easy... =R1C = R1C[-1]
- Alphabetical column headers become confusing - much beyond column H it's hard to know which column number you're in, something you might need when writing an INDEX or OFFSET function. In R1C1 you don't have to work out what column AR is in numbers.


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.