Change Case in Excel: UPPER, LOWER and PROPER

To change case in Excel, you use one of three functions: UPPER for all capitals, LOWER for all small letters, and PROPER for a capital at the start of each word. Excel has no Change Case button like Word, so you write the formula in a helper column and then paste the results over the original. This guide covers all three, sentence case, Flash Fill, and the PROPER mistakes to watch for.

The three case functions#

Each function takes one argument, the text or cell to change, and returns a new version of the text:

These functions only change letters. Numbers, spaces and punctuation stay as they are. All three work in every version of Excel, on Windows and Mac.

Messy capitals usually come with other problems, such as extra spaces or stray characters. The guide to cleaning messy data in Excel shows how to fix them all together.

How to change case with a helper column#

Because the formula can't sit in the same cell as the text it changes, you work in a spare column and then replace the original. Say you have a list of names in A2:A6:

1. Click B2 and type =PROPER(A2). Press Enter.
2. Copy the formula down to B6. Double-click the fill handle, or select B2:B6 and press Ctrl+D (Mac: ⌘D).
3. With B2:B6 still selected, copy (Windows: Ctrl+C; Mac: ⌘C).
4. Select A2, then paste as values: right-click and choose Paste Special > Values (Windows: Ctrl+Alt+V, then V; Mac: ⌃⌘V, then choose Values).
5. Delete column B.

If you clean the same file every week, Power Query can do this too. Select the data, choose Data > From Table/Range, then right-click the column and pick Transform > lowercase, UPPERCASE or Capitalize Each Word. Click Close & Load, and next time just refresh.

Step 4 is the key. A normal paste would copy the formulas, which would point at themselves and break. Paste as values keeps only the finished text.

How to make sentence case#

Sentence case means only the first letter of the cell is a capital, like "Order shipped today". Excel has no SENTENCE function, but you can build it from four pieces:

=UPPER(LEFT(A2,1))&LOWER(MID(A2,2,LEN(A2)))

Here is what each part does with "oRDER SHIPPED today" in A2:

MID starts at the second character, and LEN(A2) is simply a number big enough to take the rest of the text.

PROPER mistakes to check for#

PROPER capitalises any letter that follows a non-letter, such as a space, an apostrophe, a hyphen or a number. That works for most names, but not all:

So after using PROPER on names or addresses, scan the results for apostrophes and numbers. You can fix common cases with SUBSTITUTE, for example =SUBSTITUTE(PROPER(A2),"'S ","'s ") to turn "It'S" back into "It's" mid-sentence. The guide to SUBSTITUTE and REPLACE shows more ways to fix specific text.

How to change case with Flash Fill#

Flash Fill copies a pattern you type by hand, so it can change case without a formula. Next to your list:

1. In B2, type the first name the way you want it, such as Jane Doe.
2. Start typing the second one in B3. Excel shows a grey preview of the rest.
3. Press Enter to accept, or use Data > Flash Fill (Windows: Ctrl+E).

Flash Fill is quick for one-off lists, but the results don't update if the original changes, and it can guess the pattern wrong on unusual entries. Check a few rows before you rely on it.

Does case matter in Excel formulas?#

For most formulas, no. An equals comparison, VLOOKUP, XLOOKUP, MATCH, COUNTIF and SUMIF all ignore case, so "apple", "Apple" and "APPLE" count as the same value. That means you often don't need to change case just to make a lookup work.

When case does matter, such as for passwords or product codes where "ab12" and "AB12" are different items, use EXACT. It compares two values letter for letter:

=EXACT(A3,UPPER(A3))

This returns TRUE if A3 is already in capitals and FALSE if it isn't, which is a quick way to find the codes that need fixing. The guide to EXACT and case-sensitive matching covers this in more detail. FIND is also case-sensitive, while SEARCH is not.

When to use each case#

A few simple rules keep data consistent:

• UPPER for codes and IDs: product codes, postcodes, country codes.
• PROPER for names, cities and job titles, followed by a quick check.
• LOWER for email addresses and web addresses, which are easier to compare in small letters.
• Sentence case for descriptions and notes.

Cleaning a column of names is a common first step in a case study interview task, so it's worth being quick at it.

Practise it#

Case functions are short, but the full clean-up routine (formula, fill down, paste values, check) is what tests and real jobs ask for. Practise cleaning text in a live spreadsheet: you type the formulas, including TRIM, PROPER and SUBSTITUTE, and it checks each result instantly. Or try the EXACT exercise on this page first.