Excel has no Change Case button. Instead it has three worksheet functions, UPPER, LOWER and PROPER, which you use in a helper column and then paste back as values. PROPER is the one people reach for with titles and names, and it has two quirks worth knowing.
The three case functions
=UPPER(A2)converts the text in A2 to capitals.=LOWER(A2)converts it to lowercase.=PROPER(A2)capitalizes the first letter of the text and any letter that follows a non-letter character, and lowercases everything else.
Change case step by step
- Insert a temporary column next to the column you want to convert.
- In the first cell of the new column, type
=PROPER(A2)(orUPPERorLOWER) and press Enter. - Fill the formula down: select the cell and double-click the small square at its bottom-right corner. In an Excel table, the column fills automatically.
- With the new values selected, press Ctrl+C, right-click the first cell of the original column, choose Paste, then Values.
- Delete the temporary column.
Two PROPER quirks
Because PROPER capitalizes any letter that follows a non-letter, an apostrophe counts too: don't becomes Don'T and O'Brien's becomes O'Brien'S. And like Word and Google Docs, it capitalizes every word, including the, of and and, so it doesn't produce style-guide title case. It also turns acronyms into ordinary words (USA becomes Usa).
A formula for real title case
This formula starts from PROPER, lowercases common small words in the middle of the title, fixes the apostrophe problem, and keeps an article capitalized after a colon. It uses only PROPER and SUBSTITUTE, so it works in any version of Excel. Change A2 to your first cell:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(PROPER(A2)," A "," a ")," An "," an ")," The "," the ")," And "," and ")," But "," but ")," Or "," or ")," For "," for ")," Nor "," nor ")," At "," at ")," By "," by ")," In "," in ")," Of "," of ")," On "," on ")," To "," to ")," Up "," up ")," As "," as "),"'S","'s"),"'T","'t"),": a ",": A "),": an ",": An "),": the ",": The ")
The small words are matched with a space on each side, so the first and last words of the title are never touched and stay capitalized. The result follows the AP and APA pattern, where four-letter prepositions such as with and from are capitalized:
| In A2 | =PROPER(A2) | Title-case formula |
|---|---|---|
the lord of the rings | The Lord Of The Rings | The Lord of the Rings |
war of the worlds: a novel | War Of The Worlds: A Novel | War of the Worlds: A Novel |
don't stop believing | Don'T Stop Believing | Don't Stop Believing |
harry potter and the order of the phoenix | Harry Potter And The Order Of The Phoenix | Harry Potter and the Order of the Phoenix |
It can't fix acronyms or brand names (Usa, Iphone), and it doesn't know when a word like up is part of a verb. For a column of titles, our bulk converter takes a pasted column or a CSV file, applies a chosen style guide, and keeps acronyms and common brand names intact.
Using Google Sheets instead? It has the same UPPER, LOWER and PROPER functions; see how to change case in Google Sheets.
Sources
- Change the case of text, Microsoft Support
- Top ten ways to clean your data, Microsoft Support (PROPER definition)
Questions
Is there a Change Case button in Excel?
No. Excel uses the UPPER, LOWER and PROPER functions instead. Put the formula in a helper column, then paste the results back as values.
Why does PROPER turn don't into Don'T?
PROPER capitalizes any letter that follows a non-letter character, and an apostrophe counts. Wrap the result in SUBSTITUTE to change 'T back to 't, as the title-case formula on this page does.
Can Excel do title case?
Not with a single built-in function. PROPER capitalizes every word. The SUBSTITUTE formula on this page lowercases the common small words to get AP-style title case.