How to Change the Name of an Excel Column from Numerical to Alphabetical (2 Ways)

Last updated: Jun 11, 2024

To demonstrate the methods, we’ll use the following dataset representing some products’ prices. The column names are in numerical order.

Method 1 – Unmark R1C1 Reference Style

Steps:

Click the File option beside the Home tab.

Click on Options.
And a dialog box will open up.

From the Formulas section, unmark the R1C1 reference style.
Press OK.

Excel has changed the column name from number to the default alphabet system.

Method 2 – Use Excel Formula to Convert

2.1 Use the CHAR Function for Single-Letter Columns

Steps:

Enter the following formula in cell C5:

=CHAR(64+B5)

Press the Enter button to get the output.

Drag the Fill Handle icon to copy the formula for the other cells.

You will get all the column names in alphabetical order.

2.2 Use SUBSTITUTE and ADDRESS Functions for Any Column

Steps:

Enter the following formula in cell C5:

=SUBSTITUTE(ADDRESS(1,B5, 4), "1", "")

Press the Enter button to get the corresponding alphabet.

Use the Fill Handle tool to copy the formula.

We have 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