sodcalculator

10 Tech How-To

How to Change Text Case in Excel

Guide 10-49310 · Updated · Step by step

Changing text case in Excel is a common task when cleaning up imported data or standardizing entries like names, product codes, or addresses. Excel provides three dedicated functions—UPPER, LOWER, and PROPER—that instantly convert text to all uppercase, all lowercase, or proper case (first letter of each word capitalized). You don’t need any special add-ins or programming knowledge; just a basic understanding of formulas. This guide walks you through each method, including how to replace the original text with the converted version.

What You’ll Need

  • Microsoft Excel (2010 or later, including Microsoft 365 and Excel for the web)
  • A worksheet containing the text you want to convert
  • Basic familiarity with entering formulas (e.g., typing = and selecting cells)

Step-by-Step

  1. Identify the cell or range containing the text you want to change. For example, if your data is in column A from A2 to A10, you’ll be working with that range.
  2. Decide which case conversion you need:
    • UPPER: converts all letters to uppercase (e.g., “john smith” → “JOHN SMITH”)
    • LOWER: converts all letters to lowercase (e.g., “JOHN SMITH” → “john smith”)
    • PROPER: capitalizes the first letter of each word and lowercases the rest (e.g., “john smith” → “John Smith”)
  3. Insert a new column next to your data (e.g., column B) to hold the converted text. This keeps your original data intact until you’re satisfied with the results.
  4. Click on the first empty cell in the new column (e.g., B2).
  5. Type the formula for your chosen case. For uppercase, enter: =UPPER(A2). For lowercase, enter: =LOWER(A2). For proper case, enter: =PROPER(A2). Replace A2 with the actual cell reference of your first data cell.
  6. Press Enter to see the converted text appear in the cell.
  7. Drag the fill handle (the small square at the bottom-right corner of the cell) down to copy the formula to the rest of the cells in your range. Release the mouse button to apply the conversion to all selected cells.
  8. Review the results in column B. If everything looks correct, you can replace the original column with the converted values.
  9. To replace the original data: Select the entire range in column B (e.g., B2:B10), press Ctrl+C to copy, then right-click on cell A2 and choose Paste Special → Values. This pastes the converted text as plain values, overwriting the original text.
  10. Delete the temporary column (column B) if you no longer need it.

Common Mistakes

  • Forgetting to use Paste Special → Values – If you simply copy and paste the formula cells over the original, you’ll end up with formulas referencing themselves, causing a circular reference error. Always use Paste Special → Values to paste only the results.
  • Applying the formula to the wrong cell reference – Double-check that the cell reference in the formula matches the row of the cell you’re entering it into. For example, in B2, the formula should reference A2, not A1.
  • Assuming the function changes the original cell – The UPPER, LOWER, and PROPER functions are not in-place; they create new values in a different cell. You must use the copy-paste method to replace the original data.
  • Not accounting for extra spaces – If your text has leading/trailing spaces, the case conversion won’t remove them. Use the TRIM function in combination if needed, e.g., =UPPER(TRIM(A2)).
  • Using PROPER on acronyms or names like “McDonald” – PROPER capitalizes the first letter of every word, so “McDonald” becomes “Mcdonald”. You’ll need to manually fix such exceptions.

Tips

  • Use Flash Fill for quick conversions (Excel 2013 and later): Type the converted version of the first entry manually in the adjacent cell, then press Ctrl+E. Excel detects the pattern and fills the rest. This works well for simple conversions but may not be reliable for complex patterns.
  • Combine with other functions to clean data: For example, =PROPER(TRIM(A2)) removes extra spaces and capitalizes properly. You can also nest within IF statements for conditional conversions.
  • Use absolute references if you need to copy the formula across multiple columns while keeping the same source cell, e.g., =UPPER($A$2).
  • Convert entire columns at once by selecting the range and typing the formula, then pressing Ctrl+Enter instead of just Enter. This fills all selected cells with the formula relative to each row.
  • Handle non-English characters: Excel’s functions work with most accented characters, but if you have special Unicode text, test a sample first to ensure the conversion behaves as expected.

Troubleshooting

Formula returns #NAME? error: This usually means you’ve typed the function name incorrectly (e.g., “UPPERCASE” instead of “UPPER”) or you’re using an older Excel version that doesn’t support these functions (unlikely, as they’ve been around for decades). Check the spelling and ensure there’s no extra space before the equals sign.

Formula returns #VALUE! error: This occurs if the cell reference is invalid or the cell contains a value that Excel can’t convert (like a number). UPPER, LOWER, and PROPER work on text; if the cell contains a number, it will be treated as text and converted without error, but if it contains an error value, that error propagates.

Converted text appears as a formula (e.g., =UPPER(A2)) instead of the result: This happens if the cell is formatted as Text. Select the cell, change the number format to General, then re-enter the formula by pressing F2 and Enter.

PROPER capitalizes words that shouldn’t be capitalized (e.g., “iPhone” → “Iphone”): PROPER is simplistic and doesn’t recognize brand names or camelCase. You’ll need to manually correct these after conversion or use a more advanced method like VBA for complex rules.

Paste Special → Values doesn’t work: Make sure you’ve selected the range of formula cells, copied them (Ctrl+C), then right-clicked on the destination cell and chosen Paste Special. If the destination is the same as the source, you may need to copy to a different sheet first, then paste back.

Frequently Asked Questions

Q: Can I change text case without using formulas?
A: Yes, you can use Flash Fill (Excel 2013+) by typing the first converted value manually and pressing Ctrl+E. Also, Microsoft Word’s Change Case feature can be used if you copy-paste data there, but it’s less efficient for large datasets.

Q: Does the PROPER function handle names like “O’Brien” correctly?
A: No, PROPER capitalizes the letter after an apostrophe, so “O’Brien” becomes “O’Brien” (which is correct in many cases), but it will also capitalize after hyphens, which may not always be desired. For example, “Jean-Luc” becomes “Jean-Luc” (correct), but “McDonald” becomes “Mcdonald” (incorrect). You’ll need to fix exceptions manually.

Q: Can I convert case in a column without adding a new column?
A: Yes, you can use a macro (VBA) to loop through cells and change them in place. However, this is more advanced and requires enabling macros. For most users, the helper column method is safer and easier to undo.

Q: Will these functions work on numbers or dates?
A: They will convert numbers to text with the case change (e.g., 123 becomes “123”), but there’s no visual change since numbers have no letters. Dates are converted to their text representation (e.g., “1/1/2024”) and then case-converted, but this is rarely useful. It’s best to only apply these functions to text data.

Q: How do I convert case in Excel for Mac?
A: The steps are identical. The functions UPPER, LOWER, and PROPER work the same way, and the keyboard shortcuts may differ slightly (e.g., Cmd+C for copy, Cmd+V for paste). Flash Fill is available in Excel for Mac 2016 and later.

Q: Can I use these functions in Excel Online (web version)?
A: Yes, UPPER, LOWER, and PROPER are available in Excel for the web. Flash Fill is not available in the web version, so you’ll need to use the formula method.

Related

General steps for current mainstream versions; menus can differ by device and version. See the disclaimer or report an error.

More in this bed

The whole Tech How-To bed →