Change Case in Excel: UPPER, LOWER and PROPER
· 9 min read
To change case in Excel, use a formula. Excel has no Change Case button the way Word does (Shift+F3 in Word cycles through the cases, but in Excel it opens the Insert Function dialog). Instead there are three functions:
=LOWER(A2)
=UPPER(A2)
=PROPER(A2)With "HARBOR & pine Hotels" in A2, LOWER returns harbor & pine hotels (all small letters), UPPER returns HARBOR & PINE HOTELS (all capitals) and PROPER returns Harbor & Pine Hotels (a capital at the start of every word). Each one returns a new value in another cell. To replace the original text, write the formula in a helper column, then copy it and paste it back as values. The steps are below.
UPPER, LOWER and PROPER on realistic data
These contact names came from a Brightline Supply Co. CRM import, typed by different people in different styles:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Name (raw) | LOWER | UPPER | PROPER |
| 2 | CHRIS PATEL | chris patel | CHRIS PATEL | Chris Patel |
| 3 | peter hart | peter hart | PETER HART | Peter Hart |
| 4 | maria HART | maria hart | MARIA HART | Maria Hart |
| 5 | Kevin garcia | kevin garcia | KEVIN GARCIA | Kevin Garcia |
| 6 | olivia JENSEN | olivia jensen | OLIVIA JENSEN | Olivia Jensen |
| 7 | harbor & pine hotels | harbor & pine hotels | HARBOR & PINE HOTELS | Harbor & Pine Hotels |
All three take one argument, the text or the cell that holds it, and change only letters. Spaces, digits and punctuation stay as they are.
- LOWER turns "CHRIS PATEL" into chris patel. Use it for email addresses, user names and anything that another system will compare exactly.
- UPPER turns "peter hart" into PETER HART. Use it for codes, region tags and postcodes.
- PROPER turns "maria HART" into Maria Hart and "harbor & pine hotels" into Harbor & Pine Hotels. Use it for names and places, then check the exceptions described below.
How to convert uppercase to lowercase in Excel, step by step
Because the result lives in a new cell, replacing the original column takes four steps. Here is the "upper case to lower case" Excel workflow; it is the same for UPPER and PROPER.
- Insert a helper column next to the text. Right-click the column letter to the right of your data and choose Insert.
- Write the formula in the first row of the helper column, for example
=LOWER(A2). - Fill it down. Double-click the small square at the bottom-right of the cell, and Excel copies the formula down as far as the column next to it has data.
- Replace the original. Select the helper column, copy it (Ctrl+C), select the first cell of the original column, and paste as values: Home > Paste > Paste Values, or Ctrl+Alt+V, then V and Enter on Windows. Then delete the helper column.
Step 4 matters. If you delete the original column while the helper still holds formulas, every formula loses its source and shows #REF!. Pasting as values turns the formulas into plain text first, so the original is no longer needed.
If the text is going to be used only by other formulas, you can skip the paste and keep the helper column. A lookup or a label can point at the cleaned column directly.
Without a formula: Flash Fill and Power Query
Two built-in tools change case without a helper formula:
- Flash Fill (Data > Flash Fill, or Ctrl+E). Type the first value the way you want it in the next column, such as "Chris Patel", then press Ctrl+E. Excel follows the pattern down the column. Check the results, because Flash Fill guesses from your example and does not update when the source changes.
- Power Query. In the Power Query editor, select the column and choose Transform > Format, then lowercase, UPPERCASE or Capitalize Each Word. This suits a file you import again every week, because the step reruns on refresh.
The PROPER function in Excel and its quirks
PROPER's rule is simple: capitalise the first letter of the text and every letter that follows something that is not a letter, and make every other letter small. Spaces, hyphens, apostrophes and digits all count as "not a letter". That rule gets most names right and a predictable few wrong:
| A | B | C | |
|---|---|---|---|
| 1 | Raw | PROPER | What you want |
| 2 | O'NEIL | O'Neil | O'Neil |
| 3 | JEAN-LUC MOREAU | Jean-Luc Moreau | Jean-Luc Moreau |
| 4 | MCDONALD | Mcdonald | McDonald |
| 5 | ANNA VAN DER BERG | Anna Van Der Berg | Anna van der Berg |
| 6 | 2ND FLOOR, UNIT 4 | 2Nd Floor, Unit 4 | 2nd Floor, Unit 4 |
| 7 | it's on the dock | It'S On The Dock | It's On The Dock |
| 8 | RIVER PRINT CO LLC | River Print Co Llc | River Print Co LLC |
| 9 | pp-101-bx | Pp-101-Bx | PP-101-BX |
What PROPER returns:
- O'Neil and Jean-Luc Moreau: correct, because a capital after an apostrophe or hyphen is what these names need.
- Mcdonald: PROPER has no idea that the D is a capital. The same happens to MacLeod and DeSouza.
- Anna Van Der Berg: particles such as van, de and von get a capital they may not want.
- 2Nd Floor, Unit 4: the "n" follows a digit, so PROPER treats it as the start of a word. Ordinals such as 1st and 3rd break the same way.
- It'S On The Dock: the apostrophe in a contraction starts a new "word", so the S becomes a capital.
- River Print Co Llc: acronyms such as LLC and USA are lowered.
- Pp-101-Bx: product codes should never go through PROPER. Use UPPER for codes.
Fixing the exceptions
For a short list of known exceptions, wrap PROPER in SUBSTITUTE. SUBSTITUTE is case sensitive, so it changes only the exact spelling PROPER produced:
=SUBSTITUTE(PROPER(A8), " Llc", " LLC")
=SUBSTITUTE(PROPER(A6), "2Nd", "2nd")The first returns River Print Co LLC and the second 2nd Floor, Unit 4. Including the space in " Llc" stops the fix from touching a word that merely contains those letters.
For names, there is no formula that knows every family name. A practical approach for a customer list is to run PROPER, then filter the result for "Mc", "Mac", "'S " and particles such as " Van " and fix those few rows by hand.
Capitalise only the first letter (sentence case)
PROPER capitalises every word. For a note or a product description you usually want only the first letter in capitals, and Excel has no SENTENCE function. Build it from three pieces: the first character in capitals, the rest in small letters:
=UPPER(LEFT(A2,1)) & LOWER(MID(A2,2,LEN(A2)))| A | B | C | |
|---|---|---|---|
| 1 | Note (raw) | Sentence case | PROPER |
| 2 | DELIVER TO REAR ENTRANCE | Deliver to rear entrance | Deliver To Rear Entrance |
| 3 | call before noon | Call before noon | Call Before Noon |
| 4 | pALLET ONLY, NO BOXES | Pallet only, no boxes | Pallet Only, No Boxes |
| 5 | Net-30 TERMS AGREED | Net-30 terms agreed | Net-30 Terms Agreed |
Row 2 becomes Deliver to rear entrance, against PROPER's "Deliver To Rear Entrance". MID(A2,2,LEN(A2)) takes everything from the second character to the end; asking for LEN(A2) characters is simply more than enough. Our guide to extracting text from a cell explains LEFT and MID in detail.
UPPER and LOWER inside other formulas
Changing case is often a step inside a bigger formula rather than the goal:
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | First | Last | Region | Region tag | Is the last name all capitals? | |
| 2 | Chloe | MARTIN | west | WES | chloe.martin@brightline.example | TRUE |
| 3 | Daniel | Ortiz | North | NOR | daniel.ortiz@brightline.example | FALSE |
| 4 | PRIYA | raman | EAST | EAS | priya.raman@brightline.example | FALSE |
- Region tag:
=UPPER(LEFT(C2,3))returns WES, NOR and EAS, whatever case the region was typed in. - Email:
=LOWER(A2&"."&B2)&"@brightline.example"joins the names first and lowers the whole result: chloe.martin@brightline.example. Our concatenate guide covers joining text. - All capitals check:
=EXACT(B2,UPPER(B2))is TRUE when a value is already in capitals. EXACT is case sensitive, unlike=, so it is how you find the rows that still need fixing.
When case matters, and when it does not
Most of Excel ignores case. ="West"="west" is TRUE, and COUNTIF, SUMIF, VLOOKUP, XLOOKUP and MATCH all treat "West" and "WEST" as the same value. So changing case will not fix a lookup that returns #N/A; extra spaces are a far more common cause, and our guide to removing spaces covers them.
Case does matter in EXACT, FIND and SUBSTITUTE, and in whatever system receives your data next. Logins, email lists and IDs exported to a database or another tool are often compared exactly there, so lower-case them before they leave Excel.
Common mistakes
Deleting the original before pasting values. The helper formulas still point at it and turn into #REF!. Paste as values first, then delete.
Running PROPER on codes and emails. "pp-101-bx" becomes "Pp-101-Bx" and an email becomes "Chris.Patel@Granitedental.Com". Apply PROPER to names and places only, UPPER to codes and LOWER to emails.
Hard-coding the result. Asked to "capitalise these names", an AI assistant may hand back a formula such as ="Chris Patel" for the first row, or a list of typed names. It looks right and stops working as soon as the data changes. The formula should reference the cell: =PROPER(A2). Check a filled-down formula on more than one row.
Expecting numbers to stay numbers. UPPER, LOWER and PROPER always return text, so =UPPER(500) gives the text "500". Apply them to text columns only.
Changing case to fix a lookup. Lookups already ignore case. If a match fails, check for leading or trailing spaces with LEN before you change anything else.
FAQ
Is there a shortcut to change case in Excel?
No. Excel has no Change Case button or shortcut like Word's Shift+F3. Use =LOWER(), =UPPER() or =PROPER() in a helper column, or type one example and press Ctrl+E for Flash Fill.
How do I change upper case to lower case in Excel?
Write =LOWER(A2) in an empty column and fill it down. To replace the original text, copy the new column and paste it over the original as values, then delete the helper column.
What does the PROPER function do in Excel?
PROPER capitalises the first letter of each word and makes the other letters small. It treats any character that is not a letter, such as a space, hyphen, apostrophe or digit, as the start of a new word.
Why does PROPER capitalise the letter after an apostrophe?
Because PROPER starts a new word after every non-letter. That helps for O'Neil but turns "it's" into "It'S". Fix the few cases with SUBSTITUTE or by hand.
How do I capitalise only the first letter of a cell?
Use =UPPER(LEFT(A2,1))&LOWER(MID(A2,2,LEN(A2))). It makes the first character a capital and the rest small letters.
Does changing case affect VLOOKUP or COUNTIF?
No. Lookups, COUNTIF and = comparisons ignore case. Use EXACT when you need a case-sensitive comparison.
Practise this on a realistic dataset
Solve it yourself on one company's data, with instant feedback that points at what's wrong without giving away the answer.
Try “Fix shouting names in the CRM” →