“In my Excel, numbers (1,2,3…) appeared in the column headings of the sheet instead of the usual letters (A,B,C…)! All the formulas have turned into an incomprehensible mess with the letters R and C! What to do??? Help!”
I hear this question at almost every training, and on our forum it pops up with enviable frequency. Let’s finally figure out what kind of tricky link mode it is, how to deal with it, and what, in fact, it is intended for.
What is it
The classic and well-known system of addressing sheet cells in Excel is a combination of a column letter and a row number – sea battle or chess use the same idea to designate board cells. The third cell from the top in the second column, for example, will have the address B3. Sometimes this style of links is also called “A1 style”. In formulas, addresses can be used with different types of references: relative (simply B3), absolute ($B$3) and mixed pinning ($B3 or B$3). If it is not very clear with dollars in the formulas, then I strongly advise you to read here about different types of links before continuing.
However, there is also an alternative little-known addressing system called “R1C1 style”. In this system, both rows and columns are denoted by numbers. cell address B3 in such a system would look like R3C2 (R=row=row, C=column=column). Relative, absolute and mixed references in such a system can be implemented using constructions like:
- RC – relative reference to the current cell
- R2C2 – same as $B$2 (absolute reference)
- RC5 – reference to a cell from the fifth column in the current row
- RC[-1] – reference to a cell from the previous column in the current row
- RC[2] – a reference to a cell spaced two columns to the right in the same row
- R[2]C[-3] – a link to a cell two lines below and three columns to the left of the current cell
- R5C[-2] – a link to a cell from the fifth row, spaced two columns to the left of the current cell
- etc.
Nothing super complicated, just a little unusual.
How to enable/disable it
Few people use this mode consciously. Usually it accidentally turns on itself, for example, when opening unloading curves from 1C in Excel and in some other situations. Turning it off is quite easy. The easiest way:
In Excel 2007/2010: Button Office (File) – Excel Options – Formulas – R1C1 link style (File — Excel Options — Formulas — R1C1-style)
In Excel 2003 and older: Service – Options – General – R1C1 Link Style (Tools — Options — General — R1C1-style)
If you have to do this often, then it makes sense to create a simple macro that switches these two modes back and forth:
Sub ChangeRefStyle() If Application.ReferenceStyle = xlA1 Then Application.ReferenceStyle = xlR1C1 Else Application.ReferenceStyle = xlA1 End If End Sub
You can save it to your personal macro book and attach it to a button on the toolbar or to a keyboard shortcut (how to do this is described here).
Where it can be useful
But this is the right question. If the stars are lit, then someone needs it. There are several situations where the R1C1 link mode is more convenient than the classic A1 mode:
- RџSЂRё checking formulas and finding errors in tables, it is sometimes much more convenient to use the R1C1 reference mode, because in it formulas of the same type look not just similar, but exactly the same. Compare, for example, the same table in formula debugging mode (CTRL+~) in two addressing options:
Finding a bug in R1C1 mode is much easier, right?
- If big table with the data on your sheet starting to take up several hundred rows in width and height, then there is little sense from the address of a cell like BT235 in the formula. Seeing the column number in such a situation can be much more useful than its letters.
- Some Features Excel, for example INDIRECT (INDIRECT) can work in two modes – A1 or R1C1. And sometimes it turns out to be more convenient to use the second one.
- В macro code in VBA it is often much easier to use the R1C1 style for entering formulas into cells than the classic A1. So, for example, if we need to add two columns of numbers with ten cells each (A1:A10 and B1:B10,) then we could use a simple code in the macro:
Range("C1:C10").FormulaR1C1="=RC[-2]*RC[-1]"because in R1C1 mode, all formulas will be the same. In the classical view, in the cells of column C, all the formulas are different, and we would have to write code for a loop through each cell in order to determine the formula for it personally, i.e. something like:
For Each cell In Range("C1:C10") cell.Formula = "=" & cell.Offset(0, -2).Address & "*" & cell.Offset(0, -1).Address Next cell - What are macros, how to create them, where to insert macro text in VBA?
- Why $ in cell addresses? Different types of links in Excel formulas.