By default, Excel uses letters to denote the columns and digits to denote the rows in any worksheet. However, if you want to use digits to refer to the columns instead of using A, B, C, D, etc., here is how you can do that. You can enable R1C1 reference style in Excel using Excel Options, Registry Editor, and Local Group Policy Editor.
What is the R1C1 reference style in Excel?
Any Excel spreadsheet has mainly two things – rows and columns. Excel, Google Sheets, etc., denote the columns alphabetically, i.e., A, B, C, D, E, and so on. On the other hand, they denote rows by digits, i.e., 1, 2, 3, 4, and so on. However, if you do not want to use letters to specify columns and want to use digits, you need to enable the R1C1 reference style – or Row 1 Column 1 reference style.
How to enable R1C1 reference style in Excel
To enable R1C1 reference style in Excel, follow these steps:
- Open Microsoft Excel on your PC.
- Click on the Options.
- Switch to the Formulas tab.
- Tick the R1C1 reference style checkbox.
- Click the OK button.
To know more about these steps, continue reading.
First, you need to open Microsoft Excel on your computer. Then, click on the Options visible in the bottom-left corner to open the Excel Options panel.
Following that, switch to the Formulas tab and head to the Working with formulas section. Here you can find a setting named R1C1 reference style.
You need to tick this checkbox and click the OK button to save the change.
How to enable R1C1 reference style in Excel using Group Policy
To enable R1C1 reference style in Excel using Group Policy, follow these steps:
- Press Win+R to open the Run prompt.
- Type gpedit.msc and hit the Enter button.
- Go to Microsoft Excel 2016 > Excel Options > Formulas in User Configuration.
- Double-click on the R1C1 reference style setting.
- Choose the Enabled option.
- Click the OK button.
Let’s check out more about these steps.
First, you need to open the Local Group Policy Editor on your computer. For that, press Win+R to open the Run dialog, type gpedit.msc, and hit the Enter button.
Once it is opened on your screen, navigate to the following path:
User Configuration > Administrative Templates > Microsoft Excel 2016 > Excel Options > Formulas
In the Formulas folder, you can find a setting named R1C1 reference style. You need to double-click on this setting and choose the Enabled option.
Then, click the OK button to save the change.
Note: In case you want to revert to the original reference style, you need to double-click on this setting and choose the Not Configured option.
How to enable R1C1 reference style in Excel using Registry
To enable R1C1 reference style in Excel using Registry, follow these steps:
- Press Win+R to open the Run dialog.
- Type regedit and click the OK button.
- Click the Yes button.
- Go to Microsoft\office\16.0 in HKCU.
- Right-click on 16.0 > New > Key and name it as excel.
- Right-click on excel > New > Key and name it as options.
- Right-click on options > New > Key and set the name as binaryoptions.
- Right-click on binaryoptions > New > DWORD (32-bit) Value.
- Set the name as frefa1_4_1.
- Restart your computer.
To learn more about these steps, keep reading.
For getting started, you need to open the Registry Editor. For that, press Win+R to display the Run dialog, type regedit, and click the OK button. Once the UAC prompt appears, click the Yes button.
Then, navigate to the following path:
HKEY_CURRENT_USER\Software\Policies\Microsoft\office\16.0
Right-click n 16.0 > New > Key and name it as excel.
Then, you need to repeat the same steps to create a sub-key under the excel key and name it as options. Following that, do the same and create another sub-key named binaryoptions.
Once the binaryoptions key is ready, right-click on it > New > DWORD (32-bit) Value and set the name as frefa1_4_1.
By default, it comes with a Value data of 0, and you need to keep this Value data in order to enable the R1C1 reference style. Finally, restart your computer to apply the change.
Note: If you want to opt for the original setting, you can delete the REG_DWORD value. For that, right-click on frefa1_4_1, choose the Delete option, and click the Yes button.
Read: How to delete Pivot Tables in Excel
How do I enable R1C1 in Excel?
As said earlier, you can enable R1C1 in Excel in three different ways. You can use the Excel Options panel, Local Group Policy Editor, and Registry Editor. In the Excel Options panel, go to the Formulas tab and head to the Working with formulas section. Then, tick the R1C1 reference style checkbox to enable this setting.
How do you change the reference style in R1C1?
To change the reference style in R1C1 in Excel, you can follow these aforementioned steps. First, open the Local Group Policy Editor and go to the Formulas section in User Configuration. Then, double-click on the R1C1 reference style setting and choose the Enabled option. Finally, click the OK button to save the change.
Read: How to get real-time Currency Exchange Rates in Excel sheet.
