The article will demonstrate how to solve the issue of Excel columns being designated in numbers instead of letters. Excel displays column numbers as letters by default. However, it is possible to use the R1C1 method to show the columns in numbers instead, so column A becomes column 1, column B becomes column 2, and so on. Cell A1 becomes (1, 1) or cell R1C1.
Suppose R1C1 is enabled in your copy of Excel like in the image below.
Although this is not a problem if you are familiar with this format, it may still be a challenging to understand the cell referencing. Obviously, the general format of cell references (columns number as letters) will not work, causing a #NAME? (Invalid Name Error) error.
In the image above, we used the SUM function to sum the natural numbers by referencing the cells in the traditional method. But we received an error because the cell reference is not correct.
We can fix the problem by changing the formula to the R1C1 reference format.
All good so far. But if we put cell references by selecting the cells, things begin to get messy.
This type of reference will be difficult to understand for most users. Using the conventional A1 style cell reference would definitely be more suitable for general use. Let’s see how to convert R1C1 style to A1 cell referencing.
Method 1 – Using Excel Options to Convert Column Numbers to Letters
We can easily disable R1C1 format from the Options menu.
Steps:
- Go to the File tab.
- Select Options.
- In the Options window, go to Formulas and then simply uncheck the R1C1 reference style.
- Click OK.
The workbook now has the A1 reference style.
Read More: How to Create Excel Table with Row and Column Headers
Method 2 – Using Excel VBA to Show Column Letters Instead of Numbers
We can also use Visual Basic for Application (VBA) to convert Excel columns from R1C1 reference style to A1 reference style.
Steps:
- Go to the Developer tab and select Visual Basic.
- In the VBA editor that opens, select Insert >> Module.
- In the VBA Module that opens, enter the following code:
Sub ColumnNumbersToLetters()
Application.ReferenceStyle = xlA1
End Sub
The ReferenceStyle property is used to change the reference style from R1C1 to A1.
- Save the code.
- Go back to your sheet and run the Macro.
This operation will return the A1 reference style in your workbook.
Read More: How to Change Column Header Name in Excel VBA
Download Practice Workbook
Related Articles
- How to Create Column Headers in Excel
- How to Title a Column in Excel
- How to Change Column Headings in Excel
- How to Rename Column in Excel
- How to Remove Column Headers in Excel
- How to Remove Column1 and Column2 in Excel
- How to Change Excel Column Name from Number to Alphabet
- How to Repeat Column Headings on Each Page in Excel
<< Go Back to Rows and Columns Headings | Rows and Columns in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
