Home » Tech Tips » Productivity » How to Show Numbers Instead of Letters in Excel Columns?

How to Show Numbers Instead of Letters in Excel Columns?

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.

Excel Columns with Letter Reference
Excel Columns with Letter Reference

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.

R1C1 Cell Reference with Numbers for Columns
R1C1 Cell Reference with Numbers for Columns

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”.
Open Excel Options in Windows
Open Excel Options in Windows
  • 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.
Enable R1C1 Reference Style in Windows Excel
Enable R1C1 Reference Style in Windows Excel

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…”.
Open Excel Preferences in Mac
Open Excel Preferences in Mac
  • When you are in Excel Preferences dialog box, click “Calculation” option showing under “Formulas and Lists” section.
Open Excel Calculation Settings in Mac
Open Excel Calculation Settings in Mac
  • Check “Use R1C1 reference style” showing as a last option under “Calculation Options” section.
Enable R1C1 Reference Style Excel Mac
Enable R1C1 Reference Style Excel Mac
  • 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.
Excel Shows Numbers for Columns in Mac
Excel Shows Numbers for Columns in Mac

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.

Using R1C1 in Formula
Using R1C1 in Formula

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.

Different Cell Reference Format in Excel
Different Cell Reference Format in Excel

Leave a Comment

Your email address will not be published. Required fields are marked *