How to Change Case in Excel Sheet (8 Quick Methods)

While working in Microsoft Excel we might find different cases like lowercase, and uppercase in different cells. Sometimes you will find upper and lower cases between texts also. Excel does not have a built-in feature to change text cases in a spreadsheet. In this article, I am going to share with you how to change case in excel sheet.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


8 Simple Methods to Change Case in Excel Sheet

In the following article, I have shared 8 simple and easy methods to change case in excel sheet. Even if you are not a big fan of formulas don’t worry I also have some solutions for this without formulas. So stay tuned!

Suppose we have a dataset of some Employee Names and their Total Sales. But as you can see from the following screenshot the names are not arranged properly. Now we are going to solve this problem by changing the case of the text in different ways.

Change Case in Excel Sheet


1. Use UPPER Function to Change Case in Excel Sheet

Sometimes you might want to get all the texts in upper cases in your worksheet. For that simply use the UPPER function of excel which converts texts into upper cases in a given string.

Steps:

  • Select a cell (F5) to write the formula.
  • Put the formula down in the cell-
=UPPER(C5)

Use UPPER Function to Change Case in Excel Sheet

  • Press Enter and then drag the “fill handle” down to fill all the cells.

  • As you can see here we have all our names converted into upper case.

Use UPPER Function to Change Case in Excel Sheet

Read More: How to Change Lowercase to Uppercase with Formula in Excel (3 Ways)


2. Utilize LOWER Function to Change Case

The LOWER function in excel changes all the texts to lower cases in a given string. In this method, I am describing how you can change texts to lower cases with a simple formula.

Steps:

  • Choose a cell. Here I have selected cell (F5) to apply the formula.
  • Write the formula down-
=LOWER(C5)

Utilize LOWER Function to Change Case in Excel Sheet

  • Hit the Enter.
  • Drag the “fill handle” down.

  • Thus we can change to lower cases using the LOWER function in excel.

Utilize LOWER Function to Change Case in Excel Sheet

Read More: How to Change Upper Case to Lower Case in Excel (5 Effective Methods)


3. Perform PROPER Function to Change Case

Worldwide when a name is written, everyone knows that the first letter must be the upper case whether it’s the first name or the last name. Using the PROPER function we can achieve that in excel.

Steps:

  • To apply the formula select a cell (F5).
  • The formula is-
=PROPER(C5)

Perform PROPER Function to Change Case in Excel Sheet

  • Click the Enter button and pull the “fill handle” downwards to get the desired output.

  • Check out the screenshot below, we have successfully changed text cases in our excel sheet.

Perform PROPER Function to Change Case in Excel Sheet

Read More: How to Make First Letter of Sentence Capital in Excel (4 Suitable Methods)


4. Use Microsoft Word to Change Case

Using formulas in excel sometimes seems boring. That’s why I have shared a quick and simple way of changing cases in this method.

Steps:

  • Select data from your list. Here I have selected cell (C5:C14).
  • Press Ctrl+C to copy.

  • Open a blank document in your Microsoft Word.
  • Paste the selected data using Ctrl+V.
  • While the text values are selected go to the Home tab and choose the text cases of your choice.
  • Here I have selected “Capitalize Each Word”.

Use Microsoft Word to Change Case in Excel Sheet

  • Now select the converted output and press Ctrl+C to copy.

  • Go to your excel workbook and paste by pressing Ctrl+V.
  • There we have it successful conversion of case in our excel sheet.

Use Microsoft Word to Change Case in Excel Sheet


5. Apply Flash Fill Feature to Change Case in Excel Sheet

Flash fill feature in excel is my favorite feature. In an excel sheet, you can fill cells according to your choice, and excel reads it well. But the main problem is it’s static. To know more about it don’t forget to check the “Things to Remember” section below. Now follow the steps below-

Steps:

  • Open a new column just by the side of Column C as flash fill works with side by side column.
  • In the cell (D5) type the employee name with your desired case.

Apply Flash Fill Feature to Change Case in Excel Sheet

  • Select all the cells to fill with flash fill.
  • While selecting press Ctrl+E to fill.

  • Finally, we got our precious result-changing case in an excel sheet.

Apply Flash Fill Feature to Change Case in Excel Sheet

Read More: How to Change Case for Entire Column in Excel (7 Amazing Ways)


6. Utilize Power Query to Change Case in Excel Sheet

Power Query is all about data transformation. It keeps track of every step and conversion of values that we apply in a dataset. Using the power query tool you can change your text to lower, upper or proper cases you want.

Steps:

  • Select cells with the header. Here I have chosen cells (C4:C14).
  • Click “From Table/Range” from the Data tab.

Utilize Power Query to Change Case in Excel Sheet

  • A confirmation window will appear confirming the creation of the new table.
  • Press OK.

  • Go to Add Column > Format > Capitalize Each Word.
  • Here I have chosen “Capitalize Each Word” but you can choose your own desired format.

Utilize Power Query to Change Case in Excel Sheet

  • Now you will see a new column is added capitalizing each word.

  • Close the window and get your precious output in a new worksheet.

Utilize Power Query to Change Case in Excel Sheet


