**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.

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:

- 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.)
- At the left side of the dialog box click Formulas. (See Figure 1.)
- 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.
- Click on OK.

** Figure 1.** The Formulas tab of the Excel Options dialog box.

*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**.

**Program Successfully in Excel!** John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out *Excel 2013 Power Programming with VBA* today!

When you freeze panes in a worksheet, those panes should persist even though you save the workbook and reload it. There ...

Discover MoreHiding columns is easy, even hiding column A. How, then, do you get that left-most column displayed again? Here are a few ...

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

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

2019-05-13 04:33:48

David Robinson

=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

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

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

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

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

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.

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.

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

Copyright © 2019 Sharon Parq Associates, Inc.

## Comments