Have you ever wanted to quickly change the case of text in your Google spreadsheet, from lowercase to uppercase or vice versa? Google Sheets has three different formulas that can be used to automatically change the case of text that is entered into spreadsheet cells. The UPPER function will capitalize ALL letters, the LOWER function will make all letters lowercase, and the PROPER function will capitalize the first letter of each word.
To change the case of text in Google Sheets, follow these steps:
- Type one of the following into your spreadsheet cell: "=UPPER(" to capitalize all letters, "=LOWER(" to make text lowercase, or "=PROPER(" to capitalize the first letter of words
- Type a cell reference to designate the cell that contains the text that you want to change the case of
- Press "Enter" on the keyboard, and the case of your text will now have changed as desired
You can also insert functions to change case in Google Sheets by clicking “Insert” → “Function” → “Text” and the clicking "UPPER", "LOWER", or "PROPER".
(Optional formula extension): 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 .
Formulas to change text case in Google Sheets:
Change every letter to uppercase with UPPER
- =UPPER(A1)
Change text to lowercase with LOWER
- =LOWER(A1)
Capitalize every word with PROPER
- =PROPER(A1)
Click here to get your Google Sheets cheat sheet
Or click here to take the dashboards course
Here are three basic examples that show how the UPPER, LOWER, and PROPER formulas affect text, when applied to a single cell that contains the following sentence:
"EXAMPLE: Observe how the letters in this sentence change when applying varying formulas that "change case" in Google Sheets.
Notice that the sentence above contains a variety of words. Some of the words are in all lowercase letters, some of the words are in all capital letters, and some of the words have their first letter capitalized. This will allow you to see how the change case formulas affect a variety of different letters and words, while simply referring to a single cell.
Change all the letters of a sentence to uppercase
Formula: =UPPER(A1)
Change all the letters of a sentence to lowercase
Formula: =LOWER(A1)
Change the first letter of every word to uppercase
Formula: =PROPER(A1)
Notice that in the examples above, every single letter is capitalized when the UPPER function is used… every single 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.
Below, are examples that go into greater detail on using the change case formulas in Google Sheets.
Each of the examples shows an entire list of names that are modified by the change case formulas, where the formula is initially entered into a cell at the top of the list… and then copied into the cells below it so that every name in the column is capitalized or changed to lowercase.
Then, in the last example, I'll show you how to change the case of an entire column with a single formula, by using the ARRAYFORMULA function.
Capitalize text in Google Sheets with the UPPER function
The Google Sheets UPPER function will capitalize every letter that is in the cell that the function refers to.
In this example we will use the UPPER function to capitalize all of the letters in a list of names.
In the example image below, you'll see that the original unmodified list of names is shown in the normal format that you'd expect a name to be displayed in (with only the first letter of each word/name being capitalized).
But let's say that for whatever reason, your task/job requires you to have names in all capital letters. For example, you might need to display the names in this way for a financial record, or perhaps you are using case sensitive formulas to lookup/match information.
By using the UPPER function in a Google spreadsheet you can automatically change every letter into the capitalized format that you require.
The task: Capitalize every letter in a list of names
The logic: Use the UPPER function to capitalize the letters in the text that is found in the range A2:A15
The formula: The formula below, is initially entered into cell C2, and then copied into the cells below it for this example
=UPPER(A2)
Notice that in the example above, the letters that were already capitalized have remained that way, and the letters that were originally lowercase, have now been capitalized.
Change text to lowercase in Google Sheets with the LOWER function
Now let's change every letter in a list of names to lowercase letters. We will do this by using the LOWER function.
The Google Sheets LOWER function will change every letter that is found within the cell that it refers to, to lowercase.
In this example, let's say that we have a list of names which were manually typed and submitted by staff members… that we want to clean up and convert into all lowercase letters.
In the example image below, you can see that the original list of names is messy, with names having a variety of different lowercase and uppercase arrangements. Some of the names are in all caps, and some of them have a mix of both uppercase and lowercase letters.
The task: Change the letters in a list of names to all lowercase letters
The logic: Use the LOWER function to make all of the letters in the range A2:A15, lowercase
The formula: The formula below, is initially entered into cell C2, and then copied into the cells below it for this example
=LOWER(A2)
Notice that in the example above, the letters that were originally lowercase have remained that way, and the letters that were originally uppercase have now been changed to lowercase.
Capitalize every word in Google Sheets with the PROPER function
If you only want to capitalize the first letter of every word in Google Sheets, you can use the PROPER function to accomplish this.
The Google Sheets PROPER function will change the case of the text in the cell that it refers to, so that only the first letter of each word is capitalized.
This means that it will not only capitalize the first letter, but will also make any letter that is not the first letter of a word, lowercase. This is also sometimes referred to as, "Title Case".
In this example, we will use the PROPER function to capitalize the first letter of every name (both first and last), that is listed in a column.
In the example image below, the original list of names that we are going to change the case of, is even messier than the list of names in the last example. Some of the names shift from lowercase to uppercase in every letter, which makes the list look very unprofessional and in many cases can make the data hard to reliably use when needing to match criteria in case-sensitive formulas.
Let's say that your boss gave you the list of names shown below, and asked you to change them into 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 this instantly by using the PROPER function.
The task: Capitalize only the first letter of each name
The logic: Capitalize the first letter of each name that is in the range A2:A15
The formula: The formula below, is initially entered into cell C2, and then copied into the cells below it for this example
=PROPER(A2)
Notice that in the example above, only the first letter of every word has been capitalized, and that any letters that were originally uppercase but not the first letter of a word, have now been changed to lowercase.
How to apply case changing formulas to a column
In the examples above, we were able to change the case of multiple names in a column by copying the original formula into the cells below it, so that one formula was applied to each cell.
However, you may want to change the text in an entire column to uppercase or lowercase by using a single formula. This can be done by combining your text case formula with the ARRAYFORMULA function.
To change the case of text in an entire column in Google Sheets, choose your function (UPPER, LOWER, PROPER), wrap your formula with the ARRAYFORMULA function, and then refer to a range of cells in your formula instead of a single cell.
In this example I will use the UPPER function to demonstrate how this is done, by using the same list of names from the first example. However, you'll see that instead of using multiple formulas to change every name to all capital letters, we will accomplish the same task by using a single formula.
The task: Change the names in the column to all capital letters
The logic: Use the UPPER function with the ARRAYFORMULA function the make every letter in column A, uppercase
The formula: The formula below, is entered in the blue cell (C2), for this example
=arrayformula(UPPER(A2:A))
Pop Quiz: Test your knowledge
Answer the questions below on changing text case, to refine your knowledge! Scroll to the very bottom to find the answers to this quiz.
Classroom downloads:
"Change text case" cheat sheet (PDF)
Click here to get your Google Sheets cheat sheet
Or click here to take the dashboards course
Click the green "Print" button below to print this entire article.
Question #1
Which of the following formulas will capitalize ALL letters?
- =PROPER(A9)
- =LOWER(B11)
- =UPPER(C12)
Question #2
Which of the following formulas will capitalize only the first letter of each word?
- =PROPER(J1)
- =LOWER(J2)
- =UPPER(J3)
Question #3
True or False: The PROPER function will make letters lowercase if they are not the first letter of a word?
- True
- False
Question #4
Which of the following formulas will make all letters lowercase?
- =PROPER(Z1)
- =LOWER(G5)
- =UPPER(K7)
Question #5
Which of the following formulas will change the case of a range of cells?
- =LOWER(A1:19)
- =ARRAYFORMULA(PROPER(A1:19))
Answers to the questions above:
Question 1: 3
Question 2: 1
Question 3: 1
Question 4: 2
Question 5: 2
