By default, Microsoft Excel uses numbers for rows and letters for columns in spreadsheets. This makes all cell references based on columns without the need of mentioning rows. For example, cell C5 is a fifth cell in column C and you do not need to mention the row 5 in formulas. However, sometimes you may want to refer a cell based on both row and column like R5C3 (which is same as C5). This will be useful when using complex formulas to refer a cell easily. In this case, you need to change the default Excel setting to show numbers for column reference instead of letters.
Letters and Numbers for Columns
Whenever you click on a cell, Excel will show the cell reference in a small text box showing next to formula bar. Below is the default display of how Excel shows a cell reference based on a column’s letter like C5.
And below is the changed setup where Excel shows all column references in numbers. Now, you can see the cell reference box will show as R5C3 (row 5 column 3) format instead of C5.
Here, I will show how to change this with Excel app in Microsoft 365 Windows and Mac versions.
Show Numbers for Columns in Excel Windows
Follow the below steps in Microsoft Excel Windows version.
- Open Excel app, go to “File” menu and select “Options”.
- When you are in Excel Options dialog, go to “Formulas” section from left sidebar.
- Check the option that says “R1C1 reference style” under “Working with formulas” section.
- Click “OK” button to close the dialog box and you should see Excel now shows numbers for columns instead of letters.
As mentioned, R1C1 cell reference indicates row 1 and column 1 instead of using C1.
Show Numbers for Columns in Excel Mac
Follow the below steps if you are using Excel app in Mac as part of Microsoft 365 subscription.
- Open Excel app and press “Command + Comma” shortcut keys. Alternatively, go to “Microsoft Excel” menu from top bar and select “Preferences…”.
- When you are in Excel Preferences dialog box, click “Calculation” option showing under “Formulas and Lists” section.
- Check “Use R1C1 reference style” showing as a last option under “Calculation Options” section.
- Close the preferences dialog and you will see Excel displays numbers for columns instead of letters. And the cell reference box shows in R1C1 format instead of C1 format.
The change is permanent and Excel will always show the columns in number if you have selected R1C1 reference style. You can anytime go back to Excel Options in Windows or Excel Preferences in Mac to change the setting.
Using R1Cs Cell Reference in Excel Formulas
With numbers in columns, you can simply use R1C1 references in formulas like =R3C2+R3C3. You can find the same formula appears in the formula bar.
However, Excel will show cell reference in different format when you select the cell instead of manually entering the cell reference. For example, you can see =R[-3]C[-2]+R[-3]C[-1] in the formula bar instead of =R3C2+R3C3 as I have selected the cells for addition. So, make sure to understand how it works in Excel formulas before you change the columns display to numbers.