How to Change Text to Uppercase, Lowercase or Proper Case in Excel
In Word you can press Shift + F3 to change the case of any text. Excel doesn't have that shortcut or a Change Case button. Instead, it has three simple functions that fix capital letters for a whole column at once.
The three functions
| Function | What it does | Example result |
|---|---|---|
| =UPPER(A2) | ALL CAPITALS | JOHN SMITH |
| =LOWER(A2) | all lower case | john smith |
| =PROPER(A2) | Capital First Letter Of Each Word | John Smith |
Step by step
Say your names in column A are a mess: jOHN smith, SARAH JONES, peter brown.
1. Insert a new, empty column next to your data (right-click column B and choose Insert).
2. In B2, type =PROPER(A2) and press Enter.
3. Double-click the fill handle (the small square at the bottom-right of B2) to copy the formula down the whole column.
2. In B2, type =PROPER(A2) and press Enter.
3. Double-click the fill handle (the small square at the bottom-right of B2) to copy the formula down the whole column.
Every name now appears as John Smith, Sarah Jones, Peter Brown.
Replace the original text
At the moment, column B depends on column A. If you delete A, you'll get errors. To keep only the corrected text:
1. Select the results in column B and press Ctrl + C.
2. Click A2, right-click and choose Paste Options > Values (the clipboard with 123).
3. Delete column B.
2. Click A2, right-click and choose Paste Options > Values (the clipboard with 123).
3. Delete column B.
The shortcut: Flash Fill
You can skip formulas entirely:
1. In the column next to your data, type the first entry exactly how you want it, e.g. John Smith.
2. Press Enter, then Ctrl + E.
2. Press Enter, then Ctrl + E.
Excel copies the pattern down the column. Check the results carefully, because Flash Fill sometimes guesses wrong with unusual names.
Watch out for PROPER's quirks
PROPER capitalises the first letter after any non-letter, and lower-cases everything else. That causes a few problems:
• McDonald becomes Mcdonald
• O'BRIEN becomes O'Brien (good), but it's becomes It'S
• UK or NHS in the middle of text becomes Uk and Nhs
• O'BRIEN becomes O'Brien (good), but it's becomes It'S
• UK or NHS in the middle of text becomes Uk and Nhs
After using PROPER on names or addresses, scan the results and fix these by hand.
Capitalise just the first letter of a sentence
Excel doesn't have a "Sentence case" function, but you can build one:
=UPPER(LEFT(A2,1))&LOWER(MID(A2,2,LEN(A2)))
This makes the first character upper case and the rest lower case, turning THIS IS A NOTE into This is a note.
Using Excel for Mac or the web?
The same functions work in every version of Excel, including Excel for Mac, Excel for the web and Excel on iPad.
Troubleshooting
The formula appears as text instead of the result. The column is formatted as Text. Select it, change the format to General on the Home tab, then re-enter the formula.
I pasted and got the formula instead of the text. Use Paste Values, not a normal paste.
Numbers disappeared. They didn't. The case functions leave numbers unchanged, but they return them as text. Use Convert to Number if you need to calculate with them.
Wrapping up
UPPER, LOWER and PROPER fix case in a helper column, then Paste Values replaces the originals. For a quick job, type one example and press Ctrl + E.
