How to Change the Case in Excel? While using Microsoft Excel, this will be a good thing to know how to change the case in Excel by the formula. If you know it then you don’t need to worry about if you are trying really fast and making mistakes in the upper or lower case. You will be able to fix all of these mistakes just by using a formula and dragging.
Upper Function in Excel
=UPPER(Cell Reference)Changes all charactors from any case to Upper case.
Lower Function in Excel
=Lower(Cell Reference)Changes all charactors from any case to lower case.
Proper Function in Excel
=Proper(Cell Reference)This formula understands what a word is and makes the first character of a word capital and rest in short. If the cell has multiple words then it will convert the first character of each word in the upper case and the rest in lower case.
How to Change the Case in Excel
If you are aware of how to make use of Microsoft Excel formulas. You will be aware that we use = (equal sign) to start the formula and then carry on. First, we make sure the content should be in a list (If you have a big list of words that you want to change the case of) because we will drag it after using the formula.
See the list of names below, we will be using and changing them into the upper case, lower case, and proper case by using Formulas in Excel.
Sample Excel Data
| List of Names |
| Jones |
| Kivell |
| Jardine |
| Gill |
| Sorvino |
| Jones |
| Andrews |
| Jardine |
| Thompson |
| Jones |
| Morgan |
| Howard |
| Parent |
| Jones |
| Smith |
| Jones |
| Morgan |
| Jones |
| Parent |
| Kivell |
| Smith |
| Parent |
| GilL |
| Smith |
| JoneS |
| Sorvino |
| Jardine |
| Andrews |
| Howard |
| Gill |
| Gill |
| Kivell |
| Jones |
| Morgan |
| Kivell |
| Sorvino |
| Gill |
| Sorvino |
| Thompson |
| Andrews |
| Jardine |
| Jardine |
| Andrews |
Before writing the formula you can decide where you want to put this data on the sheet. You don’t have to necessarily put this into the next cell. You have the freedom to put this data anywhere on the sheet.
Follow the Steps Below to Write the Formula.
- Select the Cell on the Sheet where you want to put the data.
- Type = (Equal Sign).
- Type Up and you will see the suggestion as UPPER.
- Press Tab on the keyboard as soon as you see the UPPER formula in the suggestion.
- Select the cell where the data is located (Make sure you are selecting the first word if you have a list of words).
- Type ) to complete the formula.
- Press Enter on the keyboard.
The steps above are not only for the UPPER function. These steps are the way to write a formula and use Upper Function in the formula. You can put Lower or Proper Function in this formula.
Using Flash Fill to Change Text Case in Excel
You just need to put the names in the list and then start typing the names in your desired format. Make sure you are typing in the same row as the list of the names. After typing the second name in your desired format, you will see the excel will suggest you the remaining names. Look at te screenshot underneath.
I am using Microsoft Excel 2019. In the 2019 version, Excel is giving me suggestions automatically without pressing short cut. If you will use some other version of Microsoft Excel, it might not give you suggestions. In that case, you need to type two names by yourself and then select all cells. Just like the screenshot here.
After selecting the cells, you can press Control + E or use the flash fill option in the Home Tab. Look at the screenshot below.
After pressing the shortcut key, Flash Fill will fill all the cells for you magically.
Flash fill can do a lot of things. I will be writing a whole new article on that later on.
Using Power Query to Change the Case in Excel
We can use power query and change the case. Use the Steps Below.
- Select the Data to make it a table.
- Click on Data Tab and then Click on From Table/Range.
- A box will appear to ask if the table has boarder.
- Check my table has a border option is the table has a border then press OK.
- A new window will open where we can set our data.
- Need to click on Add Column Tab and then Click on Format Option.
- Once we click on the Upper option, the new column will be added to our data with all upper case strings.
After reading this article I hope you have a good understanding of how to change the case in Excel.
Some people like to watch video tutorials and some like blog tutorials. For those who are fond of Youtube tutorials, I have created a video for you. You can watch it to understand the article better.
We also have articles on C# programming, you can take a look in case you are interested.
Take Care Guys, Stay Safe Stay Healthy!
