You inherit a spreadsheet. Customer names are screaming in ALL CAPS, product codes are lowercase mush, and somewhere in column D a row reads jOhN sMiTh because the data-entry intern had a long week. The boss wants it cleaned by lunch.

Excel has three text functions that solve this in a single formula: UPPER(text), LOWER(text), and PROPER(text). They take whatever case the source is in and force it to uppercase, lowercase, or title case respectively. No add-ins, no macros, no menu hunting — just type the formula in an empty column and drag it down.

And if formulas feel like overkill, Flash Fill (Ctrl+E) is sitting right there. Type one example of how you want a cell to look, press Ctrl+E, and Excel pattern-matches the rest of the column instantly. You don't even need to know the formula name.

This guide walks through every reliable method for case conversion in Excel: the three text functions, Flash Fill, Power Query for refresh-friendly workflows, a tiny VBA macro for true in-place edits, and the copy-paste-as-values trick that turns formula output into permanent text. We'll also hit the edge cases PROPER doesn't handle well — McDonald, O'Brien, MacGregor — because no one wants to send a mail-merge that calls a client Mcdonald.

The Three Functions: UPPER, LOWER, and PROPER

If you remember nothing else from this guide, remember the trio. They all take a single argument — the text or the cell reference holding the text — and they return a transformed copy without touching the original.

UPPER(A2) returns the contents of A2 with every letter forced to capitals. So practice tests becomes PRACTICE TESTS. Numbers, punctuation, and spaces pass through unchanged — only letter characters get the treatment.

LOWER(A2) goes the opposite direction. SCREAMING HEADER becomes screaming header. Useful for normalising email addresses (which should always be lowercase for matching) or cleaning up data that came in from an OCR scan with random capitalisation.

PROPER(A2) capitalises the first letter of every word and lowercases the rest. HENRY JONES and henry jones both end up as Henry Jones. It's the closest Excel ships to true title case, and it's the one most people reach for when cleaning name lists or product titles. The catch — and we'll get to it properly in a few sections — is that PROPER doesn't understand Mc, O', or hyphenated names. So MCDONALD becomes Mcdonald, not McDonald. Worth knowing before you ship a mail merge.

The mechanical pattern is the same for all three: put the formula in column B, point it at column A, drag down, then convert to values if you want to delete the original column. The whole flow takes maybe ninety seconds end-to-end on a 5,000-row list.

Flash Fill: When You Don't Want a Formula

Sometimes a formula feels like reaching for a hammer to swat a fly. If you've got fifty rows and you just want them in proper case, Flash Fill is faster than typing any function.

Put your cursor in B2 (the row next to your first messy name in A2). Type the cleaned version manually — say A2 reads JOHN SMITH and you type John Smith in B2. Press Enter. Then press Ctrl+E. Excel scans column A, spots the pattern (force title case), and fills B3 through B-whatever automatically.

Flash Fill needs at least one example to work from, and it gets better with two or three. If column A has tricky mixed inputs — some all caps, some lowercase, some already correct — type two or three examples before pressing Ctrl+E. Excel's confidence in the pattern goes up dramatically, and edge cases get handled cleanly.

The downside? Flash Fill is a one-shot transformation. If you later add a row to column A, you have to re-run Ctrl+E to fill the new row. That's why for ongoing data, the PROPER formula approach is more robust — it auto-recalculates whenever the source changes. Flash Fill is brilliant for one-time clean-ups, less so for live dashboards.

One more thing about Flash Fill: it doesn't only do case. The same Ctrl+E trick handles splitting names, combining first and last, extracting domains from email addresses, reformatting phone numbers — anything where you can show Excel an example output and trust it to infer the rule. Once you start using it, you'll wonder how you lived without it. For deeper text manipulation patterns, our guide on extract text in excel walks through LEFT, RIGHT, MID, and TEXTSPLIT with a similar mindset.

Power Query: Case Conversion That Survives a Refresh

If your data feeds in from a CSV, a database, or a web source through Power Query, you want the case transformation to live inside the query, not as a downstream formula. Otherwise every refresh wipes your cleaned values.

