In this blog we will cover how to change the case of your text in Excel with or without a formula.
If you're a keen user of the Change Case button in Word, Outlook or PowerPoint you may be wondering where it is in Excel.
The Change Case button in Word
Let’s look at an example of where the Change Case button is in Word.
It is located on the Home tab (Aa).
Click on the drop down arrow and you will see all the different case options that are available to change your text.
Highlight the text you want to change and select the case option you want.
It's nice and easy in Word but where is it in Excel?
The Change Case button in Excel
In Excel there is no Change Case button (icon) on the Home tab.
So, how can you change your case in Excel?
Change case without functions
If you're in the newer versions of Excel, you can use Flash Fill.
Flash Fill will automatically fill in information in a list if you already have that information in your table. For example, the Surnames in column A.
If you start typing the same surnames in column D, Flash Fill will instantly drop down the surnames from column A.
Press your Enter key and it will add these surnames, giving you the same information as column A.
You can take it one step further and show Flash Fill what case you want all the surnames in. For example, green in column D7.
Overtype green to Green and press Enter.
Flash Fill will now change all the case in that column to the same as Green.
One thing that should be looked out for is Flash Fill can change the case of the word you have just written. For example, if you were to write Frank in cell E4 and Carole in cell E5.
Once you start typing Carole, Flash Fill will drop down all the other names.
It has changed Carole back to carole as this is the original case the name was in from column B.
If you don't get the case you want, do this.
Highlight over the information that has been filled in and delete it.
Overtype the information that has been changed back to what you want it to be. For this example, carole is being changed back to Carole.
Then highlight over the area where you want the information to be and include your two options at the top such as Frank and Carole.
Go to the Data tab.
Click the Flash Fill button on the right of the ribbon.
You should now have all your information copied over in the correct case.
Useful tip: Instead of using the Flash Fill button you can use the shortcut Ctrl + E.
This is great if you’re using the newer versions of Excel, but what if you’re using older versions such as Excel 2010 or older and don’t have the Flash Fill button?
Changing case in older versions of Excel using Word
Copy the information you want to change the case of by highlighting (for example, the first names in column B ) and pressing Ctrl + C or use a right mouse click and select Copy.
Go into a Word document and paste it using Ctrl + V or doing a right mouse click and selecting paste.
Your information should have pasted into Word as a table.
Highlight over this list and use your Change Case button to change the case of the text. You can select whatever case suits you.
Highlight the list and copy it.
In Excel, paste it where you want it to be. For example, column F in this worksheet.
How to change case in Excel with formulas
Functions can be used in order to change your case. For example, the LOWER, UPPER and PROPER functions.
The functions to use are:
=UPPER(cell reference) to change the cell contents to UPPER case.
=LOWER(cell reference) to change the cell contents to lower case.
=PROPER(cell reference) to change the cell contents to Proper case.
The examples below show how to change data to each type of case.
The formulas displayed in column C are the formulas used in column B.
Uppercase to lowercase in Excel
Use the LOWER function to convert uppercase text to lowercase text. You can also use the Lower function to change proper case to lowercase.
To do this, select the cell where you want the lowercase letters. For example, cell B10.
Start by typing =LOWER(
Now, click onto the cell in which you want to change the case. For example, cell A10 in the example above.
Insert a closing bracket to give the formula =LOWER(A10)
You should have the text from the original cell (A10) all in lowercase in the new cell (B10).
In the example above this formula has then been copied into cells B11 and B12.
Useful Tip: When you start to type the name of a function Excel will show it in a quick tip under the cell. To drop it into your cell, you can press your Tab key so that you don’t have to type the word out completely.
Change to UPPERCASE in Excel
Use the UPPER function to convert text to uppercase. You can also use the Upper function to change proper case to uppercase.
In the example below, to change 'sarah gray' in A6 to uppercase, =UPPER(A6) was typed into into cell B6. This returned 'SARAH GRAY' into cell B6. The formula has then been copied into cells B7 and B8.
Use Proper function to change text to proper case
Use the PROPER function, to have Excel capitalize the first letter of each word.
In the example below, =PROPER(A2) was typed into cell B2 in order to change 'stacey white' from A2 to 'Stacey White' in B2. The formula has then been copied into cells B3 and B4.
Replace the formulas with the actual text in Excel
If you need to replace the formulas with values (remove the function and leave the text) please check out my blog post How to remove Excel formulas and leave the result and follow the instruction on how to Replace formulas with values.
Was this Blog helpful? Let us know in the comments below.