How to Change Letter Case in Excel (3 ways including NO Formulas) (2023)

Method #1: Using Formulas

The main advantage to using formulas is that if the source data changes, the updated formula version automatically updates.

In our data set, we have a list of names with a variety of issues. Some names are lower case, some are upper case, some are proper case, and some are mixed up beyond all reason.

We will create a version of each name in the list to upper case, lower case, and proper case using formulas. Each of these methods are incredibly simple.

Upper Case

The function to convert any cell’s text to upper case is known as theUPPERfunction. The syntax for theUPPERfunction is as follows:

=UPPER(text)

The variable “text” can refer to a cell address or to a statically declared string.

=UPPER(A1)

or

=UPPER(“This is a test of the upper function”)

In most cases, the cell reference version is the most useful option of the two.

In our sample file, we will select cellB5and enter the following formula:

=UPPER(A5)

Fill the formula down columnBto finish converting the list in columnA.

If you don’t want the formulas in the resultant cells, you just want the new upper-cased versions of the names as if they had been hand-typed, you can select the names and perform aCopy -> Past Valuesoperation on them.

A lesser-know technique to converting formulas to the formula’s results is to do the following:

• highlight the desired cells to be converted
• using your RIGHT mouse button, right-click on the thick, green border surrounding the selection
• drag a small amount away form the selection and then immediately return to the original selection location
• release your right mouse button

This presents you with a menu of choices. The choice we want is labeled “Copy Here as Values Only”.

Lower Case

The function to convert any cell’s text to upper case is known as theLOWERfunction. The syntax for theLOWERfunction is as follows:

=LOWER(text)

The variable “text” can refer to a cell address or to a statically declared string.

=LOWER(A1)

or

=LOWER(“THIS IS A TEST OF THE LOWER FUNCTION”)

As with theUPPERfunction, the cell reference version is the most useful option of the two.

In our sample file, we will select cellC5and enter the following formula:

=LOWER(A5)

Fill the formula down columnCto finish converting the list in columnA.

Proper Case

The function to convert any cell’s text to upper case is known as thePROPERfunction. The syntax for thePROPERfunction is as follows:

=PROPER(text)

The variable “text” can refer to a cell address or to a statically declared string.

=PROPER(A1)

or

=PROPER(“THIS IS A TEST OF THE PROPER FUNCTION”)

In our sample file, we will select cellD5and enter the following formula:

=PROPER(A5)

Fill the formula down columnDto finish converting the list in columnA.

Bonus Problem to Solve

Notice in our solution columns (B:D), “James Willard” has an extra space between his first and last names.

A less obvious issue is that “Gary Miller” has an extra space at the end of his name.

If you need to remove any unnecessary leading spaces, trailing spaces, or multiple spaces in between words, you can use theTRIMfunction. The syntax for theTRIMfunction is as follows:

=TRIM(text)

The variable “text” can refer to a cell address or to a statically declared string.

=TRIM(A1)

or

= TRIM(“ This is a test of the TRIM function ”)

In our sample file, we will select cellE5and enter the following formula:

=TRIM(A5)

Fill the formula down columnEto finish converting the list in columnA.

We can combine these functions to both trim and fix text casing. Suppose we wish to convert the text to upper case and trim all the extraneous spaces. We can write the formula two different ways.

=UPPER(TRIM(A3))

or

=TRIM(UPPER(A3))

Either version produces the desired results. Pick the one that makes the most sense to your brain.

Method #2: Flash Fill

The advantage ofFlash Fillis that it doesn’t require the use of functions and the result is like theCopy -> Paste Valuesaction in that the result cells contain the text, not formulas.

The disadvantage is that there is no dynamic connection back to the original list of text. If the original list changes, the “fixed” version of the list does not update. This is okay if you have a static list or you only need to perform the conversion one time and you don’t require updates.

There are many ways to use Flash Fill, but a simple way is to type on the same row as the data a version of the data as you WISH it were formatted.

