In this article, we’re going to show you 6 methods of how to change lowercase to uppercase in Excel. For this, we have a dataset containing 2 columns: Name, and Car Maker. Our aim is to transform the values of the Car Maker column into uppercase.
How to Change Lowercase to Uppercase in Excel: 6 Ways
1. Using the UPPER Function to Change Lowercase to Uppercase in Excel
For the first method, we’ll use the UPPER function to change lowercase to uppercase.
Steps:
- Firstly, type the following formula in cell D5.
=UPPER(C5)
The UPPER function returns the value of a cell in uppercase. Here, we’ll convert each lowercase into an uppercase character. However, remember that this function does not affect numbers.
- Secondly, press ENTER.
Thus, we’ll change the case of our first value.
- Finally, use the Fill Handle to AutoFill the formula.
In Conclusion, we’ve changed the lowercase to uppercase.
Read More: How to Change Case in Excel Sheet
2. Applying the Flash Fill to Change Lowercase to Uppercase in Excel
For the second method, we’ll use the Flash Fill feature of Excel to change the case.
Steps:
- Firstly, type the first value of the Car Maker column in cell D5 in uppercase to make a pattern.
- Secondly, start to type the second value too.
Before finishing it, we’ll notice a grey text box. That box is because of the Flash Fill feature.
- Finally, press ENTER.
Consequently, we’ve fulfilled our goal using yet another method.
3. Change Lowercase to Uppercase in Excel by Utilizing the Power Query
In this method, we’ll use the Power Query to change the case. Here, we’ll replace the lowercase values from the range C5:C10 to uppercase.
Steps:
- Firstly, select the cell range B4:C10.
- Secondly, from the Data tab >>> select From Table/Range.
Create Table dialog box will appear. Make sure “My table has headers” is checked.
- Thirdly, click on OK.
After that, the “Power Query Editor” window will open.
- Then, select the “Car Maker” column.
- After that, from the Add Column tab >>> Format >>> select UPPERCASE.
Then, we’ll see another column is added with uppercase values.
Now, we need to load this table into our sheet.
- Then, from File >>> select Close & Load To…
The Import Data dialog box will appear.
- Then, click on the “Existing worksheet:” and pick the location as cell B13.
- After that, press OK.
Thus, we can import the Power Query table into our existing sheet.
- Then, copy all the uppercase values from the new table.
- After that, right-click on the cell range C5:C10.
- Finally, from the Paste Options: select Values.
Thus, we’ve changed lowercase to uppercase using the Power Query feature.
4. Implementing the DAX Formula to Change Lowercase to Uppercase in Excel
For the 4th method, we’re going to use the DAX formula to change lowercase to uppercase.
Steps:
- Firstly, select the full dataset table.
- Secondly, from the Insert tab >>> select PivotTable.
A dialog box will appear.
- Thirdly, click on Existing Worksheet and pick the Location: cell B12.
- Then, put a tick mark on “Add this data to the Data Model”.
- After that, click on OK.
Then, we’ll see the PivotTable Fields option.
- Right-click on Range, then select Add Measure…
The Measure dialog box will appear.
- Type anything in the Measure Name: box. We’ve typed “uppercase”.
- After that, type the following formula in the Formula: box.
=CONCATENATEX( Range, UPPER( [Car Maker (lowercase)] ), ", ")
Here, we’re using the CONCATENATEX function from DAX and the UPPER function. The Range is our table. Finally, we’re joining the values with a comma in the Grand Total section. You can skip that comma part. The below formula will work too.
=CONCATENATEX( Range, UPPER( [Car Maker (lowercase)] ))
- Then, click on OK.
- After that, bring “Car Maker (lowercase)” to Rows and “uppercase” to Values by dragging with the mouse.
- Then, click on “X” to close that window.
We can see the values are transformed into uppercase.
- After that, Copy the values.
- Then paste those values in cell D5:D10.
Notice that, the values are not matching. This is because the values were sorted automatically when the DAX formula was used.
To fix that problem we’ll Sort the values of cell B5:C10.
- Firstly, select the cell range B5:C10.
- Secondly, from the Home tab >>> Sort & Filter >>> select Custom Sort…
The Sort dialog box will appear.
- Then, select Sort by “Car Maker (lowercase)”.
- Finally, press on OK.
Thus, we’ve solved our problem with the help of the Sort feature. Moreover, this is what the final step should look like.
Read More: How to Change Lowercase to Uppercase with Formula in Excel
5. Using VBA to Change Lowercase to Uppercase in Excel (Requires Range Pre Selection)
For method 5, we’ll use a VBA code to change lowercase to uppercase.
- Firstly, from the Developer tab >>> select Visual Basic.
This will bring up the Visual Basic window.
- Secondly, from Insert >>> click on Module.
- Thirdly, type the following code.
Sub LowerToUpper()
For Each Cell In Selection
If Not Cell.HasFormula Then
Cell.Value = UCase(Cell.Value)
End If
Next Cell
End Sub
We’re calling our Sub as LowerToUpper. It has no arguments. Then we’re telling the code to check if the selected cell has any formula using the IF statement. If there is no formula, then we’re using the UCASE function to change the values into uppercase. Then, a For loop will check the selection one by one.
- Then, save the Module and close the window.
Now, we’ll run our macro. Here we need to preselect our cell range.
- Firstly, select the cell range C5:C10.
- Secondly, from the Developer tab >>> select Macros.
The Macro dialog box will appear.
- Thirdly, select “LowerToUpper”.
- Finally, click on Run.
After doing so, we’ll change get uppercase values.
Read More: How to Change Case for Entire Column in Excel
6. Change Lowercase to Uppercase Using VBA in Excel (No Need to Preselect Range)
For the last method, we’ll use another VBA code. This time the formula is almost similar, however, we’ll not be needed to preselect cell ranges.
Steps:
- Firstly, bring up the module window as shown in method 5.
- Secondly, copy and paste the following code.
Sub UpperCase()
Dim cRange As Range
Dim xRange As Range
On Error Resume Next
xTitleId = "Lowercase to Uppercase"
Set xRange = Application.Selection
Set xRange = Application.InputBox("Range", xTitleId, xRange.Address, Type:=8)
For Each cRange In xRange
cRange.Value = VBA.UCase(cRange.Value)
Next
End Sub
We’re calling our Sub UpperCase. Then, we’re defining two variables cRange and xRange as Range. After that, we’re using the On Error Resume Next statement. Next, we used the Set statement to select the Range through InputBox by using the Application.Selection.
We’re setting our InputBox title as “Lowercase to Uppercase”. After that, we’re asking for a cell range from the user. Finally, the UCASE function will change it to uppercase.
Then save the Module and close the window.
- Thirdly, bring up the Macro dialog box as shown in method 5.
- Then, select “UpperCase”.
- After that, click on Run.
We’ll see a dialog box, and it will ask us to select a cell range.
- Then, select the cell range C5:C10.
- Finally, press OK.
Thus, we’ve demonstrated method 6 to change lowercase into uppercase.
Read More: Excel VBA to Capitalize First Letter of Each Word
Practice Section
We’ve provided a practice dataset for each method in the Excel file.
Download Practice Workbook
Conclusion
We’ve shown you 6 methods of how to change lowercase to uppercase in Excel. If you face any difficulties while understanding the methods, feel free to comment below. Thanks for reading, keep excelling!