How to Change Excel Column Name from Number to Alphabet (2 Ways)

The column names in Excel remain in alphabetical order by default. But it can be set to numerical order too. So if you somehow get that the column name is in numerical order then you can get back to the default alphabetical order easily. In this article, I’ll show 2 quick methods to change column name in Excel from number to alphabet with sharp steps.


Download Practice Workbook

You can download the free Excel template from here and practice on your own.


2 Ways to Change Column Name from Number to Alphabet in Excel

To demonstrate the methods, we’ll use the following dataset that represents some products’ prices. Have a look that the column name is in numerical order here. Now go forward to explore the methods.


1. Unmark R1C1 Reference Style to Change Excel Column Name from Number to Alphabet

First of all, we’ll use the built-in preference option to change column names in excel from number to alphabet. It’s quite easy.

Steps:

  • Click the File option beside the Home tab.

Unmark R1C1 Reference Style to Change Column Name in Excel from Number to Alphabet

  • Then click on Options.

And a dialog box will open up.

Unmark R1C1 Reference Style to Change Column Name in Excel from Number to Alphabet

  • Later, from the Formulas section, unmark the R1C1 reference style.
  • Finally, just press OK.

Unmark R1C1 Reference Style to Change Column Name in Excel from Number to Alphabet

Now you see, Excel has changed the column name from number to the default alphabet system.

Read More: How to Edit Defined Names in Excel (Step-by-Step Guideline)


2. Use Excel Formula to Convert Column Number into Alphabet

Here, we’ll use a formula to convert column numbers to the alphabet. There are two ways to do it.

2.1 Use CHAR Function for Single-Letter Columns

First, we’ll learn to convert the column number to the alphabet using the CHAR function. But it is applicable for only single-letter columns.

Steps:

  • In Cell C5, write the following formula-
=CHAR(64+B5)
  • Then press the Enter button to get the output.

Use Formula to Convert Column Number into Alphabe

  • Finally, drag down the Fill Handle icon to copy the formula for the other cells.

Use Formula to Convert Column Number into Alphabe

Then you will get all the column names in alphabetical order.

Use Formula to Convert Column Number into Alphabe

Read More: How to Name a Group of Cells in Excel (3 Methods +1 Bonus)


Similar Readings


2.2 Use SUBSTITUTE and ADDRESS Functions for Any Column

Now we’ll use the SUBSTITUTE and ADDRESS functions to covert any column number to the alphabet.

Steps:

  • Type the following formula in Cell C5
=SUBSTITUTE(ADDRESS(1,B5, 4), "1", "")
  • Later, just press the Enter button and you will get the corresponding alphabet.

Use Formula to Convert Column Number into Alphabe

  • Finally, use the Fill Handle tool to copy the formula.

Now see, I have got all the column names in the alphabet according to my given column numbers.

💭 Formula Breakdown:

➥ ADDRESS(1,B5, 4)
The ADDRESS function will return the cell reference according to row number 1 and column number 1. So it will return as-
“A1”

➥ SUBSTITUTE(ADDRESS(1,B5, 4), “1”, “”)
The SUBSTITUTE function will replace the row number with empty from the reference and will return as-
“A”

Read More: How to Name a Range in Excel (5 Easy Tricks)


Manually Change Excel Column Name from Number to Alphabet

Here, Instead of using the Excel preference option, I’ll describe a manual way to get the column names in numbers just below the column names.

Steps:

  • Type 1 in Cell A1.

Manually Change Column Name in Excel from Number to Alphabet

  • After that, type the following formula in Cell B1
=A1+1
  • Then press the Enter button.

Manually Change Column Name in Excel from Number to Alphabet

  • Finally, drag the Fill Handle icon in the right direction to get the other column numbers.

Here, I dragged till Column E.

Read More: How to Use Named Range in Excel VBA (2 Ways)


Conclusion

I hope the procedures described above will be good enough to change column name in Excel from number to alphabet. Feel free to ask any question in the comment section and please give me feedback.


Related Articles

Mithun

Mithun

Hello! I am Md. Sourov Hossain Mithun. Welcome to my profile. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is amazing software. Here I will post excel related useful articles. I am a graduate of Bangladesh University of Engineering and Technology. I love to learn new things and work with them. Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo