The Three Built-In Functions for Changing Case

Excel has three functions that change the case of text without retyping it: UPPER() converts text to all capitals, LOWER() converts to all lowercase, and PROPER() converts to title case (first letter of each word capitalized). These work in any version of Excel on Windows or Mac.

Each function does one job only. If you need to convert "john smith" to "JOHN SMITH", you use UPPER. If you need "JOHN SMITH" to become "John Smith", you use PROPER. There is no single function that does all three—you pick the one that matches what you need.

Key Takeaways

  • UPPER(), LOWER(), and PROPER() are the three case-change functions; each converts text in a specific direction.
  • Type the function in an empty column next to your original text, then copy the results and paste them as values to replace the original.
  • PROPER capitalizes the first letter of every word, which works well for names but may capitalize words you want lowercase (like "and" or "the").
  • You can nest these functions with other formulas, such as combining PROPER with TRIM to clean up spacing at the same time.

How to Use UPPER() to Convert Text to All Capitals

Click on an empty cell next to the text you want to change. Type =UPPER(A1), replacing A1 with the cell containing your text. Press Enter. The cell now shows the text in all capitals.

If your original text is in column A from rows 1 to 50, click the cell in column B row 1, type =UPPER(A1), then press Enter. Click that same cell again, then drag the small square at the bottom right corner down to row 50. Excel copies the formula down and converts all 50 entries at once.

The original text in column A stays unchanged. The converted text appears only in column B. If you want to replace the original, copy column B, right-click column A, choose Paste Special, click Values Only, and click OK. Then delete column B.

How to Use LOWER() to Convert Text to All Lowercase

The process is identical to UPPER(), but you type =LOWER(A1) instead. Click an empty cell, type the formula, press Enter, then drag down to fill the rest of the column if needed.

LOWER() is useful when you have text in mixed or all capitals and need it uniform and lowercase—common when cleaning up data from older systems or different sources. Like UPPER(), it leaves the original text untouched and creates the converted version in a new column.

How to Use PROPER() to Convert Text to Title Case

Type =PROPER(A1) in an empty cell next to your text. Press Enter. Excel capitalizes the first letter of each word and makes the rest lowercase.

PROPER() works well for names and titles, but it has a limitation: it capitalizes the first letter after any space or punctuation. If you have "mcdonald's restaurant", PROPER() converts it to "Mcdonald'S Restaurant" instead of "McDonald's Restaurant". You may need to fix these by hand, or use Find & Replace to catch common patterns like apostrophes.

Replacing Your Original Text with the Converted Version

After you have created the converted text in a new column, you have two choices: keep both columns, or replace the original. To replace the original, first copy the entire column of converted text. Click the column header letter to select the whole column, then press Ctrl+C (or Cmd+C on Mac).

Click the column containing your original text. Right-click and choose Paste Special. A dialog box opens. Click the radio button next to Values Only, then click OK. Excel pastes only the text itself, not the formula, so the original column now contains the converted version. You can then delete the helper column.

Do not skip the Paste Special step. If you paste normally, you paste the formula, not the result. The original column will then show errors or formulas instead of text.

Combining Case Functions with Other Formulas

You can nest case functions inside other formulas. For example, =PROPER(TRIM(A1)) removes extra spaces and converts to title case in one step. =UPPER(LEFT(A1,1))&LOWER(RIGHT(A1,LEN(A1)-1)) capitalizes only the first letter and lowercases the rest—useful for names where you want "John" not "JOHN".

These combinations are more advanced, but they save time if you are cleaning large datasets. Start with the straightforward functions first, then add complexity only if you need it.

Common Mistakes and How to Avoid Them

The most common mistake is pasting the formula result back into the original column without using Paste Special. This creates a circular reference or overwrites the formula before it finishes. Always use Paste Special with Values Only selected.

Another mistake is forgetting that PROPER() capitalizes after punctuation. "o'brien" becomes "O'Brien" instead of "O'brien". Check the results before you delete the original column, especially with names that have apostrophes or hyphens.

A third mistake is explore the function to a column that already contains formulas. If column A has =CONCATENATE(B1,C1), and you explore UPPER() to it, you are converting the result of that formula, not the formula itself. This usually works fine, but be aware of what you are converting.

Frequently Asked Questions

Can I change case without creating a new column?

Not directly. Excel functions always need a place to put their result. You can use a new column and then copy the results back, or you can use a helper column and delete it after. There is no way to change case in place without a formula or helper column.

What if I have mixed case like "jOhN sMiTh" and want "John Smith"?

Use PROPER(). It converts any mix of uppercase and lowercase to title case, so "jOhN sMiTh" becomes "John Smith". This is the most common fix for data that has been typed inconsistently.

Does PROPER() work with numbers or special characters?

PROPER() ignores numbers and special characters. If your text is "123 main street", PROPER() returns "123 Main Street"—the numbers stay the same, only the letters change. This is usually what you want.

Can I undo a case change if I pasted values over the original?

Yes, if you have not closed the file. Press Ctrl+Z (or Cmd+Z on Mac) to undo. If you closed the file, the original text is gone. This is why it is safer to keep the original column until you are certain the conversion is correct.

What if the formula shows #NAME? error?

This usually means you typed the function name wrong. Check that you typed UPPER, LOWER, or PROPER exactly—no extra letters or spaces. Also check that you included the parentheses and the cell reference inside them, like =UPPER(A1).