Names, titles and product lists often arrive in the wrong case: all capitals, all lowercase or a random mix. Excel has three functions for changing case, UPPER, LOWER and PROPER, and a simple formula for capitalizing only the first letter of a sentence. This guide shows how to use each, how to capitalize the first letter of every word or only of the first word, how to handle names such as McDonald and abbreviations, how to use Flash Fill and Power Query, and how to turn the results into values so that you can delete the original column.
Updated November 2026
In this guide
- The three case functions
- Capitalize the first letter of every word
- Capitalize only the first letter of the text
- Flash Fill for a quick fix
- Power Query for repeated imports
- Examples table
- Handling names and abbreviations
- Capitalize the first letter of each sentence in a cell
- Turn the formulas into values
- Related case tasks
- Worked example: a product catalog
- Change case in Google Sheets
- Quick recap
- Checking your results
- Common mistakes
The three case functions
Excel gives you three functions. UPPER turns all letters into capitals: =UPPER("sara ali") returns SARA ALI. LOWER turns all letters into lowercase: =LOWER("SARA ALI") returns sara ali. PROPER capitalizes the first letter of every word and lowers the rest: =PROPER("sARA aLI") returns Sara Ali. All three take a cell reference or text and return a new value, so the original stays unchanged unless you paste the result back as values.
Capitalize the first letter of every word
Use PROPER. If the text is in A2, type =PROPER(A2) in B2 and fill down. A list such as “ayesha khan”, “BILAL AHMED” and “sara ali” becomes “Ayesha Khan”, “Bilal Ahmed” and “Sara Ali”. PROPER treats any character that is not a letter as the start of a new word, which gives surprises: “o’brien” becomes “O’Brien”, which is correct, but “mcdonald” becomes “Mcdonald”, and “it’s” becomes “It’S” in some cases. For names that need special handling, see the exceptions section below.
Capitalize only the first letter of the text
To capitalize only the first letter of a sentence and leave the other letters alone, use =UPPER(LEFT(A2,1))&MID(A2,2,LEN(A2)). LEFT takes the first character, UPPER makes it a capital, MID returns everything from the second character on, and the ampersand joins them. For “excel is useful” it returns “Excel is useful”. To also force the rest to lowercase, use =UPPER(LEFT(A2,1))&LOWER(MID(A2,2,LEN(A2))). For a sentence with capitals in the middle, such as “meet NASA staff”, the first version keeps NASA as it is, and the second turns it into “Meet nasa staff”, so choose carefully.
Flash Fill for a quick fix
Type the correct version of the first value, such as “Ayesha Khan”, in the next column. Start typing the second one and Excel shows a preview. Press Enter to accept or use Ctrl+E. Flash Fill works well for names with a clear pattern, but the output is static and will not update if you change the source. Check the results, because Flash Fill can misread unusual cases.
Power Query for repeated imports
Select your data and choose Data, then From Table/Range. In the editor, select the column, go to Transform, then Format, and choose Capitalize Each Word, UPPERCASE, lowercase or Trim. Close and Load. When the source changes, use Refresh All to reapply the same case changes. Power Query is the best choice for weekly reports, since you set it up once.
Examples table
The table shows what each method returns for the same input.
| Input | PROPER | UPPER | First letter only |
|---|---|---|---|
| sara ali | Sara Ali | SARA ALI | Sara ali |
| BILAL AHMED | Bilal Ahmed | BILAL AHMED | Bilal ahmed |
| mcdonald farm | Mcdonald Farm | MCDONALD FARM | Mcdonald farm |
| meet NASA staff | Meet Nasa Staff | MEET NASA STAFF | Meet NASA staff |
Handling names and abbreviations
PROPER cannot know that “McDonald” has a second capital or that “NASA” should stay in capitals. A common fix is to run PROPER and then correct exceptions with SUBSTITUTE. For example, =SUBSTITUTE(PROPER(A2),"Mcd","McD") turns “Mcdonald” into “McDonald”. For several exceptions, nest SUBSTITUTE functions or keep a small table of exceptions and use a lookup. You can also protect abbreviations by correcting them at the end: =SUBSTITUTE(PROPER(A2),"Nasa","NASA"). Always review the final list, because no rule covers every name in every language.
Capitalize the first letter of each sentence in a cell
If a cell holds several sentences, such as “first sentence. second sentence. third one.”, you need a longer formula. In Microsoft 365, a simple approach is to split the text at the period and capitalize each part with a helper formula, but for a handful of cells it is faster to use Word. Paste the text into Word, select it and press Shift+F3 to cycle through the case options, including Sentence case, then paste it back. For repeated work, a short VBA macro or a Power Query function can handle it, but those need more setup.
Turn the formulas into values
When you are happy with the result, copy the formula cells, right-click the original column, and choose Paste Special, then Values. Delete the helper column afterward. If you skip this step and delete the source column, the formulas will show #REF! errors. Pasting as values also makes the file smaller and faster, because formulas are not recalculated. Keep a backup of the original file in case you need to check the source.
Related case tasks
You can combine these case functions with other text tools. To build an email address in lowercase from a first and last name, use =LOWER(A2&"."&B2&"@example.com"). To create a username with the first initial in capitals, use =UPPER(LEFT(A2,1))&LOWER(B2). To clean the data first, wrap the cell in TRIM, as in =PROPER(TRIM(A2)), which removes extra spaces and then fixes the case. For the text-joining functions, see Microsoft’s page on the CONCAT function and our guide to combining text in Excel.
Worked example: a product catalog
Imagine a catalog with titles in column A such as “WIRELESS mouse with usb receiver”, “bluetooth KEYBOARD, slim” and “usb-c cable 2m”. You want sentence case: only the first letter in capitals. In B2 type =UPPER(LEFT(A2,1))&LOWER(MID(A2,2,LEN(A2))) and fill down. The results are “Wireless mouse with usb receiver”, “Bluetooth keyboard, slim” and “Usb-c cable 2m”. Notice that “usb” is now lowercase inside the sentence, which is wrong for an abbreviation. Add a correction with SUBSTITUTE: =SUBSTITUTE(B2,"usb","USB") fixes “Wireless mouse with USB receiver”, but it leaves “Usb-c cable 2m” unchanged, because SUBSTITUTE is case sensitive and the capital U does not match. You would need a second SUBSTITUTE for “Usb”. This shows why you should run corrections in the right order and review the output. For title case instead, use PROPER on the whole column and then fix “Usb” to “USB”.
Change case in Google Sheets
Google Sheets uses the same functions: UPPER, LOWER and PROPER work the same way. Use =ARRAYFORMULA(PROPER(A2:A100)) to convert a whole range in one formula without filling down. For sentence case, use =ARRAYFORMULA(UPPER(LEFT(A2:A100,1))&LOWER(MID(A2:A100,2,LEN(A2:A100)))). When you move a file between Excel and Sheets, test the formulas, since small differences in array behavior can change the results. In both tools, convert formulas to values before you share the file with people who do not need the logic.
Quick recap
Use PROPER for names and titles, UPPER for codes, LOWER for emails and a LEFT, MID combination for sentence case. Correct exceptions with SUBSTITUTE, clean spaces with TRIM first and paste values when finished. These few formulas cover almost every capitalization task you will meet in a spreadsheet.
Checking your results
Before you delete any source data, compare a sample of rows against the original. Look for names with apostrophes, hyphens and prefixes such as van, de and al, which PROPER may format differently from what you expect, and fix them with a short list of exceptions. Keep that list in a separate sheet and reuse it for the next import.
Common mistakes
The most frequent mistakes are applying PROPER to text that includes abbreviations or code letters, forgetting that the formula returns a new cell, and deleting the source before pasting values. Another is to use UPPER on a column that also has numbers stored as text, which does not change the numbers but may confuse later lookups. A fourth is not removing extra spaces first, which can cause a leading space to hide the first letter from LEFT. Finally, test your formula on three or four rows before filling it down through thousands of rows.
Frequently asked questions
How do I capitalize the first letter of each word in Excel?
Use =PROPER(A2).
How do I capitalize only the first letter of a cell?
Use =UPPER(LEFT(A2,1))&MID(A2,2,LEN(A2)).
How do I make everything uppercase in Excel?
Use =UPPER(A2).
Why does PROPER change McDonald to Mcdonald?
PROPER lowers every letter after the first in each word. Fix exceptions with SUBSTITUTE.
How do I keep the changes after deleting the original column?
Copy the formula results and use Paste Special, Values.
Related guides and free tools
- How to remove spaces in Excel
- How to combine text in Excel
- VLOOKUP not working: 8 fixes
- IF statements in Excel
- All free calculators
- All free calculators and templates
Sources: CONCAT function, Microsoft Support


