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

  1. Insert a temporary column next to the column you want to convert.
  2. In the first cell of the new column, type =PROPER(A2) (or UPPER or LOWER) and press Enter.
  3. 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.
  4. With the new values selected, press Ctrl+C, right-click the first cell of the original column, choose Paste, then Values.
  5. 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 ringsThe Lord Of The RingsThe Lord of the Rings
war of the worlds: a novelWar Of The Worlds: A NovelWar of the Worlds: A Novel
don't stop believingDon'T Stop BelievingDon't Stop Believing
harry potter and the order of the phoenixHarry Potter And The Order Of The PhoenixHarry 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

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.