3 Methods to Convert Column Number to Letter in Excel

Usually, if you open an Excel workbook, the column headers are supposed to stay alphabetically. In case the columns are in numbers in your worksheet, here are 3 methods to convert a column number to a column letter in Microsoft Excel.

Here’s the Excel file that I’ve used throughout the whole blog. You can download it from the following link:

Convert Column Number to Column Letter in Excel.xlsx

Introduction to the Dataset

But before moving to the solutions, let me introduce you to the dataset that I used for this blog. I have used a simple dataset where I’ve put some numbers (column: Number) in accordance with the serial of the alphabet (column: Letter).

Quickest Way to Convert Excel Column Number To Letter

You can see the column headers are in numbers. I am going to use the built-in option of Microsoft Excel to change the column header numbers to column letters.

Let’s do that!

Now Follow The Guide

Step_1: Click on the File menu.

Step_2: Select Options.

The Excel Options dialog box will pop up.

Step_3: Select Formulas.

Step_4: From the Working with Formulas group, unmark the R1C1 Reference Style checkbox.

Step_5: Hit OK.

Final Result

Now you will find the column headers in ABCD forms.

Note:

Let’s say, you had another workbook open while working with your current workbook, you would see the other workbook had also switched its reference style into lettered column headers.

To avoid this problem, I would suggest, opening the workbooks one by one. Or, you can again change the settings in the Options into the R1C1 reference style.

🔗 3 Ways to Find Column Index Number in Excel

🔗 2 Ways to Count Columns in Excel for Vlookup

Alternative Way #1: Use ADDRESS Function to Convert Column Number to Letter

This formula is applicable for any column number. It has no limitation on column counting. So it’s very useful.

Formula Syntax

=ADDRESS(row_number, column_number, [absolute_num], [a1], [sheet text])

Usage Guide

Step_1: Type this formula in a blank cell:

=SUBSTITUTE(ADDRESS(1,RC[-1], 4), "1", "")

Formula Explanation

The ADDRESS function requires a row number and a column number as input. It merges the input values and returns a cell address as an output. Inside the ADDRESS function,

• 1 is the row number. Then RC[-1] is the column number.
• RC[-1]: Here, R refers to the same row as the active cell which is row 1. Then C[-1] refers to the immediate left column of the active cell which is column 1.
• 4 is the serial number of Relative Cell Reference.

Thus, ADDRESS(1,RC[-1], 4) returns A1.

Then the SUBSTITUTE function requires a text. Where it replaces an old text with a new text.

• The ADDRESS function returns A1. This A1 is the input text for the SUBSTITUTE function.
• “1” is the old text here.
• “” is the new text.

Thus, the SUBSTITUTE function replaces 1 with a null string. So A1 becomes A.

Step_2: Press ENTER.

Step_3: Select the whole column.

Step_4: Press CTRL + D.

This will copy down the formula applied in the top cell of the column, Letter.

Final Result

Input numbers are converted into column letters. Take a look.

🔗 [2 Cases] Count Columns in Excel Until a Certain Value Reached

🔗 Count Rows in Excel Until a Certain Value Reached [2 Cases]

Alternative Way #2: Use CHAR Function to Convert Column Number to Letter

Before moving into this method, let me notify you that this technique is only applicable if you are looking for a column number that is within range of the 26 alphabets. Not applicable for header numbers above 26.

Formula Syntax

=CHAR(64 + column_number)

Usage Guide

Step_1: Type this formula in an empty cell:

=CHAR(64+RC[-1])

Step_2: Press ENTER key.

Formula Explanation

The CHAR function requires a number. Then it converts the number to its corresponding character.

• RC[-1]: R refers to the same row as the active cell which is 1. C[-1] refers to the immediate left column of the active cell’s column; which is column 1. Thus RC[-1] refers to the first cell of the column, Number; which contains the number 1.
• 64: The CHAR function receives numbers from 1 to 26. When 64 is added to the range (1 to 26) we get a range of 65 to 90. Here. 65 is the ASCII equivalent number of the letter ‘A’. 90 is the ASCII equivalent number of the letter ‘Z’.

Thus, the CHAR function converts 1 to 26 as ‘A’ to ‘Z’.

Step_3: Select the entire column.

Step_4: Now press CTRL + D.

This will instantly copy down the formula applied in the top cell with changing cell reference.

Final Result

All the numbers from 1 to 26 have been converted into column letters. Have a look:

In case you try a column number above 26, Excel will give you a result based on the ASCII coding system.

Conclusion

I hope now you have a clear idea about converting column headers numbers and column letters, and can solve any problems regarding this topic. Please feel free to reach out to us, if you have any confusion regarding this article.

🔗 6 Ways to Count One Column in Excel If Another Column Meets Criteria

🔗 [Excel VBA] Count Columns & Rows Having Data (10 Editable Codes)