A1 & R1C1 Reference Style in Excel [A Complete Discussion]

There are two types of reference styles available in Excel to reference a cell address. The types are A1 style reference and R1C1 style reference. Here, you will get a complete view of the A1 and R1C1 reference styles in Excel.

Reference Style in Excel.xlsx

What is Reference Style in Excel?

Each worksheet in Microsoft Excel is consist of 1,048,576 rows and 16,384 columns. By default, the rows are labeled with numbers such as 1,2,3, etc.

On the other hand, the columns are labeled with letters such as A, B, C, etc. However, the column labeling can be changed from letters (A, B, C) to numbers (1,2,3).

To reference each cell, a combination of column labels followed by the row label is used. This combination is called a cell address. Some examples of cell addresses are B5, D9, F12, R3C3, R5C2, R1C7, etc.

The reference style of each cell can be divided into two categories.

A1 Style Reference

Suppose, the columns are labeled with letters (A, B, C), and the rows are labeled with numbers (1,2,3) right now. So the cell address of the very first cell is A1. Thus, it’s called A1 style reference. The default reference style of Excel is A1 style reference.

Some examples of A1 style references are B5, D9, F12, etc.

R1C1 Reference Style

When both the rows and columns are labeled with numbers, the cell address of the very first cell becomes R1C1. Thus, it is called the R1C1 reference. Style.

Some examples of R1C1 style references are R3C3, R5C2, R1C7, etc.

A1 vs R1C1 Reference Style

In the A1 style reference, the columns are marked as letters (A, B, C), and the rows are marked as numbers (1,2,3). On the other hand, both the rows and columns are marked as numbers (1,2,3) in the R1C1 reference style.

The cell address of the very first cell in an Excel sheet is A1 in A1 style reference.

On the contrary, the cell address of the very first cell in an Excel sheet is R1C1 in the R1C1 style reference.

A1 vs R1C1 Reference Style in Formulas

In A1 style reference, a cell number is just the combination of the column number and the row number.

But in the R1C1 reference style, a cell address is measured as the distance of the cell from an active cell in formulas. That’s why a cell address is temporary in the R1C1 reference style.

Consider the formula in the following case.

=R[-2]C[-1]*(1+R[-3]C[-1])

The amount of capital, $50,000 is in column 2, row 3. The formula is applied in column 3, row 5. Thus, from the active cell, if you move 1 column backward, and two rows up, you will find the cell having $50,000. That’s why in the formula R[-2]C[-1] is used to refer to $50,000.

So, while writing cell references in formulas using the R1C1 reference style, only distance is used. As distance can vary for a particular cell from an active cell, a cell address of a particular cell is not permanent. It just depends on the distance.

Cell Address Notation in Formulas Using R1C1 Reference Style

From an active cell,

If you move to the right one, two, or three columns, use C[1], C[2], and [C3] respectively.

To move to the left use C[-1], C[-2], and C[-3] for one, two, and three columns respectively.
Use R[1], R[2], and R[3], if you move one, two, or three rows down.
Use R[-1], R[-2], and R[-3], if you move one, two, or three columns up.

Relative, Absolute, & Mixed Cell References in A1 & R1C1 Reference Style

There are 3 types of cell references both in A1 and R1C1 reference styles.

Relative Cell Reference

In relative cell reference, both the row and column numbers vary when you drag down a formula.

In the A1 style reference, an example of a relative cell reference is D2. Currently, it refers to column D and row 2. When you use this relative cell reference in a formula and copy it down, it changes.

In the R1C1 reference style, a square bracket, [] is used to indicate a relative cell reference. An example of a relative cell reference is R[-8]C[-6]. Here, it means from the active cell, the very first cell of your worksheet is located 6 columns up and 8 rows left.

When the active cell changes its location, the distance also changes. This means the same cell may have a different cell address in the R1C1 reference style.

Absolute Cell Reference

In the A1 style reference, an absolute cell reference refers to a locked cell reference. An example of an absolute cell reference is $A$1. Dollar Sign ($) is used to lock up both the column and row number. This type of cell reference doesn’t change when you drag down a formula.

In the R1C1 reference style, R5C4 is an example of an absolute cell reference. Here, R5 means the 5th row and C4 means the 4th column. As it’s referring to a specific cell, this type of cell reference is called absolute cell reference.

Mixed Cell Reference

A mixed cell reference is a mixture of both absolute and relative cell references.

In the A1 style reference, an example of a mixed cell reference is $C3. Here, a dollar sign ($) is put before the column number. Thus, the column is locked up and it’s an absolute cell reference. But the row is free to change. So it’s a relative cell reference. As a result, a mixture of relative and absolute cell references in $C3 makes it a mixed cell reference.

In the R1C1 reference style, an example of a mixed cell reference is R[-8]C1. Here, the square bracket is used with the row number. This R[-8] is a relative cell reference. But C1 refers to a specific column which is 1. So it’s an absolute cell reference. Hence, the existence of both the relative and absolute type of cell reference in R[-8]C1 makes it a mixed type of cell reference.