7. VBA Code to Change Case in Excel Sheet

VBA (Visual Basic for Applications) is the programming language in excel. With the help of VBA code, you can easily change cases in an excel sheet. In this method, I have described changing texts to all different cases Upper, Lower and Proper cases.

7.1 Change to Upper case

In the following submethod, I will show how you can change the upper case of texts.

Steps:

  • Choose cells (C5:C14) as we are changing the cases of these cells.
  • Press Alt+F11 to open the “Microsoft Visual Basic for Applications” window.

VBA Code to Change Case in Excel Sheet

  • In the module put the following code-
Sub Uppercase()
For Each x In Selection
If Not x.HasFormula Then
x.Value = UCase(x.Value)
End If
Next x
End Sub
  • Press “Run”.

VBA to Change case

  • Now in your workbook check out the result. Simple isn’t it?

Read More: How to Change Lowercase to Uppercase in Excel Without Formula

7.2 Transform into Lower Case

Just like the previous method, this method is the same. Only a small difference in the command section with lowercase.

Steps:

  • Here I have selected cell (C5:C14).
  • From the keyboard holding the Alt button press F11.

  • Open a new module and write the following code-
Sub Lowercase()
For Each x In Selection
If Not x.HasFormula Then
x.Value = LCase(x.Value)
End If
Next x
End Sub
  • Hit the “Run” button.

VBA to Change Case

  • So as you can see we have changed cases in our excel sheet using a simple VBA code.

VBA Code to Change Case in Excel Sheet

7.3 Change to Proper Case

Now let’s convert the text case to the proper case.

Steps:

  • Select cells (C5:C14) and open the “Microsoft Visual Basic for Applications” window by pressing Alt+F11.

  • Put the following code in the module-
Sub Propercase()
For Each x In Selection
If Not x.HasFormula Then
x.Value = _
Application.WorksheetFunction.Proper(x.Value)
End If
Next x
End Sub
  • Click the “Run” button.

VBA to change case

  • Finally, by performing a VBA code in our worksheet we have changed cases.

VBA Code to Change Case in Excel Sheet


8. Use DAX Formula to Change Case in Excel Sheet

To summarize data in an excel workbook we use mostly pivot tables. Using DAX formulas you can change and at the same time summarize your data in an excel sheet.

8.1 Change to Upper Case

To convert to upper case using a DAX formula follow the steps below-

Step 1:

  • Choose cells (C4:C14).
  • Now click the “Pivot Table” from the “Table” section in the “Insert” tab.

Use DAX Formula to Change Case in Excel Sheet

  • Choose a location in the existing worksheet.
  • Check to mark the “Add this data to the data model”.
  • Press OK to continue.

  • From the right pane choose “Range” and click the right button on the mouse to get options.
  • From the options choose “Add Measure”.

Use DAX Formula to Change Case in Excel Sheet

Step 2:

  • In the “Measure” window give a measure name. Here I have given “Upper Case” as we are changing all our texts to upper case.
  • Enter the following formula in the formula section-
=CONCATENATEX(Range,UPPER(Range[Employee Name]),",")
  • Choose “General” and click OK.

Use DAX Formula to Change Case in Excel Sheet

  • Now choose the “Upper Case” and drag it to the “Values” section.

  • Then drag the “Employee Name” to the “Rows” section.

Use DAX Formula to Change Case in Excel Sheet

  • Simply we got our output in the same excel sheet by converting all the texts to upper case.

Use DAX Formula to Change Case in Excel Sheet

Read More: How to Change Lowercase to Uppercase in Excel (6 Methods)

8.2 Shift to Lower Case

Applying the DAX formula we can change texts to lower and upper cases only. Inside a DAX formula, it can’t calculate the proper cases for texts. Follow the steps to change to lower cases.

Steps:

  • Just like the previous method, we will open the “Measure” window.
  • Put the formula down-
=CONCATENATEX('Range 1',LOWER('Range 1'[Employee Name]),",")
  • Hit OK to continue.

  • There we have it. In our excel sheet changing text to lower cases as we wanted.

Use DAX Formula to Change Case in Excel Sheet


Things to Remember

  • After applying formulas in the cell don’t forget to convert them into values to edit if needed. To convert to values just select the output, press Ctrl+C to copy, and then press Alt+ESV to paste as values.
  • The disadvantage of the flash fill feature is that it’s static. If you change the value on which you applied the feature it won’t change in the output section.
  • You can also change cases using different types of fonts like Copperplate Gothic, Engravers, Felix Titling, Stencil, etc.

Conclusion

In this article, I have tried to cover all the simple methods to change case in excel sheet. Take a tour of the practice workbook and download the file to practice by yourself. Hope you find it useful. Please inform us in the comment section about your experience. We, the Exceldemy team, are always responsive to your queries. Stay tuned and keep learning.


Related Articles

Wasim Akram

Wasim Akram

Hi! my name is Wasim Akram. I am a graduate in mechanical engineering from Ahsanullah University of Science & Technology. I am passionate about learning new things and writing articles. I write article about Microsoft Excel and it's my favourite thing to do.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo