=UPPER(A2) returns the text in A2 with every letter capitalized. =LOWER(A2) makes every letter lowercase, and =PROPER(A2) capitalizes the first letter of each word and lowercases the rest.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Text | UPPER | LOWER | PROPER |
| 2 | ana silva | ANA SILVA | ana silva | Ana Silva |
| 3 | BEN OKAFOR | BEN OKAFOR | ben okafor | Ben Okafor |
| 4 | cHEN wU | CHEN WU | chen wu | Chen Wu |
Digits, spaces and punctuation pass through unchanged. All three functions work in every Excel version and in Google Sheets.
Excel has no Change Case button like Word's Shift+F3, so a formula is the usual way. To replace the original text with the result, see the last section.
Capitalize the first letter only
There is no SENTENCE function. Build it from pieces: UPPER on the first character, then the rest from MID:
| A | B | C | |
|---|---|---|---|
| 1 | Text | First letter, rest lowercase | First letter, rest unchanged |
| 2 | hELLO wORLD | Hello world | HELLO wORLD |
| 3 | order from NASA | Order from nasa | Order from NASA |
Column B lowercases everything after the first letter, which also turns NASA into nasa. Column C leaves the rest as it was. LEN(A2) is used as "the rest of the text": MID never returns more characters than there are.
| A | B | |
|---|---|---|
| 1 | Comment | Fixed |
| 2 | pAYMENT RECEIVED |
Your turn: In B2, capitalize the first letter of the text in A2 and make the rest lowercase.
PROPER's quirks: McDonald, O'Neil and 3rd
PROPER capitalizes the first letter of the text and every letter that follows a character that is not a letter. That rule is right for most names and wrong for some:
| A | B | |
|---|---|---|
| 1 | Text | PROPER |
| 2 | o'neil | O'Neil |
| 3 | mary-ann | Mary-Ann |
| 4 | mcdonald's | Mcdonald'S |
| 5 | 3rd floor | 3Rd Floor |
| 6 | USA today | Usa Today |
| 7 | mcdonald | Mcdonald |
o'neilandmary-anncome out right, asO'NeilandMary-Ann.mcdonald'sbecomesMcdonald'S: the S after the apostrophe is capitalized.=SUBSTITUTE(PROPER(A2),"'S","'s")fixes that pattern.3rdbecomes3RdandUSAbecomesUsa: PROPER lowercases everything that is not a first letter.mcdonaldbecomesMcdonald, notMcDonald. PROPER cannot know where a name has a second capital; such names need a manual fix or a lookup table of exceptions.
Case-sensitive comparison with EXACT
Excel ignores case when it compares text: ="ANA"="ana" is TRUE, and so are COUNTIF, VLOOKUP and XLOOKUP matches. When case matters, for codes like ab12 and AB12, use EXACT:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Code | Typed | A=B | EXACT |
| 2 | AB12 | ab12 | TRUE | FALSE |
| 3 | AB12 | AB12 | TRUE | TRUE |
| 4 | Xy-7 | XY-7 | TRUE | FALSE |
For a case-sensitive lookup, give XLOOKUP the TRUE/FALSE list that EXACT returns for the whole column: =XLOOKUP(TRUE,EXACT(A2:A10,F2),B2:B10) finds ab12 but not AB12 (Excel 2021 and Microsoft 365).
| A | B | |
|---|---|---|
| 1 | Typed email | Stored email |
| 2 | Ana.Silva@Coddy.TECH |
Your turn: Email addresses should be stored in lowercase. In B2, return the address in A2 in lowercase.
Replace the text with the new case
The formula writes its result in another column. To change the original cells:
- Put
=UPPER(A2)(or LOWER, or PROPER) in an empty column and fill it down. - Copy that column with Ctrl+C (Cmd+C on a Mac).
- Select the original column and use Home > Paste > Paste Values.
- Delete the helper column.
Flash Fill is a shortcut for simple cases: type the first value in the case you want next to the original, press Enter, then press Ctrl+E or use Data > Flash Fill. It copies the pattern to the other rows as fixed text. Check the result on names like McDonald, where the pattern is not obvious.
Frequently Asked Questions
How do I capitalize all letters in Excel?
Use =UPPER(A2) in another column, fill it down, then copy the results and use Home > Paste > Paste Values over the original. Excel has no Change Case button, unlike Word.
How do I capitalize only the first letter in Excel?
=UPPER(LEFT(A2,1))&LOWER(MID(A2,2,LEN(A2))) capitalizes the first letter and lowercases the rest. To leave the rest as it is, drop the LOWER: =UPPER(LEFT(A2,1))&MID(A2,2,LEN(A2)).
Why does PROPER capitalize the letter after an apostrophe?
PROPER capitalizes every letter that follows a character that is not a letter, so mcdonald's becomes Mcdonald'S. Fix the common case with =SUBSTITUTE(PROPER(A2),"'S","'s").
How do I do a case-sensitive lookup in Excel?
Lookups ignore case, so match with EXACT: =XLOOKUP(TRUE,EXACT(A2:A10,F2),B2:B10) returns the row where A matches F2 letter for letter, capitals included (Excel 2021 and Microsoft 365).
