How to convert uppercase in excel

Change the case of text

Excel

Enter and format data

Format data

Format data

Change the case of text

Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel for the web Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 for Mac Excel 2016 Excel 2016 for Mac Excel 2013 Excel for iPad Excel for iPhone Excel for Android tablets Excel 2010 Excel 2007 Excel for Mac 2011 Excel for Android phones Excel Mobile More...Less

Unlike Microsoft Word, Microsoft Excel doesn't have a Change Case button for changing capitalization. However, you can use the UPPER, LOWER, or PROPER functions to automatically change the case of existing text to uppercase, lowercase, or proper case. Functions are just built-in formulas that are designed to accomplish specific tasks—in this case, converting text case.

How to Change Case

In the example below, the PROPER function is used to convert the uppercase names in column A to proper case, which capitalizes only the first letter in each name.

1. First, insert a temporary column next to the column that contains the text you want to convert. In this case, we’ve added a new column (B) to the right of the Customer Name column.

   In cell B2, type =PROPER(A2), then press Enter.
   

   This formula converts the name in cell A2 from uppercase to proper case. To convert the text to lowercase, type =LOWER(A2) instead. Use =UPPER(A2) in cases where you need to convert text to uppercase, replacing A2 with the appropriate cell reference.

2. Now, fill down the formula in the new column. The quickest way to do this is by selecting cell B2, and then double-clicking the small black square that appears in the lower-right corner of the cell.
   
    

   Tip: If your data is in an Excel table, a calculated column is automatically created with values filled down for you when you enter the formula.

3. At this point, the values in the new column (B) should be selected. Press CTRL+C to copy them to the Clipboard.
   

   Right-click cell A2, click Paste, and then click Values. This step enables you to paste just the names and not the underlying formulas, which you don’t need to keep.

4. You can then delete column (B), since it is no longer needed.

Need more help?

You can always ask an expert in the Excel Tech Community or get support in the Answers community.

See Also

Use AutoFill and Flash Fill

5 Ways To Change Text Case In Excel

You’ve probably come across this situation before.

You have a list of names and it’s all lower case letter. You need to fix them so they are all properly capitalized.

With hundreds of names in your list, it’s going to be a pain to go through and edit first and last names.

Thankfully, there are some easy ways to change the case of any text data in Excel. We can change text to lower case, upper case or proper case where each word is capitalized.

Download Example Files

In this post, we’re going to look at using Excel functions, flash fill, power query, DAX and power pivot to change the case of our text data.

Video Tutorial

Using Excel Formulas To Change Text Case

The first option we’re going to look at is regular Excel functions. These are the functions we can use in any worksheet in Excel.

There’s a whole category of Excel functions to deal with text, and these three will help us to change the text case.

LOWER Excel Worksheet Function

=LOWER(Text)

The LOWER function takes one argument which is the bit of Text we want to change into lower case letters. The function will evaluate to text that is all lower case.

UPPER Excel Worksheet Function

=UPPER(Text)

The UPPER function takes one argument which is the bit of Text we want to change into upper case letters. The function will evaluate to text that is all upper case.

PROPER Excel Worksheet Function

=PROPER(Text)

The PROPER function takes one argument which is the bit of Text we want to change into proper case. The function will evaluate to text that is all proper case where each word starts with a capital letter and is followed by lower case letters.

Copy And Paste Formulas As Values

After using the Excel formulas to change the case of our text, we may want to convert these to values.

This can be done by copying the range of formulas and pasting them as values with the paste special command.

Press Ctrl + C to copy the range of cells ➜ press Ctrl + Alt + V to paste special ➜ choose Values from the paste options.

Using Flash Fill To Change Text Case

Flash fill is a tool in Excel that helps with simple data transformations. We only need to provide a couple examples of the results we want, and flash fill will fill in the rest.

Flash fill can only be used directly to the right of the data we’re trying to transform. We need to type out a couple of examples of the results we want. When Excel has enough examples to figure out the pattern, it will show the suggested data in a light grey font. We can accept this suggested filled data by pressing Enter.

We can also access flash fill from the ribbon. Enter the example data ➜ highlight both the examples and cells that need to be filled ➜ go to the Data tab ➜ press the Flash Fill command found in the Data Tools section.

We can also use the keyboard shortcut Ctrl + E for flash fill.

Flash fill will work for many types of simple data transformations including changing text between lower case, upper case and proper case.

Using Power Query To Change Text Case

Power query is all about data transformation, so it’s sure there is a way to change the case of text in this tool.