After you press ENTER to commit the new version to a cell, press the keyboard combinationCTRL-E.

The system will look for patterns in what you typed against other text on the same row. If a pattern exists, such as “I see the text you typed, but you typed it in upper case letters”, the system will repeat the pattern for the remainder of the rows in the table.

The Flash Fill tool can also be activated by selectingHome (tab) -> Editing (group) -> Fill (button) -> Flash Fill.

Another way to activate theFlash Filltool is to selectData (tab) -> Data Tools (group) -> Flash Fill.

If we use theFlash Filltechnique to convert the names in columnAto upper case version in columnB, notice that “James Willard” still has the extra space between his names.

Flash Fillcan fix that problem as well.Flash Fillcan perform theTRIMand theUPPERfunctions simultaneously. The trick is to select a name that contains extra spaces before, after, or within itself. In this case, we will use “james willard”.

In cellB4, type “JAMES WILLARD” with only a single space separating the first from the last name.

After you press ENTER, press the keyboard combinationCTRL-E.

Flash Fillwill fix the names in BOTH directions of the list. Not only will it create upper case versions of the names, but it is smart enough to detect that you only placed a single space between the names and that it should do the same thing. Also, because you didn’t add any additional leading or training spaces,Flash Filldoesn’t place any in the results list of names.

AlthoughFlash Fillis a fantastic tool that can quickly correct may data entry mishaps, it isn’t perfect. If there isn’t a recognizable pattern, or if it perceives multiple patterns, it may produce unexpected results. It’s always a good idea to double-check the output ofFlash Fillfor accuracy.

Method #3: ALL CAPS FONT

The advantage of this method is lack of composing any formulas or using Flash Fill.

Imagine a scenario where you always want a page title to be in upper case, but you don’t want to worry about how the user type the title. If the user enters the title in lower case, proper case, or sentence case, we want the typed text to automatically convert to upper case.

We can accomplish this by using a font that contains no lower case version of the letters.

When you are browsing through your list of fonts, you can tell if a font is upper case only because the font name will be in all upper case letters.

Some of the supplied fonts in Microsoft Windows/Office that contain only upper case letters are:

• Copperplate Gothic
• Engravers
• Felix Tilting
• Stencil

You are not restricted to fonts that came with Windows or Office. Many websites exist that provide both free and “pay-to-play” fonts.

• WebsitePlant.com
• 1001freefonts.com
• dafont.com
• fontsquirrel.com

When you are downloading a font from one of these sites, or other font supplier’s sites, be mindful that some fonts are free for personal use, but other fonts may require a fee when used in a business capacity.

When you download and install the font based on the website’s installation instructions, the font will be available to all your Windows and Office applications, not just Excel.

Using the Newly Installed Font

With the font installed, we return to Excel and select a cell where we will enter our title.

From the font dropdown list, select the desired font that contains all upper case letters.

It doesn’t matter weather you type your title in upper case, lower case, or mixed case, the result will always be in an upper case format.

Using Cell Styles to Expedite Font Assignment

Locating a specific font for all for all your titles can be time consuming, especially if you must repeatedly scroll through long list of font choices.

A timesaving way to apply an ALL CAPS font to a cell is to utilizeCell Styles.

Cell Styles are located on theHometab in theStylesgroup.

You have the option to use an existing style, create your own style and add it to the library, or modify an existing style.

If we wish to use theHeading 1style, but we wish it to be in all upper case letters, right-click on theHeading 1style and selectModify.

In theStyledialog box, click theFormatbutton.

In theFormat Cellsdialog box, select theFonttab and set the font to the desired ALL CAPS font. You can also use this opportunity to set the font color, underline color, border color, etc…

We can now select a cell and type in our new title. Once entered, with the title cell selected, click theHeading 1style from theCell Styleslist.

Practice Workbook

Feel free to Download the WorkbookHERE.