PROPER, UPPER, LOWER Functions

These three functions all change the capitalization of text, but in different ways:

• UPPER(text) — converts every letter in the string to uppercase. =UPPER("hello world") → HELLO WORLD

• LOWER(text) — converts every letter to lowercase. =LOWER("Hello World") → hello world

• PROPER(text) — capitalizes the first letter of each word and lowercases all remaining letters. =PROPER("john smith") → John Smith

All three take a single argument — the text string or a cell reference — and return the converted string. They do not modify the original cell; the result appears in the cell where the formula is entered.

Typical use cases:

Limitation of PROPER: It capitalizes the letter after every word boundary, including after apostrophes and hyphens. This can produce undesired results like O'Brien → O'Brien (correct) but also Mcdonald → Mcdonald rather than McDonald. For proper names with unusual capitalization, manual correction or a more complex formula may be needed.

PROPER capitalizes the first letter of every word in a text string and converts all other letters to lowercase.

Syntax:

=PROPER(text)

text is either a literal string in quotes or a cell reference.

Examples:

=PROPER("john smith")        → John Smith
=PROPER("MARY JONES")        → Mary Jones
=PROPER("the quick brown fox") → The Quick Brown Fox
=PROPER(A1)                  → formats whatever text is in A1

Typical use case — cleaning a name column:

If you have a column of customer names imported from a database where names are all uppercase or all lowercase, PROPER standardizes them in one step:

=PROPER(A2)

Copy this formula down the column, then paste the results as values (Ctrl+Shift+V or Paste Special > Values) to replace the originals.

Important limitation: PROPER capitalizes the letter immediately after any word separator — including apostrophes, hyphens, and spaces. This can cause issues with names like:

• o'brien → O'Brien ✓ (correct)
• mcdonald → Mcdonald ✗ (should be McDonald)
• van der berg → Van Der Berg ✗ (some prefer van der Berg)

For a dataset where names follow consistent patterns, PROPER handles the vast majority of cases well. Edge cases may need a lookup table or manual correction.

Combining with TRIM: Data from external sources often has extra spaces. Combine both for cleaner results:

=PROPER(TRIM(A2))

LOWER converts all letters in a text string to lowercase, leaving numbers and other characters unchanged.

Syntax:

=LOWER(text)

Example:

=LOWER("HELLO WORLD")   → hello world
=LOWER("John Smith")    → john smith
=LOWER(A1)              → lowercased version of whatever is in A1

Situations where LOWER is useful:

1. Normalizing data for comparisons: When comparing text values, case differences cause mismatches — "North" <> "north". Converting both sides to lowercase before comparing ensures consistent results:

=IF(LOWER(A2)=LOWER(B2), "Match", "No match")

2. Cleaning email addresses: Email addresses should always be lowercase for storage and lookup consistency. If a user enters [email protected], =LOWER(A2) normalizes it to [email protected].

3. Preparing data for case-insensitive lookups: Before feeding data into a VLOOKUP, XLOOKUP, or database export, standardizing to lowercase eliminates case-based mismatches.

4. Generating slugs or codes: When creating URL slugs, file names, or system identifiers from display text, forcing lowercase is a standard step — often combined with SUBSTITUTE to replace spaces with hyphens:

=SUBSTITUTE(LOWER(A2)," ","-")

5. Consistency in combined datasets: When merging data from multiple sources where one used title case and another used all caps, LOWER (or PROPER) normalizes everything to a single format before analysis.

UPPER converts all letters in a text string to uppercase, leaving numbers, spaces, and special characters unchanged.

Syntax:

=UPPER(text)

text is a literal string or a cell reference.

Examples:

=UPPER("excel for interviews")  → EXCEL FOR INTERVIEWS
=UPPER("hello world")           → HELLO WORLD
=UPPER(A1)                      → uppercased version of A1's content

Common use cases:

1. Generating codes or identifiers: Product codes, country codes, and department abbreviations are often required in all caps. =UPPER(A2) ensures consistent formatting regardless of how the original data was entered.

2. Headings and labels: When building report templates, UPPER can programmatically format labels or category names as all caps without manually typing them.

3. Case-insensitive comparisons:

=IF(UPPER(A2)=UPPER(B2), "Match", "No match")

Forces both sides to uppercase before comparing, so the test is case-insensitive.

4. Cleaning data from mixed-case sources: If an imported column contains country names in inconsistent cases ("usa", "USA", "Usa"), =UPPER standardizes them all to "USA" before analysis or PivotTable grouping.

Combining with other functions:

=UPPER(TRIM(A2))

Removes extra spaces and converts to uppercase in one step — useful when cleaning imported data before analysis.

UPPER is a pure text function and does not affect numbers or special characters in the string.

All three functions transform the capitalization of a text string, but each produces a different result:

Syntax for all three is identical:

=UPPER(text)
=LOWER(text)
=PROPER(text)

Choosing between them:

• Use UPPER when you need codes, identifiers, or headings in all caps — or to normalize text before a case-insensitive comparison.
• Use LOWER when standardizing email addresses, usernames, or values for lookups and database exports.
• Use PROPER when cleaning name fields (first name, last name, full name) that arrive in inconsistent capitalization.

One important distinction — PROPER's behavior with punctuation:

PROPER capitalizes the character immediately after any word separator, including apostrophes and hyphens. This is usually correct (O'Brien, Jones-Smith) but can produce unexpected results with prefixes like McDonald (yields Mcdonald) or multi-word particles like van der in Dutch names.

UPPER and LOWER have no such edge cases — they unconditionally convert every letter in the string.

Combining with TRIM:

For imported data that may have extra spaces, combine these functions with TRIM:

=PROPER(TRIM(A2))