How to Change Column Name from ABC to 1 2 3 in Excel

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.


Download Practice Workbook


2 Easy Ways to Change Column Name from ABC to 123 in Excel

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.

Dataset for Changing Column Name from ABC to 123

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.

Changing Column Name from ABC to 1 2 3

  • Then, click Options at the last of the menu list.

Change the Column Name to 123 from ABC

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

Change Column Name from ABC to 123

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!

Read More: How to Reference Cell by Row and Column Number in Excel (4 Methods)


Similar Readings


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.

Changing the Column Name from ABC to 1 2 3

  • 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

Change Column Name from ABC to 1 2 3

  • 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: VBA to Convert Column Number to Letter in Excel (3 Methods)


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.


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. For more queries, kindly visit our website ExcelDemy.


Related Articles

Rafi

Rafi

Hey there! I am Md. Rafiul Hasan. Currently I am working as an Excel & VBA content developer. I like new ideas and want to explore the field of innovation. Excel has saved our worktime and made it easy for us to quick calculations. I am trying to make it easier for you to overcome the obstacles you face while working on Excel. Stay connected!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo