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

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
                 Address.Value = LCase(Address.Value)
            Next Address
    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.

Download Practice Workbook

Change of Case for Entire Column.xlsm

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.

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

Get FREE Advanced Excel Exercises with Solutions!

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

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

1. Excel PROPER Function to Change Case for Entire Column

2. Use of UPPER Function to Change Case

3. Applying LOWER Function to Change Case for Entire Column

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

5. Change Case for Entire Column Using POWER QUERY

6. DAX Formula Implementation in Pivot Table

6.1. For UPPER Case

6.2. For LOWER Case

7. Applying VBA to Change Case for Entire Column

7.1. Change to Upper Case

7.2. Change to LOWER Case

7.3. Change to PROPER Case

Practice Section

Conclusion

Related Articles

Leave a reply Cancel reply