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.

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

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-

• 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

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–

• 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 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.

Steps:

• 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.

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.

Related Articles

• 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

<< Go Back to Rows and Columns Headings | Rows and Columns in Excel | Learn Excel