Google Sheets Cheat Sheet

PROPER Google Sheets Function [With Quiz]

In Google Sheets the PROPER function outputs the provided text with each word capitalized.

PROPER Syntax

=PROPER(text_to_capitalize)

The definition of 'word' within the PROPER function is a consecutive series of letters [A-Za-z]. Anything that separates letters (punctuation or numbers) counts as the end of one word and the beginning of another.

For example, "mid-20th century" will become "Mid-20Th Century" because the "-20" makes "mid" the first word and "th" the second.

FREE RESOURCE

Google Sheets Cheat Sheet

Learn 12 tips and tactics that will make your sheets more beautiful and user-friendly from today.

Google Sheets Cheat Sheet

By entering your email address you agree to get email updates from me. I'll respect your privacy and you can unsubscribe at any time.

PROPER Function Example

ABC
1TextFormulaOutput
2MICHAEL SCOTT=PROPER(A2)Michael Scott
3jim halpert=PROPER(A3)Jim Halpert
4DwIgHt ScHrUtE=PROPER(A4)Dwight Schrute
5PAM beesly=PROPER(A5)Pam Beesly
6Andy Bernard=PROPER(A6)Andy Bernard

You can also reference text directly instead of using a cell reference:

=PROPER("MICHAEL SCOTT")

Will output: Michael Scott

Using The PROPER Function On A Whole Column

By combining the PROPER function and the ArrayFormula function you can force PROPER to accept more than one argument.

This means you can capitalize multiple cells, an entire column or row, or even a table with one formula.

Here's an example using the same data as above:

ABC
1TextFormulaOutput
2MICHAEL SCOTT=ArrayFormula(PROPER(A2:A6))Michael Scott
3jim halpertJim Halpert
4DwIgHt ScHrUtEDwight Schrute
5PAM beeslyPam Beesly
6Andy BernardAndy Bernard

All five names are capitalized with only one formula.

PROPER Errors

The PROPER function is very stable.

It handles the following without error:

If you input a range (e.g. A2:A6) without using ArrayFormula it will output one piece of capitalized text if the range crosses the same row or column as the formula's cell.

Otherwise, it will throw this error:

An array value could not be found.

Potential Problems With PROPER

PROPER is great for fixing proper nouns (names of people and places) that have been entered incorrectly.

However, PROPER converts all characters not at the beginning of 'words' to lowercase.

ALL characters.

So names with bicapitalization (like Jerry DiCanio) and names that start with lowercase letters (like Wolf von Weyler) output incorrectly when using the PROPER function:

ABC
1TextFormulaOutput
2Jerry DiCanio=PROPER(A2)Jerry Dicanio
3Wolf von Weyler=PROPER(A3)Wolf Von Weyler

It's also not the best option for paragraphs or blocks of text as it does not capitalize sentences.

If you do need that functionality, I've written about it here.

PROPER Quiz

Here's a quick summary of how to use the quiz spreadsheet:

  1. Select the required cell while the formula bar is empty
  2. Type your answer in the formula bar
  3. You can click on the spreadsheet to add cell/range references

Complete all questions to solidify your understanding of the PROPER function.

PROPER Function Exercises
Enter a formula for each question in the formula bar for the spreadsheet below.
1. One Name
In cell B2, enter a formula that capitalizes the first name from the list using a cell reference. Remember to start your formula with '='.
2. All Names
In cell C2, enter a formula that capitalizes every name using a range reference. Remember, the PROPER function won't do this by itself.
ABC
1DataOneAll
2MICHAEL SCOTT
3jim halpert
4DwIgHt ScHrUtE
5PAM beesly
6Andy Bernard
7
8
Click Here When Complete

FREE RESOURCE

Google Sheets Cheat Sheet

Learn 12 tips and tactics that will make your sheets more beautiful and user-friendly from today.

Google Sheets Cheat Sheet

By entering your email address you agree to get email updates from me. I'll respect your privacy and you can unsubscribe at any time.

Kieran Dixon started using spreadsheets in 2010. He leveled-up his skills working for banks and running his own business. Now he makes Google Sheets and Apps Script more approachable for anyone looking to streamline their business and life.