Md. Sourov Hossain Mithun
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.
How to Change Excel Column Name from Number to Alphabet: 2 Ways
To demonstrate the methods, we’ll use the following dataset that represents some products’ prices. Have a look that the column name 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.
- Then click on Options.
And a dialog box will open up.
- Later, from the Formulas section, unmark the R1C1 reference style.
- Finally, just press OK.
Now you see, Excel has changed the column name from number to the default alphabet system.
Read More: How to Change Column Header Name in Excel VBA
Similar Readings:
- How to Create Column Headers in Excel
- How to Change Column Headings in Excel
- How to Rename Column in Excel
- How to Remove Column Headers in Excel
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.
- Finally, drag down the Fill Handle icon to copy the formula for the other cells.
Then you will get all the column names in alphabetical order.
Read More: [Fixed] Excel Column Numbers Instead of Letters
Similar Readings
- How to Promote a Row to a Column Header in Excel
- How to Make Multiple Sortable Headings in Excel
- How to Make First Row as Header in Excel
- How to Keep Row Headings in Excel When Scrolling
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.
- 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:
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.
- After that, type the following formula in Cell B1–
=A1+1- Then press the Enter button.
- 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 Create Excel Table with Row and Column Headers
Download Practice Workbook
You can download the free Excel template from here and practice on your own.
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.
