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.

Change Case in Excel for Entire Column


Download Practice Workbook


7 Amazing Ways to Change Case in Excel for Entire Column

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.

Change Case in Excel for Entire Column

Read More: How to Make First Letter of Sentence Capital in Excel (4 Suitable Methods)


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.

Change Case in Excel for Entire Column

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

  • Now, AutoFill the rests.

Change Case in Excel for Entire Column

Read More: How to Change Lowercase to Uppercase with Formula in Excel (3 Ways)


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.

Change Case in Excel for Entire Column

  • Finally, AutoFill the remaining cells.

Read More: How to Change Upper Case to Lower Case in Excel (5 Effective Methods)


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.

Change Case in Excel for Entire Column

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

Change Case in Excel for Entire Column

Read More: How to Change First Letter to Uppercase in Excel (6 Handy Methods)


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.

Change Case in Excel for Entire Column

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.

Change Case in Excel for Entire Column

  • Now, choose your case pattern in lowercase.

You will have the selected values in small letters.

Change Case in Excel for Entire Column

  • You can choose UPPERCASE if you want.

This will turn the selected values into capital letters.

Change Case in Excel for Entire Column

Alternatively, you can choose Capitalize Each Word option too.

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

Change Case in Excel for Entire Column

Read More: How to Change Lowercase to Uppercase in Excel (6 Methods)


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.

Change Case in Excel for Entire Column

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

Change Case in Excel for Entire Column

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

Change Case in Excel for Entire Column


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.

Change Case in Excel for Entire Column

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

Change Case in Excel for Entire Column

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.

Change Case in Excel for Entire Column

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

Change Case in Excel for Entire Column

Read More: How to Change Lowercase to Uppercase in Excel Without Formula


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.

Change Case in Excel for Entire Column

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.

Change Case in Excel for Entire Column

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

Change Case in Excel for Entire Column

Read More: Excel Change to Proper Case without Formula (6 Easy Ways)


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. You can visit our ExcelDemy site for further information on Excel.


Related Articles

Naimul Hasan Arif

Naimul Hasan Arif

Hello, I am Arif. I am an Engineer who graduated from BUET. I want to pursue an enterprising career in a progressive environment where my skills & knowledge can be enhanced to their maximum potential to contribute to the overall success and growth of the organization.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo