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)

7

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

Applying a Style

Styles can be a great help in making sure that the cells in a worksheet are formatted consistently. Here's how to apply ...

Discover More

Fast AutoFill

Want to fill a long column with predictive data? It's easy to do by using AutoFill and a double-click of the mouse.

Discover More

Selecting a Window

When you get a lot of windows open on your screen, it can sometimes be difficult to locate the one with which you need to ...

Discover More

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

More ExcelTips (ribbon)

Embedding Your Phone Number in a Workbook

Want to provide a bit of contact information in a workbook? A great place to do it (out of sight, but not inaccessible) ...

Discover More

Creating New Windows

If you need to look at different parts of the same worksheet at the same time, the answer is to create windows for your ...

Discover More

Not Enough System Resources

When you are using Excel, it can be frustrating to receive a cryptic error message that indicates the program cannot ...

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 nine more than 0?

2019-05-13 04:33:48

David Robinson

Bryan, this is a different question really, but in short you can use the INDEX function for this. Type =INDEX(, then click the top left of the sheet where column headers meet row headers, that little triangle, to select the entire worksheet, then enter comma 1 (for first row) and comma 1 (for first column). You'll end up with something like
=INDEX(1:1048576,1,1)

This will work even if you insert new rows or columns.
You could use a similar approach to get a range of cells by using OFFSET.


2019-05-11 01:04:14

Bryan

I'm wondering if there is an Excel reference formula that will reference a cell location, and stay referenced to that cell location even if the original cell is moved. So the normal reference formula, for example would be =A1. So let's say that formula was in B1. The default reference behavior is, as you know, for that reference to remain linked, so that if A1 is moved to let's say A3, cell B1 will still refer to the "orginal" A1 cell, which is now C1. That default "follow" reference behavior is not what I want, rather, I want the formula to remain "fixed" to A1.

To further clarify, let's say, using the above scenario, the number 10 was in A1, that being the case, the number 10 would also be displayed in B1. What I want is to be able to move A1, and then have B1 go blank, until new data is entered into A1, which B1 would then display.

Seems simple enough to me, and that Excel should be able to do this, but all searches have simple led me to the regular relative/absolute references explanations which don't apply to what I want to do. Hope there's an answer! Thanks!


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.