Load the data via Data → Get Data or by selecting a range and clicking From Table/Range. The Power Query Editor opens. Click the column header that needs fixing, then on the Transform tab look for the Format dropdown. Three options sit there: UPPERCASE, lowercase, and Capitalize Each Word. They map exactly to UPPER, LOWER, and PROPER respectively.

Pick one, click Close & Load, and the transformation runs every time the query refreshes. Add a row to the source file, hit refresh, and the new row gets the same case treatment automatically. No manual re-formula required.

Power Query's Capitalize Each Word has the same blindspot as PROPER — it doesn't know about McDonald or O'Brien. If those are common in your data, you can write a custom M-language step using Text.Combine and Text.Split to handle the prefixes, but for most lists the standard transformation is good enough and you fix the dozen exceptions by hand afterwards.

For more Power Query workflows, the text to columns in Excel guide covers data splitting, and Excel TRIM function deals with the leading and trailing spaces that often arrive in the same imported CSV. Combine those with Capitalize Each Word and you've got a three-step clean that handles ninety percent of real-world data hygiene.

Copy-Paste as Values: Locking In the Result

UPPER, LOWER, and PROPER return formulas, not text. Column B still depends on column A. If you delete column A without converting first, every cell in B turns into a #REF! error and your clean data vanishes with the source.

The fix is one extra step. Once column B has the values you want, select the whole column. Press Ctrl+C to copy. Then either right-click and choose Paste Special → Values, or press Ctrl+Alt+V then V then Enter to do the same with the keyboard. The formulas vanish, the text remains. Now column A is dispensable.

Forgetting this step is the most common case-conversion mistake. People drag the formula, delete the source, and watch their work disintegrate. Build the muscle memory: formula → drag → copy → paste-as-values. Four moves, every time.

Bonus move: if the converted column is large and you only want to overwrite the original (rather than keep two columns), copy column B, click into A1, paste-as-values directly. A is now the clean version. Delete B. One column, fully cleaned, no helper artefacts.

Edge Cases: Names PROPER Gets Wrong

Title case sounds simple until you actually try to write a rule for it. English names break the pattern constantly. Here's the short list of culprits.

Mc and Mac prefixes — McDonald, MacGregor, McKinnon. PROPER capitalises the M, lowercases everything else, so you get Mcdonald. The fix is a manual Find & Replace for Mc followed by a lowercase letter, replacing with Mc followed by an uppercase letter — or a custom formula that splits on Mc and re-capitalises.

O' prefixes — O'Brien, O'Connor, O'Donnell. PROPER capitalises the O, but it doesn't know to capitalise the letter after the apostrophe. O'brien is the typical output. Same fix pattern: Find & Replace, or a SUBSTITUTE wrapper.

Hyphenated names — Smith-Jones, Anne-Marie. PROPER actually handles these correctly because it treats hyphens as word separators. Good news.

Roman numerals and suffixes — John Smith III, Henry IV. PROPER turns III into Iii. If you've got a column of names with suffixes, you'll want a manual pass at the end or a SUBSTITUTE chain to restore the all-caps Roman numerals.

Brand names and acronyms inside text — NASA report through PROPER becomes Nasa Report. Acronyms aren't preserved. For brand-laden content, PROPER is often the wrong tool entirely — you're better off leaving the original case and just fixing the ALL CAPS rows manually, or building a dictionary-based VBA loop that knows your domain's special terms.

None of these are deal-breakers, but they're the reason a mail-merge sent through raw PROPER will occasionally embarrass you. Spot-check the output before you click Send.

The VBA Macro for True In-Place Edits

Every method so far creates a new column and asks you to overwrite the original later. If you'd rather have Excel rewrite the source cells directly — no helper column, no paste-as-values dance — a five-line VBA macro does the job.

Press Alt+F11 to open the VBA editor. Insert a module via Insert → Module. Paste the code below, swap vbProperCase for vbUpperCase or vbLowerCase as needed, and run with F5 after selecting the cells you want changed.