With power query we can transform the case into lower, upper and proper case.

Select the data we want to transform ➜ go to the Data tab ➜ select From Table/Range. This will open up the power query editor where we can apply our text case transformations.

Text.Lower Power Query Function

Select the column containing the data we want to transform ➜ go to the Add Column tab ➜ select Format ➜ select lowercase from the menu.

= Table.AddColumn(#"Changed Type", "lowercase", each Text.Lower([Name]), type text)

This will create a new column with all text converted to lower case letters using the Text.Lower power query function.

Text.Upper Power Query Function

Select the column containing the data we want to transform ➜ go to the Add Column tab ➜ select Format ➜ select UPPERCASE from the menu.

= Table.AddColumn(#"Changed Type", "UPPERCASE", each Text.Upper([Name]), type text)

This will create a new column with all text converted to upper case letters using the Text.Upper power query function.

Text.Proper Power Query Function

Select the column containing the data we want to transform ➜ go to the Add Column tab ➜ select Format ➜ select Capitalize Each Word from the menu.

= Table.AddColumn(#"Changed Type", "Capitalize Each Word", each Text. Proper([Name]), type text)

This will create a new column with all text converted to proper case lettering, where each word is capitalized, using the Text.Proper power query function.

Using DAX Formulas To Change Text Case

When we think of pivot tables, we generally think of summarizing numeric data. But pivot tables can also summarize text data when we use the data model and DAX formulas. There are even DAX formula to change text case before we summarize it!

First, we need to create a pivot table with our text data. Select the data to be converted ➜ go to the Insert tab ➜ select PivotTable from the tables section.

In the Create PivotTable dialog box menu, check the option to Add this data to the Data Model. This will allow us to use the necessary DAX formula to transform our text case.

Creating a DAX formula in our pivot table can be done by adding a measure. Right click on the table in the PivotTable Fields window and select Add Measure from the menu.

This will open up the Measure dialog box, where we can create our DAX formulas.

LOWER DAX Function

=CONCATENATEX( ChangeCase, LOWER( ChangeCase[Mixed Case] ), ", ")

We can enter the above formula into the Measure editor. Just like the Excel worksheet functions, there is a DAX function to convert text to lower case.

However, in order for the expression to be a valid measure, it will need to be wrapped in a text aggregating function like CONCATENATEX. This is because measures need to evaluate to a single value and the LOWER DAX function does not do this on it’s own. The CONCATENATEX function will aggregate the results of the LOWER function into a single value.

We can then add the original column of text into the Rows and the new Lower Case measure into the Values area of the pivot table to produce our transformed text values.

Notice the grand total of the pivot table contains all the names in lower case text separated by a comma and space character. We can hide this part by going to the Table Tools Design tab ➜ Grand Totals ➜ selecting Off for Rows and Columns.

UPPER DAX Function

=CONCATENATEX( ChangeCase, UPPER( ChangeCase[Mixed Case] ), ", ")

Similarily, we can enter the above formula into the Measure editor to create our upper case DAX formula. Just like the Excel worksheet functions, there is a DAX function to convert text to upper case.

Creating the pivot table to display the upper case text is the same process as with the lower case measure.

Missing PROPER DAX Function

We might try and create a similar DAX formula to create proper case text. But it turns out there is no function in DAX equivalent to the PROPER worksheet function.

Using Power Pivot Row Level Formulas To Change Text Case

This method will also use pivot tables and the Data Model, but instead of DAX formulas we can create row level calculations using the Power Pivot add-in.

Power pivot formulas can be used to add new calculated columns in our data. Calculations in these columns happen for each row of data similar to our regular Excel worksheet functions.

Not every version of Excel has power pivot available and you will need to enable the add-in before you can use it. To enable the power pivot add-in, go to the File tab ➜ Options ➜ go to the Add-ins tab ➜ Manage COM Add-ins ➜ press Go ➜ check the box for Microsoft Power Pivot for Excel.

We will need to load our data into the data model. Select the data ➜ go to the Power Pivot tab ➜ press the Add to Data Model command.

This is the same data model as creating a pivot table and using the Add this data to the Data Model checkbox option. So if our data is already in the data model we can use the Manage data model option to create our power pivot calculations.

LOWER Power Pivot Function

=LOWER(ChangeCase[Mixed Case])

Adding a new calculated column into the data model is easy. Select an empty cell in the column labelled Add Column then type out the above formula into the formula bar. You can even create references in the formula to other columns by clicking on them with the mouse cursor.

Press Enter to accept the new formula.

The formula will appear in each cell of the new column regardless of which cell was selected. This is because each row must use the same calculation within a calculated column.

We can also rename our new column by double clicking on the column heading. Then we can close the power pivot window to use our new calculated column.

When we create a new pivot table with the data model, we will see the calculated column as a new available field in our table and we can add it into the Rows area of the pivot table. This will list out all the names in our data and they will all be lower case text.

UPPER Power Pivot Function

=UPPER(ChangeCase[Mixed Case])

We can do the same thing to create a calculated column that converts the text to upper case by adding a new calculated column with the above formula.

Again, we can then use this as a new field in any pivot table created from the data model.

Missing PROPER Power Pivot Function

Unfortunately, there is no power pivot function to convert text to proper case. So just like DAX, we won’t be able to do this in a similar fashion to the lower case and upper case power pivot methods.

Conclusions

There are many ways to change the case of any text data between lower, upper and proper case.

• Excel Formulas are quick, easy and will dynamically update if the inputs ever change.
• Flash fill is great for one-off transformations where you need to quickly fix some text and don’t need to update or change the data after.
• Power query is perfect for fixing data that will be imported regularly into Excel from an outside source.
• DAX and power pivot are can be used for fixing text to display within a pivot table.

Each option has different strengths and weaknesses so it’s best to become familiar will all methods so you can choose the one that will best suit your needs.

About the Author

John is a Microsoft MVP and qualified actuary with over 15 years of experience. He has worked in a variety of industries, including insurance, ad tech, and most recently Power Platform consulting. He is a keen problem solver and has a passion for using technology to make businesses more efficient.

Changing the case of text in Excel

Lowercase

Unlike Word, Excel does not have a case change button. To translate text into lowercase - for example, so that instead of "SERGEY IVANOV" or "Sergey Ivanov" it becomes "Sergey Ivanov" - you need to use the function "LOWER". The advantage of using a function is that you can change the case of an entire column of text at once. The example below shows how to do this.

1. Insert a new column next to the column containing the text you want to convert. Assume that the new column is column B and the original column is column A, and that cell A1 contains the column heading.

2. In cell B2, type =LOWER(A2) and press Enter. The text in cell B2 should become inline.

3. Fill in column B with this formula.

4. Now select the converted values ​​in column B, copy them and paste as values ​​over the values ​​in column A.

5. Delete column B as you don't need it anymore.

Uppercase

Unlike Word, Excel does not have a case change button. To convert the text to uppercase - for example, so that instead of "Sergey Ivanov" or "Sergey Ivanov" it becomes "SERGEY IVANOV" - you need to use the "UPPER" function. The advantage of using a function is that you can change the case of an entire column of text at once. The example below shows how to do this.

1. Insert a new column next to the column containing the text you want to convert. Assume that the new column is column B and the original column is column A, and that cell A1 contains the column heading.

2. In cell B2, type =UPPER(A2), and then press Enter. The text in cell B2 should change to uppercase.

3. Fill in column B with this formula.

4. Now select the converted values ​​in column B, copy them and paste as values ​​over the values ​​in column A.

5. Delete column B as you don't need it anymore.

Capitalize each word

Unlike Word, Excel does not have a case change button. To convert text so that all words in the text are capitalized - for example, so that instead of "Sergey IVANOV" or "SERGEY IVANOV" becomes "Sergey Ivanov" - you need to use the PROPCH function. The advantage of using the function is that you You can change the case of an entire column of text at once. The example below shows how to do this.

1. Insert a new column next to the column containing the text you want to convert. Assume that the new column is column B and the original column is column A, and that cell A1 contains the column heading.

2. In cell B2, type =PROPER(A2) and press Enter. The text in cell B2 should change case.

3. Fill in column B with this formula.

4. Now select the converted values ​​in column B, copy them and paste as values ​​over the values ​​in column A.

5. Delete column B as you don't need it anymore.

Text case change

excel

Entering and formatting data

Data Formatting

Data Formatting

Change case of text

Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel for the web Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 for Mac Excel 2016 Excel 2016 for Mac Excel 2013 Excel for iPad Excel for iPhone Excel for Android tablets Excel 2010 Excel 2007 Excel for Mac 2011 Excel for Android phones Excel Mobile More...Less

In Microsoft Word, Microsoft Excel does not have a Edit Case button to change letters. However, the UPPER, LOWER, and PROPER functions can be used to automatically change the case of existing text to upper, lower, or proper case. Functions are built-in formulas designed to perform specific tasks, in this case text transformations.