When we type new text into a cell in Excel, we can capitalize it by using the shift key as we type the letters. We can type in all caps using the caps lock key. But what can we do if we need to change the case of existing text in a range of cells?
Table of Contents
In Microsoft Word, changing text case is as simple as selecting the text and clicking a button in the ribbon. But you won’t find that button or menu item in Excel.
However, there is a way that we can easily change a large amount of text in Excel to upper case, lower case, or normal case.
That is where the UPPER, LOWER, and PROPER text functions come in handy. At first, it may not be obvious how to use those functions, but it’s not difficult with a little guidance.
We covered using a function to build jump to links in Excel. This process is no harder.
Using Text Functions to Capitalize in Excel
In this example, we’ll use a simple list of animals in one Excel column. The names of the animals are in lowercase.
To change the names of those animals to uppercase, we’ll first need to move to an empty column.
The following four steps are the same in Excel for Windows or Mac:
1. Click your mouse in the first cell of the empty column.
2. Open the Formulas tab.
3. Click the Text button. (May be large or small, depending on the width of your display.)
4. Toward the bottom of the list, click UPPER.
At this point, you should see the Function Arguments panel open center-screen in Excel for Windows or the Formula Builder panel along the right border in Excel for Macs. They both do the same thing in this exercise.
In either panel, type in the reference cell range that contains the text that you are converting to uppercase.
In this example, the first cell in that range is A1. The last is A10. You would type that in the formula panels as A1:A10.
Click the Done button in Excel for Macs or the OK button in Excel for Windows.
You should see the uppercase versions of your original text in the formerly blank column where you entered the UPPER function.
At this point, you can select the uppercase text by clicking in the first cell where the first word is. Then, hold the Shift key and click in the last cell containing the uppercase words.
Right-click that selection and choose Copy.
Go to the column that contains the original text. Select the words in that column the same way you selected the uppercase text.
Right-click and choose Paste Special, then choose Values.
Important: Be sure you choose to paste Values only. If you just choose Paste or Formulas, you will see a function reference error.
You should see your lowercase words in the original column replaced by uppercase words.
To finish, delete the contents of the cells in the other column where you inserted the UPPER function.
An alternative method of selecting cells for the formula
If you had difficulty entering the range of cells in the Function Arguments or Formula Builder panel, you can select them manually.
Follow the original steps until you have selected UPPER from the list of functions.
From there, in Excel for Windows:
Place your cursor in the first cell of the original words (in this case, A1).
Hold the mouse button down and drag downward until all of the words have been selected.
Hit the Enter key.
Note: If you happen to accidentally close the Function Arguments panel without entering or selecting your cell range, a warning will pop up. Click OK to make it go away.
In Excel for Macs:
Select the entire original list of words by clicking the first one and dragging down to the last one.
Either click the Done button in the Formula Builder panel or click your mouse just past the reference range in the Formula bar and hit the Return key.
Changing text to lowercase or proper case in Excel
Excel also provides functions to convert text to all lowercase and to proper case. In proper case, the first letter of each word in capitalized.
The process for converting text to either of those choices is exactly the same as converting from lower to upper. Instead of choosing UPPER from the text functions list, you’ll choose LOWER or PROPER.
Microsoft offers more information about the use of functions.