Wasim Akram
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.
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)
- 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.
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)
- Hit the Enter.
- Drag the “fill handle” down.
- Thus we can change to lower cases using the LOWER function in excel.
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)
- 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.
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”.
- 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.
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.
- 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.
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.
- 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.
- Now you will see a new column is added capitalizing each word.
- Close the window and get your precious output in a new worksheet.
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.
- 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”.
- 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.
- So as you can see we have changed cases in our excel sheet using a simple VBA code.
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.
- Finally, by performing a VBA code in our worksheet we have changed cases.
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.
- 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”.
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.
- Now choose the “Upper Case” and drag it to the “Values” section.
- Then drag the “Employee Name” to the “Rows” section.
- Simply we got our output in the same excel sheet by converting all the texts to upper case.
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.
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.
