Have you ever needed to quickly change the text case in your Google Sheets? Whether you want to convert lowercase to uppercase or vice versa, Google Sheets has three different functions that can automatically modify the case of the text entered in the cells. The UPPER function will capitalize ALL letters, the LOWER function will change all letters to lowercase, and the PROPER function will capitalize the first letter of each word.
Optional formula to use: You can use autofill to quickly copy the formula down the column, or combine your formula with the ARRAYFORMULA function to apply the formula to an entire column.
Click here to get your Google Sheets cheat sheet. Or click here to take the dashboard course.
Examples of Changing Text Case
Here are three basic examples that demonstrate how the UPPER, LOWER, and PROPER functions affect the text when applied to a single cell containing the following sentence:
“EXAMPLE: See how the letters in this sentence change when you apply different case-changing formulas in Google Sheets.”
Note that the above sentence contains a variety of words. Some words are all lowercase, some words are all uppercase, and some words have their first letter capitalized. This will allow you to see how the case-changing formulas affect different letters and words, simply by referring to a single cell.
Changing All Letters to Uppercase
Formula: =UPPER(A1)
Changing All Letters to Lowercase
Formula: =LOWER(A1)
Capitalizing the First Letter of Each Word
Formula: =PROPER(A1)
Note that in the above examples, each letter is capitalized when the UPPER function is used, each letter is changed to lowercase when the LOWER function is used, and only the first letter of each word is capitalized when the PROPER function is used.
How to Capitalize Text in Google Sheets with the UPPER Function
The UPPER function in Google Sheets capitalizes every letter in the referenced cell.
In this example, we will use the UPPER function to capitalize every letter in a list of names.
In the example image below, you will see the original list of unmodified names displayed in the normal format you expect for a name (only the first letter of each word/name is capitalized).
But let’s say, for some reason, your task/job requires having all names in uppercase. For example, you might need to display the names this way for a financial record, or perhaps you are using case-sensitive formulas for searching/matching information.
By using the UPPER function in a Google Sheets, you can automatically change every letter to uppercase, according to the uppercase format you need.
Task: Capitalize every letter in a list of names.
Logic: Use the UPPER function to capitalize the letters in the text in the range A2:A15.
Formula: The formula below is initially entered in cell C2, then copied to the cells below for this example.
=UPPER(A2)
Notice that in the above example, the letters that were already uppercase remained uppercase, and the letters that were initially lowercase are now uppercase.
How to Change Text to Lowercase in Google Sheets with the LOWER Function
Now, let’s move on to changing all letters in a list of names to lowercase. We will do this using the LOWER function.
The LOWER function in Google Sheets changes each letter in the referenced cell to lowercase.
In this example, let’s assume we have a list of names that have been manually entered and submitted by staff members, and we want to clean them up and convert them to lowercase.
In the example image below, you can see that the original list of names is messy, with names having different combinations of lowercase and uppercase letters. Some names are all uppercase, and others have a mix of uppercase and lowercase letters.
Task: Change the letters in a list of names to lowercase.
Logic: Use the LOWER function to change all letters in the range A2:A15 to lowercase.
Formula: The formula below is initially entered in cell C2, then copied to the cells below for this example.
=LOWER(A2)
Notice that in the above example, the letters that were already lowercase remained lowercase, and the letters that were initially uppercase have now been changed to lowercase.
How to Capitalize Each Word in Google Sheets with the PROPER Function
If you simply want to capitalize the first letter of each word in Google Sheets, you can use the PROPER function to achieve this.
The PROPER function in Google Sheets changes the case of the text in the referenced cell so that only the first letter of each word is capitalized. This means it will not only capitalize the first letter, but also change any other letters that are not the first letter of a word to lowercase. This is sometimes referred to as “Title Case.”
In this example, we will use the PROPER function to capitalize the first letter of each name (first name and last name) listed in a column.
In the example image below, the original list of names we are going to modify is even messier than the list of names in the previous example. Some of the names go from lowercase to all uppercase letters, making the list look very unprofessional and, in many cases, can make the data difficult to reliably use when case-sensitive criteria need to be respected in formulas.
Let’s say your boss has given you the list of names shown below and asked you to put them in a nice format where only the first letter of each name is capitalized. This would be very tedious to do manually, but luckily, you can do it instantly using the PROPER function.
Task: Capitalize only the first letter of each name.
Logic: Capitalize the first letter of each name in the range A2:A15.
Formula: The formula below is initially entered in cell C2, then copied to the cells below for this example.
=PROPER(A2)
Notice that in the above example, only the first letter of each word has been capitalized, and any letters that were initially uppercase but are not the first letter of a word have been changed to lowercase.
How to Apply Case-Changing Formulas to an Entire Column
In the above examples, we were able to change the case of multiple names in a column by copying the original formula to the cells below, so that each cell had a formula applied to it.
However, you might want to change the text in an entire column to uppercase or lowercase using just one formula. This can be done by combining your case-changing formula with the ARRAYFORMULA function.
To change the case of text in an entire column in Google Sheets, choose your function (UPPER, LOWER, or PROPER), wrap your formula with the ARRAYFORMULA function, and reference a range of cells in your formula instead of a single cell.
In this example, I will use the UPPER function to show how to do this, using the same list of names as in the first example. However, you will notice that instead of using multiple formulas to change each name to uppercase, we will accomplish the same task using just one formula.
Task: Change the names in the column to uppercase.
Logic: Use the UPPER function with the ARRAYFORMULA function to capitalize all letters in column A.
Formula: The formula below is entered in the blue cell (C2) for this example.
=arrayformula(UPPER(A2:A))
Surprise Quiz: Test Your Knowledge
Answer the questions below about changing text case to test your knowledge! Scroll to the bottom for the quiz answers.
Question 1:
Which formula among the following will capitalize all letters?
=PROPER(A9)=LOWER(B11)=UPPER(C12)
Question 2:
Which formula among the following will only capitalize the first letter of each word?
=PROPER(J1)=LOWER(J2)=UPPER(J3)
Question 3:
True or False: The PROPER function will change letters to lowercase if they are not the first letter of a word.
- True
- False
Question 4:
Which formula among the following will change all letters to lowercase?
=PROPER(Z1)=LOWER(G5)=UPPER(K7)
Question 5:
Which formula among the following will change the case of a range of cells?
=LOWER(A1:19)=ARRAYFORMULA(PROPER(A1:19))
Quiz Answers:
Question 1: 3
Question 2: 1
Question 3: 1
Question 4: 2
Question 5: 2
