# How to Change Case for Entire Column in Excel (7 Amazing Ways)

Get FREE Advanced Excel Exercises with Solutions!

To err is human. Sometimes we forget to use the correct pattern while writing. We might lose a lot of valuable time making corrections for those mistakes. Donâ€™t worry!!! There are seven amazing ways to change case in Excel for entire column. I am going to explain all these methods in this article.

For more explanation, I am going to use a dataset of some students containing the Student ID, Name, and Location columns.

## How to Change Case in Excel for Entire Column: 7 Amazing Ways

### 1. Excel PROPER Function to Change Case for Entire Column

In case of making the first letter of every words capital, we can apply the PROPER function.
Steps:

• First of all, create a new column (i.e. Modified Location Column).
• Select a cell (i.e. E5).
• Now, apply the following formula in that cell.
`=PROPER(D5)`

Here, The PROPER function is applied for the value in cell D5.

• Press ENTER to have the result.

• Lastly, use Fill Handle to AutoFill the rests.

### 2. Use of UPPER Function to Change Case

The UPPER function converts text to all capital case. Letâ€™s see its use.

Steps:

• Create a new column first (i.e. Modified Location Column).
• Next, pick a cell (i.e. E5).
• Now, apply the following formula in that cell.
`=UPPER(D5)`

Here, The UPPER function is applied for the value in cell D5.

• After that, press ENTER to have all the letters in CapitalÂ form.

• Now, AutoFill the rests.

### 3. Applying LOWER Function to Change Case for Entire Column

While converting case into the lower case you can use the LOWER function, it converts all texts to small case.

Steps:

• Firstly, create a new column (i.e. Modified Location Column).
• Next, choose a cell (i.e. E5).
• Now, input the following formula in that cell.
`=LOWER(D5)`

Here, the LOWER function is applied in cell D5.

• Hit ENTER to have the outcome.

• Finally, AutoFill the remaining cells.

### 4. Adopting Flash Fill Feature to Change Case for Entire Column

Flash Fill can be considered as the simplest way to change the case for an entire column.

Steps:

• First of all, write the pattern the way you want to have your result. In my case, I have mentioned the pattern where I want to have the first letter of every word in capital form.
• Then, put your Cursor on that cell.

• Next, go to the DataÂ tab.
• From there, click on Flash Fill from the ribbon. You can also use the shortcut key for the functionality (CTRL + E).

The rest cells will be automatically filled with the given pattern.

### 5. Change Case for Entire Column Using POWER QUERY

POWER QUERY is another advanced way to change the case for an entire column.

Steps:

• Go to the DataÂ tab.
• Along with this, click on the From Table/Range option from the ribbon.

• Now, input your table range (i.e. B4:D10 ).
• Check the My table has headers
• Then, press OK.

Power Query Editor will appear along with the table.

• Now, select the entire column that you want to modify.
• Next, go to Add Column.
• Then, click on Format from the ribbon.

• Now, choose your case pattern in lowercase.

You will have the selected values in small letters.

• You can choose UPPERCASE if you want.

This will turn the selected values into capital letters.

Alternatively, you can choose Capitalize Each Word option too.

It will turn the first letter of every word into capital letter.

### 6. DAX Formula Implementation in Pivot Table

DAX (Data Analysis Expressions) is a formula expression language that can be used in Pivot Table. In fact, DAX formulas are the combination of functions, operators, and values to perform advanced calculations and queries on data. Moreover, DAX formula can also be used to change the case for an entire column.

Steps:

• Go to the InsertÂ tab.
• Then, click on Pivot Table from the ribbon.

• Select the range in the worksheet in the Table/Range
• Afterward, choose where you want your pivot table data. You have two options. Either in the existing worksheet or in the new worksheet.
• Then, check Add this data to the Data ModelÂ box.
• Next, press OK.

• Now, Right-click on the table name and choose Add MeasureÂ option.

• Fill in the Table Name, Measure Name, Formula, and CategoryÂ section.

#### 6.1. For UPPER Case

• Here, I used the Table Name as Range, Measure Name as Upper Case.
• Input the following formula in the Formula
`=CONCATENATEX( Range, UPPER( Range[Location] ), ", ")`

Here, the CONCATENATEX function takes the first parameter as Table. The second parameter is a column that contains values to concatenate.

• From the Category section, select General.
• Then, press OK.

• Now, from the PivotTable Fields, choose the columns as Rows and Values. Here, I used Location as Rows and Upper Case as Values.

You will automatically have the entire column changed to upper case. You can modify your data according to your choice.

#### 6.2. For LOWER Case

• Here, I input the Table Name as Range and Measure Name as Lower Case.
• Next, apply the following formula to fulfill our purpose.
`=CONCATENATEX( Range, LOWER( Range[Location] ), ", ")`

Here, the CONCATENATEX function takes the first parameter as Table which I mentioned as Range. The second parameter is a column that contains values to concatenate which is Location.

• From the Category, pick General and press OK.

• After that, from the PivotTable Fields, select the columns as Rows and Values. Here, I used Location as Rows and Lower Case as Values.

This will automatically change the column into lower case.

### 7. Applying VBA to Change Case for Entire Column

Visual Basic for Applications (VBA) is the most developed and advanced way for any problem. We can use it here too to change the case for an entire column. We have to use 3 different types of VBA codes to have 3 different types of cases. They are explained briefly in the following section.

#### 7.1. Change to Upper Case

Steps:

• Select the cells that you want to change case (i.e. D5:D10).
• Click on the DeveloperÂ tab.
• Then, go to Visual Basic.

• Input the following code in the related space.
``````Sub ChangeforUppercase()
Â Â Â Â Â Â Â Â Â Â Â  For Each Location In Selection
Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â  Location.Value = UCase(Location.Value)
Â Â Â Â Â Â Â Â Â Â Â  Next Location
Â Â Â  End Sub``````

Here, I have mentioned ChangeforUppercase() as Sub_Procedure and determined the variable as Location. I have used the UCase function to convert the selected cell into Upper Case.

• Press F5 to run the program.

You will be able to see the outcome after closing the code window.

#### 7.2. Change to LOWER Case

Steps:

• Select the cells to change case (i.e. D5:D10).
• Now, click on the DeveloperÂ tab.
• Then, go to Visual Basic.

• Now, write down the following code:
``````Sub ChangetoLowercase()
For Each Address In Selection
End Sub
``````

Here, I have mentioned ChangetoLowercase() as Sub_Procedure and determined the variable as Address. Here, I have used VBA LCase function to convert the selected cell into Lower Case.

• Press F5 to run the program.

We will have our desired output on the selected cells.

#### 7.3. Change to PROPER Case

Steps:

• Firstly, select the cells that you wish to change case (i.e. D5:D10).
• Next, click on the DeveloperÂ tab.
• Then, go to Visual Basic.

• Now, input the following code.
``````Sub ChangetoProperCase()
Â Â Â Â Â Â Â Â Â Â Â  For Each Location In Selection
Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â  Location.Value = Application.WorksheetFunction.Proper(Location.Value)
Â Â Â Â Â Â Â Â Â Â Â  Next Location
Â Â Â  End Sub``````

In this case, I have mentioned ChangetoProperCase() as Sub_Procedure and determined the variable as Location. Here, I have used the Application.WorksheetFunction.Proper function to convert in the pattern where the first letter of every word will be in Capital.

• Finally, press F5 to run the program perfectly.

Thus, we can have the values in Proper Case using VBA.

## Practice Section

For more expertise, you can practice here.

## Conclusion

In this article, I have tried to explain seven amazing ways to change case in Excel for entire column. It will be a matter of great pleasure for me if this article could help any Excel user even a little. For any further queries, comment below.

## Related Articles

<< Go Back to Change CaseÂ | Text Formatting | Learn Excel

## What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Naimul Hasan Arif

Naimul Hasan Arif, a BUET graduate in Naval Architecture and Marine Engineering, has been contributing to the ExcelDemy project for nearly 2 years. Currently serving as an Excel and VBA Content Developer, Arif has authored over 120 articles. His expertise lies in Microsoft Office Suite, and he thrives on learning new aspects of data analysis. Arif's dedication to the ExcelDemy project is reflected in his consistent contributions and ongoing enthusiasm for expanding his knowledge in data analysis.

## Designation

Excel &... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF