If you ever need to quickly capitalize the first letter of each word in a Google spreadsheet, there is a very simple function that can be used to accomplish this. The PROPER function will automatically change the case of text so that only the first letter of each word is capitalized. This is also sometimes referred to as "Title Case".
For example, if you apply the PROPER function to the text "book title", then the formula will display "Book Title".
To capitalize the first letter of each word in Google Sheets, do the following:
- Type "=PROPER(" into a spreadsheet cell or click “Insert” → “Function” → “Text” → “PROPER”
- Type a cell reference to designate the cell that contains the words to be capitalized
- Press "Enter" on the keyboard, and now only the first letter of each word will be capitalized
How to extend the PROPER formula down the column:
1) Copy the formula down by dragging the fill handle, or
2) Use the ARRAYFORMULA function to apply a single formula to a column
Click here to get your Google Sheets cheat sheet
Or click here to take the dashboards course
PROPER formula examples:
After you have completed the steps above, your formula should look like the formula below.
Capitalize words
- =PROPER(A2)
Capitalize words in a column
- =ARRAYFORMULA(PROPER(A2:A))
In this article I will focus only on the PROPER function, but there are two other "change case" formulas that you may want to learn as well. Read this article about changing text case in Google Sheets, to learn about the LOWER function (which makes text lowercase), and UPPER function (which makes every letter uppercase).
The Google Sheets PROPER function:
The image below shows how the PROPER function works. Setting up the PROPER function is very easy, since the only thing that you need to specify, is a cell reference.
When taking a look at the description of the PROPER function, there is one particular feature that can be easy to miss.
It's good to note that the PROPER function not only capitalizes the first letter of every word, but it also makes any non-first letter of a word, lowercase. This is why when I describe the PROPER function, I like to state that the function will modify a string of text so that only the first letter of each word is capitalized.
There are some words that should always have their first letter capitalized, such as names of people, titles of books, and places like cities and states. All of these types of words can be quickly capitalized by using the PROPER function.
Google Sheets description for the PROPER function:
Syntax:
PROPER(text_to_capitalize)Formula summary: “Capitalizes each word in a specified string”
Below are a few examples that show how to use the PROPER function in a variety of situations.
We will begin with a basic example that capitalizes the words in a single cell, and then I'll show you how to capitalize the words within a range of cells, or an entire column.
Capitalize the first letter of every word in Google Sheets
Let's begin by going over an example of capitalizing every word in a single Google spreadsheet cell.
In this first example, we will capitalize the state/word "New Hampshire".
The names of states are words that should always be capitalized, and so if you ever have one or more states listed in a spreadsheet that are not properly capitalized, then using the PROPER function is the perfect way to automatically capitalize them.
Notice that in the example image below, the text that is in cell A1 is typed like this, "NEW hampshire", where there are some uppercase letters that should be lowercase, and there are some lowercase letters that should be uppercase.
So we going to capitalize the first letter of each word, as well as make all letters that are not the first letter of a word, lowercase. Your PROPER formula will take care of both of these things all at once!
The task: Correct the spelling for the word/state "NEW hampshire"
The logic: Use the PROPER function to capitalize the first letter of every word in cell A1, and to make every other letter in that cell lowercase
The formula: The formula below, is entered in the blue cell (B1), for this example
=PROPER(A1)
In the example above, you can see that ONLY the first letter of each word have been capitalized, and that any other letter has been made lowercase.
Make the first letter of words uppercase in multiple cells
If you have one cell that you want to capitalize in your Google spreadsheet, chances are that you will want to capitalize the text in multiple cells.
One way to do this is by copying your formula into a range of cells, or even an entire column.
In this example, we will capitalize the words in a list of hand tools, to make the list look clean and professional.
We will do this by copying the PROPER formula that is initially entered at the top of the sheet, into the cells below it, so that each name in the source data has a formula that refers to it. Stated simply, the word(s) in each cell will be capitalized by their own individual formula.
In column A of the example below, is a list of tools that have been typed in lowercase letters. Column C contains the formulas, where you can see the capitalized list of words that the PROPER formulas generated.
The task: Capitalize every word in the list of tools
The logic: Capitalize the words that are in the range A2:A15, by using the PROPER function. Copy the formula into the entire range.
The formula: The formula below, is initially entered into cell C2, and then copied into the other cells in the range/column
=PROPER(A2)
Capitalize every word in a column in Google Sheets
One of the most common uses of the Google Sheets PROPER function, is to capitalize the first letter of first and last names in a spreadsheet.
In this example we will apply the PROPER formula to an entire column by using the ARRAYFORMULA function, so that every name will be capitalized while using only one formula.
To capitalize an entire column of words in Google Sheets, follow the steps below:
- First, type a basic PROPER formula, that refers to a single cell, like this =PROPER(A2)
- Then modify your cell reference so that it refers to a column, and combine your formula with the ARRAYFORMULA function, like this =ARRAYFORMULA(PROPER(A2:A))
When names are being manually typed into a spreadsheet, especially by anyone who is in a rush, the names are often typed with a variety of uppercase and lowercase combinations.
Notice that in the example image below, many of the names in column A contain uppercase letters other than the letters at the beginning of each word. Some of the names are typed in all caps. You'll be able to see that the PROPER function will actually make these letters lowercase (the letters that are NOT the first letter).
With thanks to the ARRAYFORMULA function, we will be able to extend the functionality of the PROPER formula through the entire column with one formula that is entered into a single cell.
Follow the example to see exactly how to do this.
The task: Change the first letter of every name to uppercase, and change all other letters to lowercase
The logic: Use ARRAYFORMULA with PROPER to change the case of the text in column A so that only the first letter of each word is capitalized
The formula: The formula below, is entered in the blue cell (C2), for this example
=ARRAYFORMULA(PROPER(A2:A))
Pop Quiz: Test your knowledge
Answer the questions below about the PROPER function, to refine your knowledge! Scroll to the very bottom to find the answers to the quiz.
Classroom downloads:
PROPER function 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 formula is a valid formula that will capitalize the first letter of each word?
- =UPPER(O7)
- =PROPERCASE(L9)
- =PROPER(Z3)
Question #2
Which of the options below represents what the following text would look like after the PROPER function was applied to it? "formulas are AMAZING!"
- FORMULAS ARE AMAZING!
- formulas are amazing!
- Formulas Are Amazing!
Question #3
True or False: The PROPER function will make any letter other than the first letter of a word, lowercase
- True
- False
Answers to the questions above:
Question 1: 3
Question 2: 3
Question 3: 1
