April 27

0 comments

Change Case in Excel

By Sharyn Baines

April 27, 2021

Change Case, Functions, LOWER, PROPER, UPPER

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).

Excel Change Case

Click on the drop down arrow and you will see all the different case options that are available to change your text.

Excel Change Case

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.

Excel Change Case

So, how can you change your case in Excel?

Change case without functions

Flash Fill

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.

Excel Change Case

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.

Excel Change Case

Overtype green to Green and press Enter.

Flash Fill will now change all the case in that column to the same as Green. 

Excel Change Case

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.

Excel Change Case

Once you start typing Carole, Flash Fill will drop down all the other names.

Press Enter.

It has changed Carole back to carole as this is the original case the name was in from column B.

Excel Change Case

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.

Excel Change Case

Go to the Data tab.

Click the Flash Fill button on the right of the ribbon.

Excel Change Case

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

Excel Change Case

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.

Excel Change Case

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.

Excel Change Case

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.

Excel Change 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.

Excel Change Case

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)

Press Enter.

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.

Excel Change Case

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.

Excel Change Case

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.

Excel Change Case

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.

Sharyn Baines

About the author

Sharyn is an expert trainer. She became the first certified Microsoft® MOUS Authorised Instructor in New Zealand. She is endorsed by Microsoft® as a qualified Microsoft® Office Specialist and has more than 20 years of experience in the training industry, developing and delivering technology training workshops. Her approach to taking the “techie-speak” out of technology training has placed her as a preferred supplier to many of New Zealand’s leading organisations.

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

JOIN FREE! 'Excel at Work Insiders Group' for free tutorials, mini-courses, videos and blogs!

>