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.
- 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.
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.
- In Cell C5, write the following formula-
- 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.
2.2 Use SUBSTITUTE and ADDRESS Functions for Any Column
- 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-
➥ SUBSTITUTE(ADDRESS(1,B5, 4), “1”, “”)
The SUBSTITUTE function will replace the row number with empty from the reference and will return as-
Read More: How to Title a Column in Excel
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.
- Type 1 in Cell A1.
- After that, type the following formula in Cell B1–
- 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.
Download Practice Workbook
You can download the free Excel template from here and practice on your own.
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.
- 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
- How to Repeat Column Headings on Each Page in Excel
- How to Remove Column1 and Column2 in Excel