UPPER, LOWER and PROPER standardise text capitalisation. Use them to clean inconsistent data so lookups and comparisons work correctly.
These three functions change the capitalisation of text. They are essential for standardising data that has been entered inconsistently — mixing "london", "LONDON", and "London" in the same column will break VLOOKUP and COUNTIF comparisons.
=UPPER(A2) ← "john smith" → "JOHN SMITH"
=LOWER(A2) ← "JOHN SMITH" → "john smith"
=PROPER(A2) ← "john smith" → "John Smith"
| Function | Converts to | Best for |
|---|---|---|
| UPPER(text) | ALL CAPITALS | Codes, keys, database IDs |
| LOWER(text) | all lowercase | Email addresses, usernames |
| PROPER(text) | First Letter Of Each Word | Names, titles, addresses |
=UPPER(A2) ← "finance" → "FINANCE"=LOWER(LEFT(A2,1))&"."&LOWER(B2)&"@company.com"Builds "j.smith@company.com" from first name "John" and last name "Smith".
=PROPER(A2) ← "MOHAMMED SHEIKH" → "Mohammed Sheikh"=IF(UPPER(A2)=UPPER(B2), "Match", "Mismatch")Convert both values to the same case before comparing — makes the comparison case-insensitive.
=PROPER(TRIM(A2))Remove extra spaces AND fix capitalisation in one formula.
PROPER capitalises the first letter of every word including words after apostrophes. "O'brien" becomes "O'Brien" correctly, but "McDonald" becomes "Mcdonald" (incorrect). Fix these manually or use SUBSTITUTE after PROPER.
Inconsistent capitalisation is one of the most common causes of VLOOKUP failures and COUNTIF undercounts. A column that should say "North" but contains a mix of "NORTH", "north", and "North" will not match correctly without standardisation. UPPER converts everything to the same case before comparison — making lookups and counts reliable.
For email address generation, the standard formula is =LOWER(LEFT(A2,1))&"."&LOWER(B2)&"@company.com". LOWER ensures the email is always lowercase regardless of how names were entered. Consistency in email generation prevents duplicate accounts and routing failures.
PROPER is the most commonly needed of the three — most data entry problems involve names or places typed in all caps (from legacy systems) or all lowercase (from mobile entry). PROPER fixes both. The one caveat: PROPER capitalises letters after apostrophes and hyphens, so "O'Brien" and "Al-Hassan" are handled correctly, but "McDonald" becomes "Mcdonald". A follow-up SUBSTITUTE can fix this if needed.
ExcelPro has UPPER, LOWER, PROPER and TRIM exercises across all tracks. Free to start.
Try text exercises →