How to Change Case for Entire Column in Excel: 7 Methods

Method 1 – Excel PROPER Function to Change Case for Entire Column

Steps:

• Create a new column (i.e. Modified Location Column).
• Select a cell (i.e. E5).
• Apply the following formula in that cell.

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

• Press ENTER to have the result.

• Use Fill Handle to AutoFill the rests.

Method 2 – Use of UPPER Function to Change Case

Steps:

• Create a new column first (i.e. Modified Location Column).
• Pick a cell (i.e. E5).
• Apply the following formula in that cell.

The UPPER function is applied to the value in cell D5.

• Press ENTER to have all the letters in Capital form.

• AutoFill the rests.

Method 3 – Applying LOWER Function to Change Case for Entire Column

Steps:

• Create a new column (i.e. Modified Location Column).
• Choose a cell (i.e. E5).
• Input the following formula in that cell.

The LOWER function is applied in cell D5.

• Hit ENTER to have the outcome.

• AutoFill the remaining cells.

Method 4 – Adopting Flash Fill Feature to Change Case for Entire Column

Steps:

• Write the pattern the way you want your result. We mentioned the pattern where I want the first letter of every word in capital form.
• Put your Cursor on that cell.

• Go to the Data tab.
• Click on Flash Fill from the ribbon. Use the shortcut key for the functionality (CTRL + E).

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

Method 5 – Change Case for Entire Column Using POWER QUERY

Steps:

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

• Input your table range (i.e. B4:D10 ).
• Check the My table has headers
• Press OK.

Power Query Editor will appear along with the table.

• Select the entire column that you want to modify.
• Go to Add Column.
• Click on Format from the ribbon.

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

Choose the Capitalize Each Word option, too.

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

Method 6 – DAX Formula Implementation in Pivot Table

Steps:

• Go to the Insert tab.
• Click on Pivot Table from the ribbon.

• Select the range in the worksheet in the Table/Range
• Choose where you want your pivot table data. You have two options. Either in the existing worksheet or in the new worksheet.
• Check Add this data to the Data Model box.
• Press OK.

• 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

• We used the Table Name as Range, Measure Name as Upper Case.
• Input the following formula in the Formula

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

• From the PivotTable Fields, choose the columns as Rows and Values. We used Location as Rows and Upper Case as Values.

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

6.2. For LOWER Case

• We input the Table Name as Range and Measure Name as Lower Case.
• Apply the following formula to fulfill our purpose.

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.

• From the PivotTable Fields, select the columns as Rows and Values. We used Location as Rows and Lower Case as Values.

This will automatically change the column into lower case.

Method 7 – Applying VBA to Change Case for Entire Column

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

We mentioned ChangeforUppercase() as Sub_Procedure and determined the variable as Location. We 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).
• Click on the Developer tab.
• Go to Visual Basic.

• Write down the following code:

Sub ChangetoLowercase()
            For Each Address In Selection
                 Address.Value = LCase(Address.Value)
            Next Address
    End Sub

We mentioned ChangetoLowercase() as Sub_Procedure and determined the variable as Address. We 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:

• Select the cells that you wish to change case (i.e. D5:D10).
• Click on the Developer tab.
• Go to Visual Basic.

• Input the following code.

Sub ChangetoProperCase()
            For Each Location In Selection
                Location.Value = Application.WorksheetFunction.Proper(Location.Value)
            Next Location
    End Sub

We mentioned ChangetoProperCase() as Sub_Procedure and determined the variable as Location. We used the Application.WorksheetFunction.Proper function to convert in the pattern where the first letter of every word will be in Capital.

• Press F5 to run the program perfectly.

We can have the values in Proper Case using VBA.

Download Practice Workbook

Related Articles

• How to Change Sentence Case in Excel
• How to Change Case in Excel Sheet
• Excel VBA to Capitalize First Letter of Each Word

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