Cellwise Try it free

Excel guide · 3 min read

How to change text to uppercase or lowercase in Excel

Quick answerUse =UPPER(A2) for capitals, =LOWER(A2) for lowercase, and =PROPER(A2) to capitalize each word. Excel has no Change Case button like Word, so put the formula in a helper column, then copy it and use Paste Values over the original.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

ABCD
1NameEmailSKUCountry
2MARIA GARCIA[email protected]tsh-red-lus
3tom o'neil[email protected]Mug-Blu-12gb
4Priya Shah[email protected]cap-blk-osIN

UPPER: all capitals

UPPER changes every letter to a capital and leaves digits, spaces and punctuation alone. It is the right choice for codes that a system matches exactly, such as SKUs, country codes and postcodes, so that "tsh-red-l", "Tsh-Red-L" and "TSH-RED-L" all end up identical.

=UPPER(C2)

Result: TSH-RED-L

Returns "TSH-RED-L".

LOWER: all lowercase

LOWER does the opposite. Use it on email addresses before you deduplicate or send them to a mailing tool. Email providers ignore case in practice, but two spellings of the same address can still look like two contacts in a list.

=LOWER(B3)

Result: [email protected]

Returns "[email protected]".

PROPER: capitalize each word

PROPER capitalizes the first letter of every word and lowercases the rest. It is the usual fix for names exported in capitals. It treats any non-letter as a word break, so O'Neil and Smith-Jones come out right, but McDonald becomes Mcdonald and "3rd" becomes "3Rd".

For sentence case (only the first letter capitalized), see the capitalize-first-letter guide.

=PROPER(A2)

Result: Maria Garcia

Returns "Maria Garcia".

Replace the original text

These functions return a new value in another cell. To change the original column, convert the formulas to values and paste them over it. Skip the Paste Values step and deleting the original column will turn every formula into #REF!.

  1. In an empty column next to your data, enter =UPPER(A2) (or LOWER or PROPER) and fill it down.
  2. Copy the helper column (Ctrl+C).
  3. Select the first cell of the original column.
  4. Go to Home > Paste > Paste Values (or press Ctrl+Alt+V, choose Values, and click OK).
  5. Delete the helper column.

Without a formula: Flash Fill and Power Query

Flash Fill copies a pattern from your example. Type the first value the way you want it in the next column, then press Ctrl+E. Check the results, because Flash Fill guesses and can get unusual rows wrong. It does not update if the source changes.

For data you import again every week, Power Query is better. Select the data, go to Data > From Table/Range, select the column, then Transform > Format > UPPERCASE, lowercase or Capitalize Each Word. Close & Load, and next time just click Refresh.

Excel mostly ignores case anyway

Comparisons with =, COUNTIF, SUMIF, VLOOKUP, XLOOKUP and MATCH are not case-sensitive. "us" and "US" count as the same country, so you often do not need to fix case just to make a lookup work. Fix it for reports, mail merges and systems outside Excel.

When you do need a case-sensitive check, use EXACT: =EXACT(D4,"IN") returns TRUE only if the case matches too. FIND and SUBSTITUTE are also case-sensitive.

=D2="US"

Result: TRUE

Returns TRUE, even though D2 holds lowercase "us".

Common mistakes

Looking for a Change Case button

That is Shift+F3 in Word. Excel has no equivalent. Use UPPER, LOWER or PROPER in a helper column, or Flash Fill.

Deleting the source column before pasting values

The helper formulas still point at it and turn into #REF!. Paste Values first, then delete.

Running PROPER over emails or codes

PROPER turns "[email protected]" into "[email protected]" and "tsh-red-l" into "Tsh-Red-L". Use LOWER for emails and UPPER for codes.

FAQ

What is the shortcut to change case in Excel?

There is none. Shift+F3 changes case in Word and PowerPoint, but not in Excel. Use a formula or Flash Fill (Ctrl+E).

How do I make a whole column uppercase in Excel?

Put =UPPER(A2) in a helper column, fill it down, copy it, and Paste Values over the original column. Then delete the helper.

Is VLOOKUP case-sensitive?

No. VLOOKUP, XLOOKUP, MATCH and COUNTIF treat "abc" and "ABC" as the same. Use EXACT inside a formula when case must match.

Can I force uppercase as people type?

Not with a format. Data Validation can reject entries that are not uppercase using a custom rule like =EXACT(A2,UPPER(A2)), or you can convert the column with UPPER afterwards.

Functions used

Related guides

Updated