How to Change Lowercase to Uppercase in Excel Without Formula (5 Methods)

Here, we have a simple dataset containing two columns. Let’s change the lowercase text in the left column to uppercase in the empty right column.

Method 1 – Using the Flash Fill Feature

Flash Fill senses the pattern in your text and fills your data in the same way. It identifies the cell value pattern and repeats the order for the rest of the cells in a range.

Steps:

Enter the lowercase text “chris” in Cell B5 in uppercase format in Cell C5, i.e. “CHRIS“.
Press Enter.
Press Alt+E to activate the Flash Fill.
In cell C6, start typing E (for EVANS).

Excel suggests the rest. Flash Fill will also suggest filling the rest of the names in the column in the same manner.

Simply accept the suggestion by pressing Enter.

Method 2 – Using Excel Caps Fonts

When you always want a text in uppercase but you don’t want to worry about how the text will be typed, you can use a font that has no lowercase style for the letters. The following fonts are always in uppercase:

Stencil
Engravers
Copperplate Gothic
Felix Titling
Algerian

Steps:

Select the range C5:C10.
Under the Home tab, select one of the fonts above from the font drop-down list, or type the font name in the box. Here, we select Copperplate Gothic.
Now type anything in any cell in the range.

Text appears in uppercase in the selected font.

Note:

The output will always be in uppercase, whether you type your text in lowercase, mixed-case, or uppercase.

Method 3 – Using Microsoft Word

If you don’t feel comfortable using formulas in Excel, you can apply the following process for converting text cases in MS Word.

Steps:

Copy the range of cells (i.e. B5:B10) for which you want to change cases in Excel.
Open an MS Word document.
Paste the copied cells into it.
Select the texts for which you want to change cases.
In the Home tab, click on the Change Case icon.
Select UPPERCASE from the list.

The text is converted to uppercase.

Copy the text from the Word table.
Right-click on Cell C5.
Select the Paste option as in the following image.

The copied uppercase values are inserted.

Method 4 – Using Excel VBA Code

Steps:

Select the column in which you want to change the case.

Press Alt+F11 and a VBA module will open.

Paste the following code in the Module window:

Sub Upper_case()
For Each Cell In Selection
If Not Cell.HasFormula Then
Cell.Value = UCase(Cell.Value)
End If
Next Cell
End Sub

Press Run Sub/UserForm, or just press F5.

Text in the selected column is converted to uppercase.

Note: To apply Lowercase, insert the following code into the Module window:

Sub Lower_case()
For Each Cell In Selection
If Not Cell.HasFormula Then
Cell.Value = LCase(Cell.Value)
End If
Next Cell
End Sub

And to apply Propercase, insert the following code into the Module window:

Sub Proper_case()
For Each Cell In Selection
If Not Cell.HasFormula Then
Cell.Value = _
Application _
.WorksheetFunction _
.Proper(Cell.Value)
End If
Next Cell
End Sub

Method 5 – Use the Power Query Tool

Power Query is a significant tool for data transformation, which can convert the case into lowercase, uppercase, and propercase styles.

Steps:

Select any cell in the dataset.
Go to the Data tab.
Click From Table/Range.

A pop-up will appear.

Mark the My table has headers option.
Check the data range is correct as in the screenshot below.
Press OK.

A Power Query Editor window will pop up.

Ensure the column is selected, then click Add Column> Format> UPPERCASE.

A new UPPERCASE column will be created adjacent to the lowercase column.

Click the File tab.
Select Close & Load.

The following table will be created in a new worksheet in your workbook.

Download Practice Book

Change Lowercase to Uppercase without Formula.xlsm

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

Get FREE Advanced Excel Exercises with Solutions!

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Dim rng As Range Set rng = Sh.Range("A1:Z100") ' Replace with the desired range If Not Intersect(Target, rng) Is Nothing Then Application.EnableEvents = False On Error Resume Next Target.Value = UCase(Target.Value) Application.EnableEvents = True End If End Sub

2 Comments

Mashal Jun 26, 2023 at 1:42 PM

Hi

Thank you for the good work. I was looking for a way to automatically convert any entry I enter in specific cells to upper case by just only entering the text and without choosing the Caps only fonts.

Thank you,

Mashal
- Reply
  Rafiul Hasan Jul 2, 2023 at 12:10 PM
  Hi MASHAL,
  You can use use VBA macro to automatically convert any entry you enter in specific cells to upper case by just only entering the text. Just follow these steps:
  1. Press ALT + F11 to open the Visual Basic for Applications (VBA) editor.
  2. In the Project Explorer window, locate and double-click on the “ThisWorkbook” module for the desired workbook.
  3. In the appeared code window, paste the following code:
```
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim rng As Range
Set rng = Sh.Range("A1:Z100") ' Replace with the desired range

If Not Intersect(Target, rng) Is Nothing Then
Application.EnableEvents = False
On Error Resume Next
Target.Value = UCase(Target.Value)
Application.EnableEvents = True
End If
End Sub
```
  Replace “A1:Z100” with the range of cells you want to convert to uppercase.
  4. Close the VBA editor.
  
  Now, whenever you enter any text within the specified range, it will be automatically converted to uppercase without the need to manually run any macro. Adjust the range in the code as needed to match your desired cells.
  
  Regards
  Rafiul Hasan
  Team ExcelDemy

How to Change Lowercase to Uppercase in Excel Without Formula (5 Methods)

Method 1 – Using the Flash Fill Feature

Method 2 – Using Excel Caps Fonts

Method 3 – Using Microsoft Word

Method 4 – Using Excel VBA Code

Method 5 – Use the Power Query Tool

Related Articles

Leave a reply Cancel reply