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.

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.

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.

Step 2:

• Then, we will apply the following formula to determine the unit price.

• Consequently, you can see that in the formula, the cell numbers contain column letters in them which are C5 and D5.

Step 3:

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

Step 4:

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

Step 5:

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

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.

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.

Read More: How to Create Column Headers in Excel

2. Applying Excel VBA to Change Column Headings

In our second approach, we will apply VBA to change the column header name. 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.

• Here, the column labels are in alphabetical order.

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.

Step 3:

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

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

Step 5:

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

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.

Read More: How to Remove Column1 and Column2 in Excel

Download Practice Workbook

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

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

• How to Rename Column in Excel
• How to Remove Column Headers in Excel
  
• How to Title a Column in Excel
• How to Change Excel Column Name from Number to Alphabet
• How to Create Excel Table with Row and Column headers
• How to Repeat Column Headings on Each Page in Excel
• [Fixed] Excel Column Numbers Instead of Letters

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