Two types of cell references in Excel. A1 is the default reference style. Most Excel users do not know about the existence of the R1C1 notation in the worksheet and may not have much use. But it is still a good idea to understand how both these notations work, an understanding of these notations will be very useful for you to work with VBA coding.

In this article, you will learn about A1 and R1C1 reference notation in Excel.

A1 Reference style

In the Microsoft Excel spreadsheet, the A1 notation is the common reference style. It is a combination of both the column letter and row number and is referred to as either the cell reference or cell address. This combination of column and row is used to point to a specific cell in the worksheet and then used to calculate formulas or enter data.

An example of A1 notation is B5, which references the cell located in the second column, and fifth row of the spreadsheet. This type of notation is useful when you need to reference a particular cell in a formula or when you need to read data from a specific cell.

In the A1 reference style, you have the column name as an alphabet and the row name as a number and when you select the B5 cell that means you are in Column B and Row 5.

Since A1 is the default reference style notation, if you type the equal to sign in a cell, and then select any cell in the worksheet. You can see the reference of that cell appear in the current cell.

R1C1 Reference Style

In the R1C1 reference style, both the row and column of the cell reference in Excel are expressed in numbers. It uses the combination of letters and numbers to represent the cell location or range of cells on a worksheet. The letter R stands for the row and the letter C stands for the column, and the numbers after the letters are the row and column numbers.

The cell reference uses a row number and column number in the R1C1 reference style. It gives you the relative position of that reference from the current cell. This is also known as the Relative Notation.

When you select cell E3 in the R1C1 reference style, it shows you R3C5. It means that you selected the third row and fifth column of the spreadsheet.

You can use the range operator colon : to identify a range of cells. For example, =R1C1:R5C3 would refer to the range of cells from row 1, column 1 to row 5, column 3.

if you refer to a cell in R1C1, it creates the address of referred cell using its distance from the current cell.

Example, if you refer the cell E6 in cell A1 it will show the address R[5]C[4]

Cell E6 is 5 rows below and 4 columns right of cell A1.  R1C1 cell references are displayed using Row and Column offset values. so, if you refer to the same cell E6 from a different cell then its address will be different.

The numbers in the square brackets show how many rows or columns the cell is relative to itself. You can also use the negative numbers in these square brackets. Negative row numbers referred to the cell above the active cell. Negative column numbers referred to the cell to the left of the active cell.

For example: if you use =R[-2]C[-2], it would refer to the cell two rows above and two columns to the left.

You have Absolute and Relative references in the R1C1 notation like in the A1 notation.
Relative References is the default one and they include square brackets around the numbers. Absolute References is straightforward and do not use square brackets. You just use R3C3 to refer the cell C3. You can use this reference anywhere in your worksheet it will always refer to the cell C3. You cannot use 0 and Negative numbers in the absolute references.

Change in Options Menu

Excel already has the A1 reference style enabled. First, you need to activate the R1C1 notation in Excel to use. you can quickly change between the two reference styles using the below steps.

Please follow the below steps to change from the A1 reference style to the R1C1 reference style.

1. Go to the File tab
2. Click the Options, to open the Excel Options dialog box

3. Click the Formulas button in the left side option list
4. Check the R1C1 reference style checkbox in the Working with formulas section
5. Press the OK button

The above steps enable the R1C1 reference style. It will change only the view that does not change the formulas. you can see that the row and column headers are shown numbers in the R1C1 format in Excel.

Follow the same steps and uncheck the R1C1 reference style checkbox in Step 4 to switch back to the A1 reference style.

Change in Quick Access Toolbar in Excel

The Quick Access Toolbar is a customizable toolbar located above the ribbon. It contains frequently used commands. You have the option to move QAT below the ribbon.

In Excel, QAT has Save, Undo, and Redo commands by default. If you use a few other commands frequently, you can add them to the Quick Access Toolbar.

QAT has a drop-down menu containing a predefined set of commands. Displays a list of more commands if you click the down arrow at the end of the QAT. You have the option to add any of these commands to the quick access toolbar. Also, you can customize it such as Add your own commands, Change the order of commands, add macros, and Export and import your customizations.

Steps to add Excel Option to the Quick Access Toolbar in Excel

1. Go to the File Tab
2. Click the Options, to open the Excel Options dialog box

3. Click the Quick Access Toolbar button from the left side list
4. Choose File Tab in the Choose command from the list item
5. Select the Options in the list of command
6. Press the Add >>  to add to the QAT
7. Press the OK button

 Remove a Command from Quick Access Toolbar

Sometimes you do not need the added command in QAT and need to remove it. It will be very easy to remove the command from the Quick Access Toolbar.

Option 1:

To remove the command from the QAT, click the Right mouse button on the command and select Remove from Quick Access Toolbar from the pop-up menu.

Option 2:

Steps to remove the command from QAT

1. Click the Right mouse button on the command
2. Select Customize Quick Access Toolbar from the pop-up menu

3. Select the command Options.
4. Click the << Remove button.
5. Press the OK button.

Change with Address function in Excel

The Excel ADDRESS function is working with cell references. You can dynamically generate cell references based on rows and columns using it. The function is very useful to create complex dynamic formulas that reference cells also combine with other functions, such as INDEX, MATCH, and INDIRECT.

ADDRESS (rowNo, columnNo, [absNo], [a1], [sheetName])  

rowNo – Integer value

columnNo – Integer value

absNo – Optional; Default Absolute $A$1; 1 for Absolute $A$1, 2 mixed reference for A$1; 3 mixed reference for $A1, and 4 for relative A1.

a1 – Optional; Default A1 style; If the parameter set to TRUE, it uses the A1 reference style A1, B1, etc. If it is FALSE uses the R1C1 reference style R1C1, R1C2, etc.

sheetName – Optional; Sheet name for address to refer to a different sheet.

The ADDRESS function returns a text string, which is the cell reference based on the input arguments.

Example 1, the formula =ADDRESS (3, 3) returns the text string $C$3, which is the address of the cell in column C and row 3.

Example 2:  Use the R1C1 reference style in the address function to display the relative column.

Type the formula =ADDRESS (3, 3, 2, FALSE) in cell B1, and then Press Enter. You can see the ADDRESS function displays the relative column in reference style.

Change with a VBA Code

XLReferenceStyle helps you to navigate easily through your worksheet and perform calculations. You have two options to set the reference style xlA1 and xlR1C1.

Application.ReferenceStyle is a property to set how Excel displays cell references and row & column headings in either A1 reference style or R1C1 reference style.

This property returns xlA1 by default. Set xlA1 to return an A1 Notation or set xlR1C1 to return the R1C1 Notation.

Sub SwitchReferenceStyle()

' Check the reference style xlA1 or not
If Application.ReferenceStyle = xlA1 Then

    ' Set the reference style xlR1C1, if the condition is TRUE
    Application.ReferenceStyle = xlR1C1

Else
    
    ' Set the reference style xlA1, if the condition is FALSE
    Application.ReferenceStyle = xlA1

End If

End Sub

VBA macro to toggle between the cell reference styles. Add the above script to your module in the visual basic editor.

VBA macro first checks the Reference Style, which is xlA1 notation or not. If it is TRUE set the R1C1 reference style xlR1C1 to the ReferenceStyle property. If it is FALSE set the A1 reference style xlA1 to the ReferenceStyle property.

To Run the SwitchReferenceStyle macro in Excel

Press the keyboard shortcut key Alt + F8, to open the Macro dialog box, Select the SwitchReferenceStyle Macro in the Macro list, and then Press Run.

Once you run the macro, the Excel reference style and the formulas in all the workbooks will change to the respective style.  

Sometimes, you may need to use the R1C1 reference style in VBA.  you have to understand and use the FormulaR1C1 cell property to use the R1C1 reference style in VBA.

For example: Suppose you want to enter a formula in the active cell. The formula sums up the Rent and Salary, which are on the above active cell.

Sub R1C1Notation()
    ' Sum the above two cell values
    ActiveCell.Formula2R1C1 = "R[-2]C+R[-1]C"
End Sub

In the above macro, you are summing up the Rent and Salary. Rent is two rows above the active cell and Salary is one row above the active cell.

You do not need to change anything in the script or formula when you move the table anywhere in the Excel worksheet.

Conclusion

Excel default A1 Reference style is easy to use, all the Excel users are using it. R1C1 reference style will be useful in VBA programming to loop through column data.

You can use the keyboard function key F4 to change between relative, absolute, and semi-relative cell references.

In the R1C1 notation, R stands for a row and C stands for a column. You can use a positive number in the square brackets to refer to the rows below and the columns right of the active cell. Use a negative number to refer to the rows above and the column left the active cell.

You need to use the FormulaR1C1 property in Excel VBA to use R1C1. You have found this post helpful and share your views with me in the comment section at the bottom of this page.