Convert lowercase to uppercase in the entire Excel sheet

While working with Excel, sometimes you need to convert all text from lowercase to uppercase to ensure consistency or format data as required. Instead of manually editing each cell, Excel provides many ways to help you automate this process. This article will guide you through the most effective methods to convert lowercase to uppercase in the entire Excel sheet.

1. Using VBA (Macro) to Convert an Entire Excel Sheet to Uppercase

If you want to automatically convert all text in an Excel sheet to uppercase without using manual formulas, VBA (Visual Basic for Applications) is a powerful solution. This method is especially useful when working with large datasets and needing a quick transformation.

Steps to Convert Text to Uppercase Using VBA

Step 1: Open the VBA Editor

Open Excel.

Press Alt + F11 to open the Microsoft Visual Basic for Applications (VBA Editor).

Select Insert > Module to create a new module.

Step 2: Enter the VBA Code

Paste the following VBA code into the module window:

Here is the complete VBA code in English to convert all text in an Excel sheet to uppercase:

Complete VBA Code

Sub ConvertToUpperCase()
    Dim ws As Worksheet
    Dim rng As Range
    Dim cell As Range
    
    ' Identify the active sheet
    Set ws = ActiveSheet
    ' Define the range of used cells in the sheet
    Set rng = ws.UsedRange
    
    ' Loop through each cell and convert text to uppercase
    For Each cell In rng
        ' Check if the cell is not empty and contains text
        If Not IsEmpty(cell) And VarType(cell.Value) = vbString Then
            cell.Value = UCase(cell.Value)
        End If
    Next cell
    
    ' Display completion message
    MsgBox "All content in the sheet has been converted to uppercase!", vbInformation, "Completed"
End Sub

Code Explanation

1. Identify the active sheet using ActiveSheet.

2. Get the used range of the sheet with ws.UsedRange.

3. Loop through each cell in the range and check:

• Not IsEmpty(cell): Ensures the cell is not empty.
• VarType(cell.Value) = vbString: Only applies to text, avoiding errors with numbers or formulas.

4. Convert content to uppercase using UCase(cell.Value).

5. Show a confirmation message when the process is done.

Running this macro will automatically convert all text in the active sheet to uppercase!

Step 3: Run the Macro to Convert Text to Uppercase

1. Close the VBA Editor and return to Excel.
2. Press Alt + F8, select ConvertToUpperCase, and click Run.
3. All text in the sheet will be instantly converted to uppercase.

 Advantages of Using VBA:

✅ Automatically converts the entire sheet with a single action.
✅ No need for formulas, reducing errors when copying data.

2. Using Power Query to Convert Lowercase to Uppercase in Excel

Power Query is a powerful tool in Excel that helps process and transform data automatically without requiring complex formulas. If you want to convert all text in a column to uppercase, Power Query is a convenient and easy-to-use method.

Steps to Convert Text to Uppercase Using Power Query

Step 1: Select Data and Open Power Query

• Select the data range you want to convert.
• Go to the Data tab > Click From Table/Range.
• If the data is not already in table format, Excel will ask you to create a table – click OK.

Step 2: Modify Data in Power Query

1. When the Power Query Editor window opens, select the column you want to convert.
2. Go to the Add Column tab > Click Custom Column.
3. In the Custom Columndialog box, enter the following formula in the formula field:

   Text.Upper([Column Name])
   

Explanation:

• Text.Upper([Column Name]) is a Power Query function that converts all text in the selected column to uppercase.
• Give the new column a name (e.g., “Uppercase Data”) and click OK.

Step 3: Load the Converted Data Back to Excel

• Once the data is converted, go to the Home tab > Click Close & Load.
• Excel will generate a new table containing the converted uppercase text.

Advantages of Power Query:

✅ No formulas required – data is processed automatically.
✅ Applies to entire columns instead of modifying individual cells.
✅ Works well with large datasets and allows easy updates.

3: Using Data Validation to Enforce Uppercase Input

Data Validation cannot automatically convert lowercase to uppercase, but it can force users to enter only uppercase letters.

Steps to Apply Data Validation:

Select the range of cells where you want to enforce uppercase input (e.g., Column A).

Go to Data > Data Validation.

In the Data Validation window, go to the Settings tab:

• In the Allow section, select Custom.
• In the Formula section, enter the following formula:

=EXACT(A1, UPPER(A1)) 

Go to the Error Alert tab and enter a warning message (e.g., “Please enter uppercase letters only.”).

Click OK to apply.

Note:

• If the user enters lowercase letters, Excel will show an error message and prevent data entry.
• This method does not automatically convert text to uppercase but only enforces uppercase input.

4. Using the UPPER Function to Convert Lowercase to Uppercase

This is the simplest method to convert text to uppercase in Excel without using VBA or external tools.

Explanation of the UPPER Function

The UPPER function is a built-in Excel function that converts all letters in a cell to uppercase.

Syntax:

=UPPER(text)

• text: The text or cell reference containing the text to be converted.
• Result: The function returns the corresponding uppercase text.

Notes:

• The UPPER function only affects letters—it does not change numbers or special characters.
• If you don’t want to keep the formula, you can copy the results in Column B and paste them as values (Paste Special > Values).

Converting lowercase to uppercase in the entire Excel sheet can be done in many different ways, depending on your needs. If you need to do it quickly, the UPPER function is a simple choice. If you want to fully automate, VBA or Power Query will be the optimal method. Hopefully this article helps you find the most suitable solution. Please explore more Excel tips on our website!

See more: How to View Two or More Excel Sheets Side by Side