A tutorial on five methods to convert text to uppercase, lowercase, or proper case in Excel.  You can watch the video above or follow the tutorial below.  If you want to download the file featured in the video, please click here.  The VBA code featured in the video tutorial can be found at the bottom of this page.

Introduction

Excel is a powerful spreadsheet application that allows you to manipulate data in various ways. One of the common tasks that you may encounter is changing the case of text in Excel. For example, you may want to convert all the names in a column to proper case, or all the product codes to uppercase.

There are different methods to change the case of text in Excel, depending on your preference and the situation. In this tutorial, we will cover five methods that you can use to change the case of text in Excel:

• Flash Fill
• UPPER, LOWER, and PROPER functions
• Power Query
• Copilot
• VBA macro

We will use the following sample data to demonstrate each method.

Method 1: Flash Fill

Flash Fill is a feature in Excel that automatically fills in values based on a pattern that you provide. You can use Flash Fill to change the case of text in Excel by typing an example of the desired output in an adjacent cell and letting Excel fill in the rest.

To use Flash Fill to change the case of text in Excel, follow these steps:

1. In the column immediately to the right of the column that contains the text that you want to change the case of, type an example of the desired output in the first cell of the new column. For example, if you want to change the names to proper case, type John Smith in cell B2.
2. Press CTRL Enter, which will leave you in the cell you are editing and then click the Flash Fill button on the Data tab of the ribbon, or use the shortcut Ctrl + E on your keyboard.
3. Flash Fill will copy the pattern down the rest of the column.

Here is the result of using Flash Fill to change the case of the names in column A:

Method 2: UPPER, LOWER, and PROPER functions

Excel has three built-in functions that can change the case of text: UPPER, LOWER, and PROPER. The UPPER function converts text to uppercase, the LOWER function converts text to lowercase, and the PROPER function converts text to proper case (the first letter of each word is capitalized, and the rest are lowercase).

To use these functions to change the case of text in Excel, follow these steps:

1. In a new column type the formula that corresponds to the desired output in the first cell of the new column. For example, if you want to change the product codes to uppercase, type =UPPER(B2) in cell C2.
2. Copy the formula to the rest of the cells in the new column.

Method 3: Power Query

Power Query is a tool in Excel that allows you to import, transform, and analyze data from various sources. You can use Power Query to change the case of text in Excel by applying a transformation step to the column that contains the text that you want to change the case of.

To use Power Query to change the case of text in Excel, follow these steps:

1. Convert the range of cells that contains the text that you want to change the case of into a table. To do this, select the range of cells, and click the Table button on the Insert tab of the ribbon. Check the box that says My table has headers, and click OK.
2. Click the Data tab of the ribbon, and click the From Table/Range button in the Get & Transform Data group. This will open the Power Query Editor window.
3. Select the column that contains the text that you want to change the case of. For example, if you want to change the case of the names in column A, select the Name column.
4. Click the Transform tab of the ribbon, and click the Format button in the Text Column group. A drop-down menu will appear with various options to change the case of text. Choose the option that corresponds to the desired output. For example, if you want to change the names to proper case, choose Capitalize Each Word.
5. Click the Close & Load button on the Home tab of the ribbon. This will close the Power Query Editor window and load the transformed data into a new worksheet in Excel.

Method 4: Copilot

Copilot is a feature in Excel that uses artificial intelligence to help you with various tasks. You can use Copilot to change the case of text in Excel by typing a natural language query in the Copilot pane and letting Copilot generate the formula or action for you.  You will need a Copilot licence to use this method.

To use Copilot to change the case of text in Excel, follow these steps:

1. Save your workbook to One Drive
2. Convert the range of cells that contains the text that you want to change the case of into a table. To do this, select the range of cells, and click the Table button on the Insert tab of the ribbon. Check the box that says My table has headers, and click OK.
3. Click in a cell within your new table and then click the Copilot button on the Home tab of the ribbon. This will open the Copilot pane on the right side of the Excel window.
4. Type a natural language query that describes the task that you want to perform. For example, if you want to change the case of the product codes in column B, you can type “change product codes to uppercase”.
5. Press Enter and wait for Copilot to generate the formula or action for you. Copilot will show you the result in a preview window and the formula or action in a text box. For example, Copilot will show you the result of changing the product codes to uppercase and the formula =UPPER(B2:B6) in the text box.
6. Click the + Insert column button to apply the formula to your worksheet.

Method 5: VBA macro

VBA (Visual Basic for Applications) is a programming language that allows you to create macros in Excel. A macro is a set of instructions that can automate tasks in Excel. You can use VBA to change the case of text in Excel by writing a macro that applies the appropriate function to change the case of text.  The macro we are going to use, automatically runs when the contents of the worksheet changes.

To use the VBA macro with the on change event for a worksheet, follow these additional steps:

1. Right-click the sheet tab that contains the text that you want to change the case of, and choose View Code. This will open the Visual Basic Editor window with the code module for the worksheet.
2. Paste the following code in the worksheet module window. This code defines a procedure named Worksheet_Change that runs whenever a cell value in the worksheet changes.

Code to change the case to proper case:

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False

If Not Application.Intersect(Target, Range("A1:A100")) Is Nothing Then

Target(1).Value = Application.WorksheetFunction.Proper(Target(1).Value)

End If

Application.EnableEvents = True

End Sub

Code to change the case to lower case:

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
     
    If Not Application.Intersect(Target, Range("A1:A100")) Is Nothing Then
        Target(1).Value = LCase(Target(1).Value)
    End If
    Application.EnableEvents = True
    
End Sub

Code to change the case to upper case:

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
     
    If Not Application.Intersect(Target, Range("A1:A100")) Is Nothing Then
        Target(1).Value = UCase(Target(1).Value)
    End If
    Application.EnableEvents = True
    
End Sub

Close the Visual Basic Editor window and return to Excel. Now, whenever you enter or change a value in the range A1:A100, the VBA macro will automatically change the case of the text. Please note you can change the range from A1:A100 to any range on your worksheet that you want the macro to apply to.

Please also note if you are using the VBA macro method you will need to save your file with the macro-enabled file type .xlsm