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.
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.
- 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 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.
- 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: How to Name a Group of Cells in Excel (3 Methods +1 Bonus)
Similar Readings
- How to Name a Cell in Excel (4 Easy Ways)
- Dynamic Named in Range Excel (Both One and Two Dimensional)
- How to Paste Range Names in Excel (7 Ways)
- Remove Named Range in Excel (4 Quick Methods)
- How to Edit Named Range in Excel
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: 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.
- 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 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.