Relative, Absolute, and Mixed Cell Reference Comparative View

Cell Reference Type	A1 Style Reference	R1C1 Style Reference
Relative Cell Reference	A1	R[-8]C[-6]
Absolute Cell Reference	$A$1	R1C1
Mix Cell Reference	$A1	R[-8]C1
Mix Cell Reference	A$1	R1C[-6]

Change Reference Style from A1 to R1C1 in Excel

Some people prefer the A1 style reference and some prefer the R1C1 reference style. However, the default reference style is A1. If you want to change the reference style from A1 to R1C1, follow these steps:

Go to the File tab.
Select Options.
Select Formulas from the left-most column in the Excel Options dialog box.

Check the R1C1 reference style below the ‘Working with formulas’ title bar.
Click OK.

This will switch to R1C1 reference style from A1 style reference.

Change Reference Style from R1C1 to A1 in Excel

To go back to the default reference style, A1 from the R1C1 reference style. Here are the steps below:

Go to File > Options > Formulas.

Uncheck the R1C1 reference style.
Click OK.

Now you will go back to the default A1 style reference straightway.

[VBA Script] Toggle Between A1 & R1C1 Reference Style in Excel

To switch between A1 & R1C1 reference styles swiftly, you can use VBA scripts. So, go through these steps below:

Go to the Visual Basic Editor and paste the following VBA code there.If you don’t know how the use a VBA script, read A Complete Guideline to Insert and Run VBA Code in Excel.
```
Sub Toggle_Reference_Style()

If Application.ReferenceStyle = xlA1 Then
Application.ReferenceStyle = xlR1C1
Else
Application.ReferenceStyle = xlA1
End If

End Sub
```
Save the code.

Select Developer > Macros in your Excel worksheet.
If you don’t see the Developer tab in the ribbon, read 2 Ways to Add Developer Tab in Excel.
You can also press ALT + F8 to open the Macro dialog box.
Click Run.

When the code runs, it changes the reference style from A1 to R1C1. If the current reference style is R1C1, then it will change from R1C1 to A1 straightway.

Create a Command Button to Toggle Between A1 & R1C1 Reference Style in Excel

If you need to switch between A1 and R1C1 reference styles too frequently, running the VBA code each time manually can be time-consuming. Thus, you can create a command button in your worksheet. Using the command button, you can toggle between A1 to R1C1 swiftly. To create the command button, go through the steps below:

Go to the Developer tab > Insert group.
If you don’t have the Developer tab, watch How to Add & Remove Developer Tab in the Main Ribbon in Excel.
Select Command Button from ActiveX Controls.
Drag your mouse and drag a command button in your worksheet.

Now, double-click on the command button. It will take you to the Visual Basic Editor.
You will see the following piece of code there.

Insert the following VBA script inside the two lines of the VBA code.

If Application.ReferenceStyle = xlA1 Then
Application.ReferenceStyle = xlR1C1
Else
Application.ReferenceStyle = xlA1
End If

Code in VBA window

To change the title of the command button, edit the Caption in the Properties – CommandButton1 dialog box.
Save the piece of code.

Now, select Developer > Design Mode in your worksheet to exit from the Design Mode.

Now just click on the command button. It will change the reference style from A1 to R1C1.
If you click again on the command, it will restore the A1 style reference again.

Now, you can continue clicking on the command button, ToggleReferenceStyle. This is how you can toggle between A1 and R1C1 reference styles quickly.

Conclusion

Here, I discussed two types of reference styles, A1 and R1C1 in Excel. I also covered various relevant topics regarding A1 and R1C1 reference styles. I hope you’ve found this article useful. You can read more articles relating to Excel from the Blog page of our website. Have a fantastic workday!

Frequently Asked Questions

What is the cell reference format in Excel?

In Excel, cell references are expressed in a combination of the column letter and row number. The format is as follows:

Relative Reference: Example: A1, B2, C3
Changes when you copy the formula to another cell.
Absolute Reference: Example: $A$1, $B$2, $C$3
Does not change when you copy the formula to another cell.
Mixed Reference: Example: $A1, B$2
Either the column or row is absolute, while the other is relative.

Understanding these formats is crucial for creating effective and dynamic formulas in Excel.

How do I fill a reference cell in Excel?

To fill a reference cell in Excel, follow these steps:

Click on the cell you want to use as a reference.

Use the keyboard shortcut CTRL+C (Windows) or Command+C (Mac) to copy the cell.
Go to the cell or range where you want to fill the reference.

Use the keyboard shortcut CTRL+V (Windows) or Command+V (Mac) to paste the reference into the new location.

This method is efficient for duplicating content and formulas from one cell to another in Excel. Adjust the destination cell or range as needed, and the reference will update accordingly.

Rate this post