How to Convert Column Number to Letter in Excel (3 Methods)

Method 1 – Convert Column Number to Letter in Excel

Consider the following dataset which we will be using as our example to convert column number to letter by applying the formula.

• Select a Cell:
  • Choose the cell where you want the result to appear.

• Formula:
  • The generic formula to convert a column number to a letter is as follows:

• In your specific cell, enter this formula:

Here,

B5 – represents the cell reference containing the column number you want to convert.

• Press Enter:
  • After entering the formula, press Enter.

• Result:
  • You’ll get the associated letter address (e.g., A) corresponding to the column number (e.g., 1) in your dataset.

• Apply to Other Cells:
  • Drag the formula down using the Fill Handle to convert other column numbers into letters.

Method 2 – VBA to Convert Column Number to Letter in Excel

• Open Visual Basic Editor:
  • Press Alt + F11 on your keyboard or go to the Developer tab and click Visual Basic to open the Visual Basic Editor.

• Create a User-Defined Function (UDF):

• • In the code window, click Insert and choose Module.

• • Copy and paste the following code:

Public Function NumToLetter(ColNum)
NumToLetter = Split(Cells(1, ColNum).Address, "$")(1)
End Function

• • Save the code (instead of running it).

• Use the UDF:
  • Return to your worksheet.
  • In a cell, enter the formula:

=NumToLetter(B5)

(Replace B5 with the cell reference containing the column number you want to convert.)

• • Press Enter to get the associated letter address (e.g., A).

• Apply to Other Cells:
  • Drag the formula down using the Fill Handle to convert other column numbers into letters.

Read More: VBA to Convert Column Number to Letter in Excel

Method 3 – Excel’s Built-in Option 

Excel has a built-in option to change the column number (shown below in the picture) to letter.

Steps:

• Click the File tab and select Options.

• In the Excel Options window, choose Formulas.
• Uncheck the R1C1 reference style box and click OK.

Your columns will now display letter addresses instead of numbers.

Read More: Column Letter to Number Converter in Excel

Download Workbook

You can download the practice workbook from here:

Related Articles

• How to Find Column Index Number in Excel
• How to Return Column Number of Match in Excel 
• How to Find Column Number Based on Value in Excel
• How to Change Column Name from ABC to 1 2 3 in Excel
• Find Value in Row and Return Column Number Using VBA in Excel
• How to Use VBA Range Based on Column Number in Excel

<< Go Back to Learn Excel