Usually, the column names in Excel remain in alphabetical form (A, B, C) by default, but you can change it to numerical order (1,2,3) too. And sometimes you may need to change the Excel column name from ABC to 1 2 3. In this article, I will show you how to change the Excel column name from ABC to 1 2 3.
How to Change Column Name from ABC to 1 2 3 in Excel: 2 Easy Ways
In this section, I will show you 2 quick ways for changing the column names from ABC to 1 2 3 in your Excel workbook. Let’s check them now!
1. Mark R1C1 Reference Style to Change Column Name
Let’s say, we have got a dataset of sales of the sales assistants of a shop over a certain period of time.
Give a look, the column headings here are in alphabetical order. If you want to change them into numerical order, just follow the steps below:
- First of all, go to the File tab on the ribbon.
- Then, click Options at the last of the menu list.
- Now, a dialogue box will open. Click on Formulas section> Mark R1C1 reference style. Press OK.
- After that, you will see Excel has changed the column headings from alphabetical to numerical order. The reference style will show up in the left corner.
So easy, isn’t it? You can now change the column headings from ABC to 123 by marking R1C1 reference style in the blink of an eye!
2. Using VBA Macro to Change Excel Column Name
Let’s say, now you want to convert column headings from A B C to 1 2 3 by using VBA macros.
For performing this task using VBA macros, let’s start the procedure:
- First of all, open a new worksheet and press ALT+F11.
- Now, the Visual Basic Editor window will show up.
- After that, from the top of the left corner of this window, right-click on the workbook name (i.e. Sheet 3) >choose Insert > click Module from the options.
- Then, copy the VBA code from where you want and paste it to the module appeared. For converting column names from ABC to 1 2 3, you can use the following:
Sub ColumnNames123()
Application.ReferenceStyle = xlR1C1
End Sub
- Now, save this file as “Excel macro-enabled Workbook” and press ALT+Q to close this window.
- And now, press ALT+F8 and Macro dialogue box will show up. Select ColumnNames123 from the Macro name box and click Run to allow the code run.
- Finally, you will get your column headings changed to 1 2 3 instead of A B C.
By following this way, you can change the column headings of your Excel workbook from A B C to 1 2 3.
Read More: Find Value in Row and Return Column Number Using VBA in Excel
Alternative Method to Change Column Name to 123
We’ll now use ADDRESS, MATCH, and COLUMN functions to change column names from alphabetical to numerical form. For this, just follow the steps below:
- First of all, type the following formula in the cell C5
=MATCH(B5&"1",ADDRESS(1,COLUMN($1:$1),4),0)
Formula Breakdown
»ADDRESS(1,COLUMN($1:$1),4)
Here,
- 1= Row number
- COLUMN($1:$1)= Sequence of column numbers
- 4= Relative reference
So, the ADDRESS function returns this array:
{“A1”, “B1”, “C1”, “D1”,….., “XFD1”}
»MATCH(B5&”1”,{“A1”, “B1”, “C1”, “D1”,….. “XFD1”},0)
- B5= A (Value of Cell B5)
- 1= Row number
So, the string becomes A1
»MATCH(A1,{“A1”, “B1”, “C1”, “D1”,….. “XFD1”},0)
And finally, the MATCH function searches the string A1 in the above array and returns the position of the found value (column number).
- Now, press ENTER and you will get the corresponding column number.
- After that, use Autofill to drag the formula to the rest of the cells and you will get the output.
In this way, you can change the column letter to the column number.
Read More: How to Find Column Index Number in Excel
Download Practice Workbook
Conclusion
In this article, we have learned how to change column names in Excel. I hope from now on, you can quickly change the names of your columns in an Excel worksheet. If you have any better methods or questions or feedback regarding this article, please share them in the comment box. This will help me enrich my upcoming articles.
