Excel UPPER, LOWER and PROPER: Fix Inconsistent Capitalisation
Convert text to capitals, to lower case, or to Title Case.
Three small functions for one problem: data where the same value arrives capitalised three different ways. UPPER makes everything capitals, LOWER makes everything lower case, and PROPER capitalises the first letter of each word and lowers the rest.
PROPER is the one people reach for on names, and it is the one that will embarrass you. It capitalises the first letter of every word by its own rules, so "chen wei" becomes "Chen Wei" correctly, but "o'brien" becomes "O'Brien" — which happens to be right — while "macdonald" becomes "Macdonald" and "IBM" becomes "Ibm". It has no idea what a name is.
One thing they do not fix, despite appearances: Excel's own comparisons ignore case anyway. A VLOOKUP for "alice" already finds "ALICE". If a lookup is failing, the cause is spaces or data types, not capitalisation, and these functions will not help.
Syntax
=UPPER(text) =LOWER(text) =PROPER(text)Arguments
textRequired- The value to convert. Numbers and punctuation pass through untouched; only letters change.
The example data
Headers in row 1, data in A2:C5. Note the stray spaces in A3 and A5.
| A | B | C | |
|---|---|---|---|
| 1 | Name | Code | |
| 2 | Alice Moreau | alice@northwind.com | NW-2024-001 |
| 3 | bruno santos | bruno@southgate.co.uk | SG-2024-014 |
| 4 | CHEN WEI | chen@northwind.com | NW-2023-207 |
| 5 | Dana Okafor | dana@eastvale.org | EV-2024-092 |
Worked examples
=UPPER(A2)Result: ALICE MOREAU
Everything to capitals. Useful for reference codes, harsh for names.
=LOWER(A4)Result: chen wei
The all-caps entry brought down, ready for PROPER to re-capitalise properly.
=PROPER(A4)Result: Chen Wei
Title Case in one step. PROPER lowers the rest of each word first, so it fixes all-caps input.
=PROPER(TRIM(A3))Result: Bruno Santos
The combination worth memorising: clean the spaces, then fix the capitalisation.
Now practise it
Reading about a formula is not the same as writing one. Open this function's exercise and type it into a real grid — you get instant feedback on exactly which cell is wrong and why.
Common errors and how to fix them
PROPER breaks acronyms
Why it happens: It has no dictionary. "IBM" becomes "Ibm" and "PhD" becomes "Phd", because every word gets the same treatment.
How to fix it: Fix them back with SUBSTITUTE afterwards, or exclude those rows. There is no way to make PROPER understand exceptions.
PROPER capitalises after digits and punctuation
Why it happens: Any non-letter counts as a word boundary, so "3rd" becomes "3Rd" and "alice-may" becomes "Alice-May".
How to fix it: The hyphen case is usually correct for names. For "3rd", repair it with SUBSTITUTE.
Numbers become text
Why it happens: All three return text, so applying them to a numeric column changes its type.
How to fix it: Only apply them to text columns, or wrap in VALUE afterwards.
Tips worth knowing
- TRIM first, then PROPER — cleaning after capitalising leaves you doing both twice.
- Excel's comparisons and lookups are already case-insensitive, so never apply these just to make a match work.
- For a genuinely case-sensitive comparison, use EXACT(A2, B2) rather than converting both sides.
- UPPER on reference codes makes a column visually consistent and easier to scan, even though nothing depends on it.
Frequently asked questions
Why does PROPER capitalise names like McDonald wrongly?
PROPER capitalises the first letter of each word and lower-cases everything after it, with no knowledge of naming conventions. "mcdonald" becomes "Mcdonald" rather than "McDonald". There is no argument to change this — you have to repair the exceptions with SUBSTITUTE or by hand.
Do I need UPPER to make a VLOOKUP match?
No. Excel's lookups and equality comparisons ignore case already, so "alice" matches "ALICE" without help. If a lookup is failing, look for trailing spaces or a number stored as text instead.
How do I capitalise only the first letter of a sentence?
PROPER capitalises every word, which is wrong for sentences. Combine UPPER and LOWER instead: =UPPER(LEFT(A2,1))&LOWER(MID(A2,2,LEN(A2))).
Related functions
Guides that use it
Longer reads where this function does real work in a real sheet.