Sub ChangeCase()
  Dim c As Range
  For Each c In Selection
    If Not IsEmpty(c.Value) And Not c.HasFormula Then
      c.Value = StrConv(c.Value, vbProperCase)
    End If
  Next c
End Sub

The HasFormula check skips cells with existing formulas so you don't accidentally overwrite live calculations. The IsEmpty check skips blanks. StrConv is VBA's built-in case converter and it accepts vbUpperCase, vbLowerCase, or vbProperCase for the second argument — three macros from one shell, just by swapping that constant.

For more on macro fundamentals — modules, the security warning that fires when you save .xlsm, and how StrConv compares to UCase/LCase — see our VBA in Excel primer and the deeper Excel VBA walkthrough.

Removing Accidental ALL CAPS Data Entry

One of the most common reasons people Google case conversion in Excel is to undo a column of data that someone entered with Caps Lock on. The boss handed it back, the deadline is in an hour, and a whole column reads JANE DOE, JOHN SMITH, MARY JOHNSON when the rest of the spreadsheet is title case.

The cleanest fix is the standard recipe: =PROPER(A2) in column B, fill down, paste as values, replace column A. Done in under a minute. The only thing you have to watch is the Mc/O'/acronym list above — scan the result and fix the dozen-ish exceptions by hand.

If the column also has random capitalisation inside words (jOhN sMiTh), PROPER still works — it doesn't care what the input case was, it always outputs title case. Same for LOWER and UPPER — they normalise to a single case regardless of the chaos in the source.

For columns with mixed quality — some rows are already title case, some are all caps, some are lowercase — PROPER also gives you uniform output. The previously-correct rows pass through unchanged, the messed-up ones get fixed. So even running PROPER over an already-clean column is safe and idempotent.

A small note on apostrophes and quotes: smart quotes (the curly ones Word inserts) sometimes confuse PROPER when they appear mid-word. If your data came from a Word doc, run find and replace in Excel to swap curly quotes for straight quotes before applying PROPER. Cleaner output, fewer surprises.

And while we're on the subject of upstream cleaning: leading or trailing spaces wreck case conversions visually. " john smith" through PROPER returns " John Smith" — still with the leading spaces. Wrap the whole thing in TRIM: =PROPER(TRIM(A2)). Nested functions handle the trim and the case in a single pass.

Putting It All Together

The honest answer to "how do I change case in Excel" is: depends on the job. One-off clean-up of a 50-row list? Flash Fill (Ctrl+E) and move on with your day. Live dashboard pulling from a CSV that refreshes daily? Power Query so the transformation persists. A formula on top of source data you'll keep around? UPPER, LOWER, or PROPER with the paste-as-values follow-up. True in-place rewrite across multiple sheets? A four-line VBA macro.

The function names also matter outside Excel itself — the same UPPER, LOWER, PROPER pattern shows up in Google Sheets, in SQL (UPPER and LOWER are standard, PROPER is a Sheets-and-Excel specialty), and in nearly every programming language as upper(), lower(), and some flavour of titleCase(). Learning the trio in Excel buys you fluency in text manipulation across the entire data ecosystem.

If you take one habit from this guide, make it the nested TRIM trick: =PROPER(TRIM(A2)), =UPPER(TRIM(A2)), =LOWER(TRIM(A2)). One extra function call wraps every input in a leading-and-trailing space cleaner, so when the data inevitably arrives with stray whitespace, your formulas just shrug and produce clean output anyway. Combine that with a paste-as-values step at the end and you've got a bulletproof workflow that handles ninety-five percent of real spreadsheets.

The remaining five percent is the edge cases — McDonald, O'Brien, NASA, John Smith III. Eyeball those, fix them manually, and move on. Excel isn't psychic, and no built-in function is going to know that nasa should be NASA while john should be John. That's domain knowledge, and that's why you still get paid.

Whichever method you settle on, the principle is the same: change case is a one-line problem with a one-second solution. Build the muscle memory once, and you'll never again waste lunch hour retyping a column of customer names because the intern left Caps Lock on.