Do you prefer the A1 reference style or the R1C1? If you’re like me you might choose A1 simply because that is also a steak sauce. I’ll admit that when I first came across the A1 vs. R1C1 question I had no idea what either meant or why it was even important. If you are in the same boat then this guide will present an easy to understand introduction to these reference styles.
Excel likes to give you options and the A1 and R1C1 references are just one such example. These are simply reference style notations used to identify a cell’s position in the spreadsheet.
A1 is the default reference style in Excel. To determine if you are using the A1 style look at the column headers. Are they letters? If so, then you are using the A1 style. When using this style columns are represented by letters and rows by numbers. The image below demonstrates an Excel spreadsheet in the A1 reference style.
We see that cell A1 is highlighted. In this reference style the column letter, in this case A, is followed by the row number.
The R1C1 reference style is an alternative way to create and display cell references to other cells. Also called relative notation, this style replaces the column headers, which are letters under A1, with numbers. However, R1C1 does not replace the row numbers with letters. Rather, rows continue to be labeled with numbers. Displaying a cell reference in this format can make it easier to understand one cell’s relationship to another.
The following image provides an example of basic addition using R1C1 style.
The numbers contained in the brackets tells you the cell’s position relative to the active cell (the cell where you are entering the formula). A negative row indicates the cell being referenced is above the active cells. A negative column indicates the cell being referenced is to the left of the active cell.
To change to the R1C1 reference style enter the Excel Options dialog box by clicking File > Options. For a quick keyboard shortcut use Alt + F + T on a Windows machine or ⌘ + , (comma) on a Mac. Navigate to the formulas tab and select the R1C1 reference style box.
To switch back to the A1 style simply follow the above steps, but uncheck the R1C1 style box.
Within the R1C1 style are two additional options: relative and absolute reference styles. The relative reference is the default style and utilizes square brackets around the numbers. For example, R[-1]C[-1] is in relative reference format.
The absolute reference style does away with the square brackets used in the relative style. An example of the absolute reference style would be R3C2. If you are an A1 style user you will remember absolute references as the those rows and columns preceded by the $. For example, $C$2 is the absolute reference to cell C2. The R1C1 style does not require the use of the $ and can make it easier when working with partial absolute references.
The below image shows the reference to the outlined cell in both R1C1 relative and absolute reference styles.
At this point you may be wondering if one style is superior to the other? The short answer is not really for the standard Excel user. If you prefer the A1 style over the R1C1 then use the A1 style. R1C1 is admittedly an older cell reference style and was introduced in 1982. However, it may be easier to check and find errors within formulas using the R1C1 style. If you are a frequent user of macros then the R1C1 style may have an advantage over the A1 style. While less compact than the A1 style, R1C1’s use of numerical references makes it easier for Excel to calculate row and column offsets when using macros.