How to Change Column Headings in Excel (2 Ways)

In Excel, a cell is represented by the combination of a column and a row. Generally, in Excel, the column headings are in alphabetical order. But, we can change the headings of columns in Excel from alphabetical to numerical order. In this article, you will see how to change column headings in Excel.


Download Practice Workbook

You can download the free Excel workbook here and practice on your own.


2 Easy Ways to Change Column Headings in Excel

For a better understanding of our article, we will use the following data set. Here in the data set, you can see some component names to build a desktop, their unit price, and total quantity. We will use two different methods to solve our problem. In our first method, we will customize the reference style of the worksheet to change the column headings from letters to numbers, and in our second method, we will apply Visual Basic for Applications (VBA) for the same purpose.

Easy Ways to Change Column Headings in Excel

1. Customizing Reference Style to Change Column Headings in Excel

In Excel, you can see the column headings are in capital letters and the row headings are in numbers. This reference style is known as the A1 reference style. By customizing the reference style, we can change the column headings in the worksheet. To do that, follow the following steps.

Step 1:

  • Firstly, we will calculate the total price from our data set.

Easy Ways to Change Column Headings in Excel

Step 2:

  • Then, we will apply the following formula to determine the unit price.
=C5*D5
  • Consequently, you can see that in the formula, the cell numbers contain column letters in them which are C5 and D5.

Easy Ways to Change Column Headings in Excel

Step 3:

  • Thirdly, press Enter and AutoFill the formula into the following cells.

Easy Ways to Change Column Headings in Excel

Step 4:

  • Fourthly, we will customize the reference style of the worksheet.
  • For that purpose, go to the File tab of the ribbon.

Easy Ways to Change Column Headings in Excel

Step 5:

  • From there, you will see the Home page of Excel.
  • Then, select the Options command.

Easy Ways to Change Column Headings in Excel

Step 6:

  • After selecting, you will see a dialogue box naming Excel Options.
  • Then, in the Formula tab of the box choose the option R1C1 reference style.
  • Lastly, select OK.

Easy Ways to Change Column Headings in Excel

Step 7:

  • Finally, after selecting go back to your worksheet.
  • Consequently, you will notice that, after choosing this reference style, the column labels in the worksheet will turn into numeric values.

Easy Ways to Change Column Headings in Excel

Read More: [Fixed!] My Column Headings Are Labeled with Numbers Instead of Letters


2. Applying VBA to Change Column Headings in Excel

In our second approach, we will apply VBA to change the column headings. Go through the following steps to learn more about the procedure.

Step 1:

  • Firstly, we will take the following data set and calculate the total price again by using the following formula.
=C5*D5
  • Here, the column labels are in alphabetical order.

Easy Ways to Change Column Headings in Excel

Step 2:

  • Subsequently, we will change the column headings by applying VBA.
  • To do that, go to the Developer tab of the ribbon.
  • Then choose the Visual Basic command from the Code group.

Easy Ways to Change Column Headings in Excel

Step 3:

  • Thirdly, the VBA window will appear after choosing the command.
  • Then, select the Module command from the Insert tab.

Sample Data Set

Step 4:

  • Fourthly, copy the following code into the module of the VBA window.
'Set the name of the function
Sub Changing_Column_Headings()
'Altering Reference Style from Letter to Number
Application.ReferenceStyle = xlR1C1
End Sub

Sample Data Set

Step 5:

  • Lastly, save the code and press the play button or F5 to run the code.

Sample Data Set

Step 6:

  • Finally, after running the code, the reference style of column headings will turn into the R1C1 reference style.
  • Consequently, this reference style will transform the column headings from letters to numbers.

Sample Data Set

Read More: How to Change Column Header Name in Excel VBA (3 Examples)


Conclusion

That’s the end of this article. I hope you find this article helpful. After reading this article, you will be able to change column headings in Excel by using either of the above two methods. Please share any further queries or recommendations with us in the comments section below.


Related Articles

Araf

Araf

I am Araf. I have completed my B.Sc in Industrial and Production Engineering from Ahsanullah University of Science and Technology. Currently I am working as an Excel & VBA Content Developer in Softeko. With proper guideline and aid of Softeko I want to be a flexible data analyst. With my acquired knowledge and hard work, I want to contribute to the overall growth of this organization.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo