ExcelInsider Team
Excel often deals with data directly imported from other sources or databases. Trimming and prepping the data per your requirement creates an extra burden. Many times, while collecting customer data, primarily through forms, we need to change the case of the text. For example, names or emails stored in uppercase are not readable and helpful. For this reason, Excel has some quick solutions for standardizing data.
Quick steps to change uppercase to lowercase in Excel with LOWER function:
ā¤ Add a new custom column for storing lowercase data.
ā¤ In the output cell, give the formula –
=LOWER(C2)
Where C2 is the cell containing the text in uppercase.
ā¤ Press Enter and drag the cells to generate the same formula for all the cells below.
Overall, in this write-up, we will discuss the ins and outs of how you can change uppercase to lowercase of any text in Excel. Here, we will ride you through the easy formulations of Excel, along with using VBA and even Microsoft Word. With the guide to typical issues, tips, and tricks, youāll know which way to rely on for your problem.
Using the LOWER Function To Change Uppercase
The LOWER function is the easiest method to change the uppercase of any alphabet in Excel. You just need to put the cell number inside the LOWER parentheses to generate the text in lowercase.
Steps:
ā¤ Add a new column for the lowercase data
ā¤ In the output cell, enter the following formula –
=LOWER(C2)
ā¤ Press the Enter key and drag the cells to fill the same formula for the rest of the rows in the column.
Note:
Make sure all data is formatted in text format to avoid incorrect values.
Apply Lowercase Using Microsoft Word
Microsoft Word can be your savior if you donāt want to go into the hassles of formulas and are not a pro in Excel. Word also has a Change Case feature that can change the case of text with a few clicks. Though it is not an automated solution, we can use this method for smaller datasets.
Steps:
ā¤ Select the range of the cells you want to change to uppercase.
ā¤ Copy them by right-clicking or using the shortcut keys like Ctrl + C .
ā¤ Open a blank document in Microsoft Word and paste the copied data.
ā¤ Select all the pasted data and go to Home tab ->Ā Change Case -> lowercase option from the dropdown menu.
ā¤ This will change the pasted data to lowercase.
ā¤ Once youāre done, copy and paste the data into Excel.
Note:
Double-check the formatting of the tables and the cells. Reapply the styles where necessary after pasting the data.
Automate Conversion With VBA Macro
When working with a huge data set, you need an automated method to organize it. A VBA Macro can save you a lot of time and hassle. If you can formulate it with the right code curated to your requirements, you can easily change uppercase to lowercase in Excel. You donāt need any helper column or manual method.
Steps:
ā¤ Go to the Developer tab -> Visual Basic to open the VBA window.
ā¤ In the VBA window, click Insert and select Module, which opens the blank space to write the code.
ā¤ Paste the following code in the space-
Sub ConvertToLowercase()
Ā Ā Ā Dim cell As Range
Ā Ā Ā For Each cell In Selection
Ā Ā Ā Ā Ā Ā Ā If Not cell.HasFormula Then
Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā cell.Value = LCase(cell.Value)
Ā Ā Ā Ā Ā Ā Ā End If
Ā Ā Ā Next cell
End Sub
ā¤ Save the file and click Yes to continue.
ā¤ Close the window of the VBA macro and return to Excel.
ā¤ Select the column or range of cells you want to change to lowercase.
ā¤ Go to the Developer tab ->Ā Macros beside Visual Basic option.
ā¤ In the Macro window, choose the name ConvertToLowercase and click Run to apply the code to all the selected cells.
ā¤ This will automatically convert the range to lowercase without any further steps.
Note:
ā§ This VBA code snippet is irreversible as the original data is overwritten. If needed, you can store the uppercase column in a separate column before applying the change.
ā§ Macro only changes the normal text, except for those with formulas.
Use Power Query To Apply Lowercase Formula
Power Query can be the best way to apply case changes to Excel to consistently format large datasets. You can directly use this for faster conversion without further formulas or complex VBA codes. It gives your data a structured way and keeps the original data intact.
Steps:
ā¤ Select the whole table or the column you want to convert.
ā¤ Click on the Data tab and choose From Table/Range.
ā¤ In the Create Table window, ensure the cell range is correct and click on the checkbox My table has headers before clicking OK.
ā¤ A Power Query Editor window shows the selected cells in tabular form.
ā¤ Select the cells you want to convert.
ā¤ Go to the Transform tab -> Format and select lowercase from the dropdown menu.
ā¤ This will convert the selected column to lowercase.
ā¤ Go to the Home tab and click Close & Load, the left-most option.
ā¤ This will generate a new worksheet with the newly generated lowercase column.
Note:
If the source data changes, you can click Refresh to load and change the new data.
Change Uppercase to Lowercase Except the First Letter
We usually need to convert the uppercase to lowercase, except for the first letter.Ā It is essential when dealing with pronouns, like names of people, countries, etc. Luckily, Excel has a curated formula for this function too.
The PROPER function is one of the simplest functions built for this purpose. Passing the cell number inside this function changes the text string in sentence form. That means all the letters are changed from uppercase to lowercase except the first one.
Steps:
ā¤ Click on the output cell.
ā¤ Enter the formula given below –
=PROPER(D2)
D2 is the cell containing the text to be changed to lowercase.
ā¤ Drag the cells to fill the rest of the column.
Frequently Asked Questions
Can I change text to lowercase without using a formula?
You can flash fill the column to lowercase without using a formula. At first, manually write one cell element with lowercase in the adjacent column. Drag the cells and fill the entire column to get lowercase for all the others.
How do I convert text to lowercase in-place?
To convert text to lowercase in-place, you can use the VBA Macro. The curated code snippet can help you to change the case without any helper column.
What is the shortcut key for changing case in Excel
There is no shortcut key if you are changing a case in Excel. However, in Word, you can use Shift + F3 to change case.
Does Excel have a built-in feature like Word’s “Change Case”?
Excel does not have a built-in feature for changing cases, unlike Word. But there are functions like UPPER, LOWER, and PROPER that can be used in some quick steps.
How to change text to uppercase?
To change the text to uppercase, you can use theĀ UPPER formula. This works similarly to the LOWER function and changes the text with the following formula –
=UPPER(D2),
where D2 cell contains the text to be changed.
Wrapping Up
Here, we tried to cover all the nitty-gritty of the methods of changing upper case to lower case in Excel. We can use the LOWER functions or the automated processes of VBA Macro and Power Query. All these will help you manipulate the data in a proper format and tailor it to your requirements.
