How to Change Capital Letters to Lowercase in Excel?

When you read an Excel sheet that contained text in all caps, you can just imagine how unprofessional or difficult to read it was. Luckily, Excel gives you an opportunity to standardize the text formatting, be it all lowercase, all uppercase, or all capital letters.

This blog will take you through the simple steps to easily turn text to lowercase in excel-not only ideal steps to be followed by beginners but also expert users.

What Does ‘Change Case’ Mean in Excel?

“Change Case” is a process of modifying the capitalization of letters in your Excel sheet. Whereas Microsoft Word has a direct button to do this, Excel needs functions or workarounds.

You can:

1. Turn all the text to lowercase (e.g. HELLO to hello).
2. Change all to upper case (hello → HELLO).
3. Proper Case capitalizes the first letter of every word (hello world → Hello World).

The case change in Excel makes your data appear good, uniform and professional- particularly when handling names, addresses or product descriptions.

Methods to Convert Uppercase to Lowercase

Excel offers many options to change the case of the letters. Here are some of the most commonly used methods:

1. Using the LOWER formula

The LOWER function is one of the easiest ways to convert uppercase letters into lowercase letters.

Syntax: =LOWER(text)

For example, if A1 cell contains HELLO WORLD. To change this case you can enter =LOWER(A1) and you will get hello world.

2. Using PROPER Formula for Names/Titles

PROPER function can be used when your dataset contains names or titles written in all caps. It will make them look professional by capitalizing the first letter of each word.

Syntax: =PROPER(text)

For example, if A1 cell contains JOHN DOE. To change this you can use =PROPER(A1) and you will get John Doe.

Step-by-Step Guide for Beginners

If you are a beginner to Excel, here is a simple process to convert uppercase text into lowercase in Excel:

Step 1: Formula Walkthrough

Let’s say your dataset is in Column A. Enter formula =LOWER(A1) in Column B. This formula will convert the text in A1 into lowercase. 

Step 2: Drag-to-fill technique

Once the formula works for the first cell you can apply it to the rest of the column. For this, place your cursor on the bottom-right corner of the formula cell until it turns into a small ‘+’ sign. Drag it down to automatically convert the rest of the column to lowercase.

Step 3: Copy-paste values for permanent fix

Formulas in Excel always depend on the original data, so we need to make the lowercase text permanent. To do this select the column with your formulas (Column B). After that, copy it using Ctrl+C. Right click and choose Paste Values. Now you can delete the original uppercase column.

Since formulas in Excel always depend on the original data, you’ll need to make the lowercase text permanent:

This ensures your dataset stays in lowercase format without being tied to the original formula.

Common Mistakes to Avoid

Although the process of changing a case in Excel is easy, there are some pitfalls that can be very tricky. The following are the most important ones to pay attention to:

Formula Error Samples

• Wrong name of the function: typing =lowercase() will give an error, but typing =LOWER() will not. Excel only accepts the literal names of functions: LOWER, UPPER and PROPER.
• Referencing blank cells: When a target cell is blank, your formula will give you an empty answer, which can be confusing to beginners.
• Dragging formulas in the wrong way: Leaving out the adjustment of cell references may cause results to be dragged in the wrong row or column.

The solution to this is to check your function name twice, make the right references and preview the initial few results before dragging the formula.

Copy-Paste Pitfalls

• Leaving out pasting as values: When you simply copy and paste the formula, you will get errors of #REF! when you delete the original column.
• The accident of overwriting data: When you paste values without verifying your choice you can overwrite valuable data.
• Failure to maintain a backup: Once you enter values in place of formulas, then you cannot reverse the change of the case unless you have retained a copy of the original data.

To fix these problems always use values and have a backup until you are sure that the conversion is correct.

Downloadable Tools & Macros

Excel macros are also an effective means of automating the process of reformatting text; this is useful to people who frequently have to do so. Rather than typing the formula several times in a row you can make some basic one-click macro that will automatically change all lowercase characters to uppercase ones.

Converting text case with a VBA macro

The VBA (Visual Base for Applications) code that is written in a macro runs automatically to carry out tasks. To make one to convert into lowercase here is how to do it:

Steps to Set Up the Macro:

• ALT + F11 will open the VBA editor in Excel.
• Go to Insert → Module.

• Paste the following code :

VBA Code to Convert Text to Uppercase

Sub ConvertToUppercase()
     Dim cell As Range
     For Each cell In Selection
         If Not cell.HasFormula Then
             cell.Value = UCase(cell.Value)
         End If
     Next cell
End Sub

VBA Code to Convert Text to Lowercase

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

VBA Code to Convert Text to Proper Case (Title Case)

Sub ConvertToProperCase()
     Dim cell As Range
     For Each cell In Selection
         If Not cell.HasFormula Then
        cell.Value= Application.WorksheetFunction.Proper(cell.Value)
         End If
     Next cell
End Sub

• Close VBA and go to your sheet of Excel.
• Click on the cells to be formatted.
• Run the macro in developer Macros and ConvertToLowercase.