Understanding R1C1 References

How do I make the A1 style the default so I don't have to unclick "R1C1 reference style" in options for every new spreadsheet I start?

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.

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!

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

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

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.

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.

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.