To capitalize the first letter of each word in Excel, use the PROPER function. In an empty column, type =PROPER(A2), press Enter, and copy it down. jane smith and JANE SMITH both become Jane Smith. Then copy the results and paste them as values to replace the original text. Excel has no Change Case button like Word, but Flash Fill (Ctrl + E) and Power Query can do the same job without formulas. Here’s each method, plus how to fix the names PROPER gets wrong.
Method 1: The PROPER function
- Insert an empty column next to your text, for example column B.
- In B2, type
=PROPER(A2)and press Enter. - Double-click the fill handle to copy the formula down to the end of the data.
In Microsoft 365, =PROPER(A2:A500) converts the whole list in one formula. The results spill down automatically.
Replace the original text
- Select the formula results and press Ctrl + C.
- Right-click the first cell of the original column (A2) and choose Paste Values (the clipboard with 123).
- Delete the helper column.
Method 2: Flash Fill (no formula)
- In the column next to your data, type the first entry the way you want it, for example Jane Smith.
- Click the cell below and press Ctrl + E.
Excel fills the rest by following your example. Check the results, especially for unusual names, because Flash Fill copies the pattern it guesses.
Method 3: Power Query (large or repeating lists)
- Select the data and go to Data > From Table/Range.
- Right-click the column header and choose Transform > Capitalize Each Word.
- Click Close & Load.
If you import the same kind of list regularly, click Refresh next time and the capitalization is reapplied. See how to find and enable Power Query.
Other capitalization formulas
| You want | Formula | “jANE o’NEIL” becomes |
|---|---|---|
| Each word capitalized | =PROPER(A2) |
Jane O’Neil |
| ALL CAPS | =UPPER(A2) |
JANE O’NEIL |
| all lowercase | =LOWER(A2) |
jane o’neil |
| Sentence case (first letter only) | =UPPER(LEFT(A2,1))&LOWER(MID(A2,2,LEN(A2))) |
Jane o’neil |
For more on uppercase, see how to make capital letters in Excel.
Where PROPER gets it wrong
PROPER capitalizes the letter after every space, apostrophe, hyphen or number. That’s usually right, but not always:
- McDonald becomes Mcdonald.
- iPhone becomes Iphone.
- 2nd Street becomes 2Nd Street.
- van der Berg becomes Van Der Berg.
Fix these afterwards with Find and Replace (Ctrl + H). For example, replace 2Nd with 2nd, and tick Match case. For a long list, filter for common patterns, such as names starting with “Mc”, and fix them by hand.
Clean up spacing at the same time
Lists copied from other systems often have extra spaces. Combine TRIM with PROPER: =PROPER(TRIM(A2)). For other clean-up tasks, see how to remove characters from a cell in Excel.
A trick using Word
Word has a Change Case shortcut. Copy the column into Word, select it, and press Shift + F3 until the text is in the case you want. Then copy it back into Excel. It’s handy for a one-off list, but the formula methods are better for anything you’ll repeat.
Troubleshooting
The formula shows as text
The cell is formatted as Text. Change it to General and re-enter the formula.
Deleting the original column breaks the results
Paste the results as values first, as described in Method 1.
Frequently asked questions
Can I capitalize text in place without a helper column?
Not with a formula, because a cell can’t hold both the text and a formula that changes it. Use Flash Fill in a new column, Power Query, or a short macro if you need to do it in place often.
Does PROPER work on Mac and Excel for the web?
Yes. PROPER, UPPER and LOWER work the same in every version of Excel, and in Google Sheets.
How do I capitalize only the first letter of a cell?
Use the sentence-case formula in the table above. It capitalizes the first character and makes everything else lowercase.
Related guides
Summary
- Type
=PROPER(A2)and copy it down. - Paste the results as values over the original text.
- Or type one example and press Ctrl + E.
- Check names like McDonald and words with numbers afterwards.
Matt Jacobs has been working as an IT consultant for small businesses since receiving his Master’s degree in 2003. While he still does some consulting work, his primary focus now is on creating technology support content for SupportYourTech.com.
His work can be found on many websites and focuses on topics such as Microsoft Office, Apple devices, Android devices, Photoshop, and more.