How to Capitalize all Letters in Excel – 3 Easy Method

how to capitalize all letters in excel
EasyExcel
13 Min Read

How do we capitalize all letters in excel or change the case of text in Excel to normalize our data or to fix casing errors?

Changing lower case text to upper case text, upper case text to proper case text (where the first letter is upper case, and the remaining letters are lower case), or mixed-case to upper case is accomplished by the push of a button in Microsoft Word…

How to Capitalize all Letters in Excel 3 easy method

…but how do we accomplish this in Microsoft Excel?

Let’s explore three ways to solve this problem; each having their own advantages and disadvantages.

Method #1: Change Case 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 want to fix this and change case in Excel.

Change Case Using Formulas

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 the UPPER function.  The syntax for the UPPER function 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 cell B5 and enter the following formula to capitalize text:

=UPPER(A5)
Upper Case using formula

Fill the formula down column B to finish converting the list in column A from lower case to upper case.

Upper Case using formula in Excel

But let’s say you don’t want the formulas in the results. You just want the new upper-cased versions of the names as if they had been hand-typed. There is an easy fix for that:

  • Select the names and perform a Copy -> Paste Values operation on them.
Upper Case using paste value in Excel

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

Upper Case using paste value in Excel 2021

Lower Case

Now we want to convert uppercase to lowercase. The Excel function we’ll use is the LOWER function.  The syntax for the LOWER function 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 the UPPER function, the cell reference version is the most useful option of the two.

In our practice file, we will select cell C5 and enter the following formula to convert to lower case.

=LOWER(A5)
Lower Case in excel

Fill the formula down column C to finish converting the list in column A.

Lower Case using formula in Excel 2021

Proper Case

Let’s find out how to capitalize the first letter in Excel. The PROPER function makes it easy. It converts the first letter of each word in a text string to uppercase. This is helpful for formatting names, titles, and more. 

The syntax for the PROPER function is as follows:

=PROPER(text)

The “text” can be a cell reference or a string. Here are some examples:

  • Cell Reference: =PROPER(A1)
  • Text String: =PROPER(“this is a test”)

In our practice file, we will select cell D5 and enter the following formula to convert the text to proper case.

=PROPER(A5)
Proper Case using formula in Excel

Fill the formula down column D to finish converting the list in column A.

Proper Case using formula in Excel

Bonus Problem to Solve

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

remove extra space at the end of word

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

remove extra space at the end of word

If you need to remove any unnecessary leading spaces, trailing spaces, or multiple spaces in between words, you can use the TRIM function.  The syntax for the TRIM function 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 cell E5 and enter the following formula:

=TRIM(A5)
Trim function in excel

Fill the formula down column E to finish converting the list in column A.

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: Change Case with Flash Fill

The advantage of Flash Fill is that it doesn’t require the use of functions and the result is like the Copy -> Paste Values action 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.

Change Case with Flash Fill

After you press ENTER to commit the new version to a cell, press the keyboard combination CTRL + E.

Change Case with Flash Fill

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 selecting Home (tab) -> Editing (group) -> Fill (button) -> Flash Fill.

Flash fill function in excel

Another way to activate the Flash Fill tool is to select Data (tab) -> Data Tools (group) -> Flash Fill.

Change Case with Flash Fill

If we use the Flash Fill technique to convert the names in column A to upper case version in column B, notice that “James Willard” still has the extra space between his names.

Flash Fill can fix that problem as well.  Flash Fill can perform the TRIM and the UPPER functions 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 cell B4, type “JAMES WILLARD” with only a single space separating the first from the last name.

Change Case with Flash Fill

After you press ENTER, press the keyboard combination CTRL + E.

Flash Fill will 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 Fill doesn’t place any in the results list of names.

Although Flash Fill is 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 of Flash Fill for 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 capitalized, 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.

ALL CAPS FONT

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.

When you are downloading a font from 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.

Using the Newly Installed Font

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

Using the Newly Installed Font

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 utilize Cell Styles.

Cell Styles are located on the Home tab in the Styles group.

Using Cell Styles to Expedite Font Assignment

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 the Heading 1 style, but we wish it to be in all upper case letters, right-click on the Heading 1 style and select Modify.

Using Cell Styles to Expedite Font Assignment

In the Style dialog box, click the Format button.

Using Cell Styles to Expedite Font Assignment

In the Format Cells dialog box, select the Font tab 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…

Using Cell Styles to Expedite Font Assignment

We can now select a cell and type in our new title.  Once entered, with the title cell selected, click the Heading 1 style from the Cell Styles list.

Cell Styles function in excel

for more visit our insta EasyExcel or our blogs

Share This Article
Leave a Comment