To capitalize every word in Excel, enter =IF(A2="","",PROPER(A2)) in an empty column beside the text in A2, then fill the formula down. Excel changes the first letter of each word to uppercase and the remaining letters to lowercase. You can also use Flash Fill for a quick, formula-free result. Before starting, decide whether you want to keep the original text or replace it with the converted version.
What You’ll Need
- An Excel workbook with the text you want to change. The instructions below are for Excel for Microsoft 365 on Windows.
- An empty column or range next to the source text if you plan to use a formula or Flash Fill. Keeping the result separate makes it easier to review before replacing anything.
- Permission to edit the workbook. If it is shared or protected, you may need to save a copy or ask the workbook owner to unlock the relevant cells.
Step-by-Step
- Locate the text and make a copy if needed. Find the column containing names, labels, or other text. If the data matters, save a separate copy of the workbook first. Excel’s formula method leaves the original column untouched until you choose to replace it, but a backup protects against accidental edits and lets you compare the before-and-after values.
- Choose an empty output column. For example, if the original entries are in column A, use column B. Add a heading such as Title Case in B1 so you can identify the converted results. Do not place the formula in the same cell as the original text: a formula that refers to its own cell creates a circular reference rather than converting the value.
- Enter the formula in the first output row. With the source text in A2 and the result intended for B2, type
=IF(A2="","",PROPER(A2))in B2 and press Enter. The IF check keeps a truly empty source cell from displaying a zero; PROPER performs the capitalization. For example, if A2 contains mARIA loPEZ, B2 displays Maria Lopez. If your first source row is different, change A2 in the formula to that row’s cell reference. - Fill the formula down the column. Select B2 and point to the small square at its lower-right corner, called the fill handle. When the pointer becomes a thin cross, double-click the handle to copy the formula down beside adjacent entries in column A. You can also drag it down to a specific row. Check the last row afterward: a double-click usually follows the neighboring filled column, but gaps in that column can stop the fill early.
- Review the converted results. Compare several entries near the top, middle, and bottom with the original data. Confirm that the formula has changed the capitalization you intended and has not missed rows. Look closely at acronyms, initials, punctuation, and names with unusual spellings, because PROPER applies a general rule rather than knowing which capitalization is correct for a particular person or organization.
- Keep both columns or replace the original values. If you want a reversible result, leave the original and converted columns in the sheet. If you need the converted text in the original location, select the filled results in column B, copy them, then select the first source cell in column A. Choose Home > Paste > Values. This pastes the displayed text rather than formulas, so the results no longer depend on column A. Verify the pasted column before deleting the temporary results.
- Try Flash Fill for a quick alternative. If you prefer not to use a formula, type the correctly capitalized version of the first source entry in the adjacent cell. Type a second example if the pattern is not obvious. Select the next output cell and press Ctrl+E on Windows, or choose Data > Flash Fill. Review the suggested entries before using them. Flash Fill creates text values, not a live formula, so edits to the original text will not automatically update the filled results.
Common Mistakes
- Expecting title-style rules. PROPER capitalizes the start of every word, including short words such as and, of, and in. It does not apply a publication’s style guide, which may keep some words lowercase or capitalize them differently.
- Assuming acronyms stay uppercase. The function lowercases the remaining letters in a word. An entry such as NASA may become Nasa, and an initial such as McDONALD may not get the special capitalization you expect. Check these cases individually and correct the output where needed.
- Overwriting the source too soon. If you paste formulas over the cells they refer to, the references can break or create errors. Convert the results to values in a separate column first, then copy those values over the original if that is your goal.
- Filling only part of the list. A blank or interrupted neighboring column can affect where a double-click on the fill handle stops. Confirm the output reaches the same final row as your source data, especially when the list contains blank rows.
- Expecting a formatting change. This process changes the text returned by the formula or Flash Fill; it is not just a font or cell-format setting. Changing the font or using a visual format will not reliably convert the letter case of the underlying text.
Tips
- Keep the source column until you finish checking. A separate output column makes errors easy to spot and gives you a clean way to redo the conversion. Hide the source column later if you do not want it visible, rather than deleting it before review.
- Use a Table for data that grows. If your list is an Excel Table, a formula entered in a new table column will generally fill through the existing rows and can extend to new rows. Check the behavior in your workbook, especially if calculated columns have been turned off or the table has been altered.
- Watch for spaces and inconsistent input. PROPER changes case but is not a full data-cleaning tool. It does not decide whether a name is spelled correctly or remove unwanted spaces. If entries have leading spaces, repeated spaces, or inconsistent punctuation, inspect and clean those separately.
- Use Flash Fill when the desired pattern is custom. Flash Fill can follow examples such as initials or a particular presentation pattern, but it may infer the wrong rule from a small sample. Give it more than one clear example when entries vary, and scan the whole output rather than accepting it blindly.
- Remember that formulas update. The results in the formula column change when the source cells change. That is useful for an ongoing list. If you need a fixed snapshot for importing or sharing, paste the results as values after checking them.
Troubleshooting
- The formula appears as text instead of a result. Check whether the cell is formatted as Text or whether an apostrophe was typed before the equals sign. Change the cell format to General, then re-enter the formula by selecting the cell, pressing F2, and pressing Enter. If Show Formulas is on, turn it off from the Formulas tab or use Ctrl+` on Windows.
- You see a formula error or the wrong cell’s text. Confirm that the formula points to the source cell on the same row. If your list begins on row 5, for example, the first output formula should refer to the source cell on row 5, not A2. Also check that the formula was entered in an empty output cell and that the workbook is set to calculate automatically.
- Blank rows show something unexpected. The formula above returns an empty string when its referenced cell is empty. A cell containing spaces is not truly empty, however, so it may still produce a result made up of spaces. Remove or correct space-only source entries if they appear blank but behave differently.
- Flash Fill does not fill the pattern. Make sure the examples are in the column beside the source data and clearly demonstrate the output you want. Then select the next cell in the output column and run Data > Flash Fill or press Ctrl+E on Windows. If the result is inconsistent, undo it, add another example, and run Flash Fill again. In Excel for the web, use the formula method if Flash Fill is unavailable in your version.
- Some words have the wrong capitalization. That is expected for exceptions PROPER cannot know, such as acronyms, brand names, or specially styled surnames. Correct those individual results after converting, or use a carefully reviewed custom process if the same exceptions recur throughout a large dataset.
Frequently Asked Questions
Does Excel have a button to capitalize every word?
Excel for Microsoft 365 does not have a general worksheet button that changes selected text to title case. Use PROPER in a helper column or use Flash Fill to create converted values.
What is the difference between PROPER and uppercase?
PROPER capitalizes the first letter of each word and lowercases the other letters. Uppercase changes every letter to a capital. For word capitalization, use PROPER rather than an all-uppercase conversion.
Will this change the original cells?
No. The formula method returns results in another cell, and Flash Fill places results in its output range. To replace the source, copy the converted results and paste values over the original only after checking them.
Can I use PROPER in Excel for Mac?
Yes. The PROPER function works in Excel for Mac as well as Windows. Ribbon placement and shortcuts can differ; use the fill handle to copy the formula, or look for Flash Fill on the Data tab. On Windows, Ctrl+E runs Flash Fill.
Does PROPER work on numbers or mixed text?
It changes letters in text and leaves digits and most other characters as they are. For entries that combine words and numbers, inspect punctuation and the final result, since the function follows general character rules rather than understanding the meaning of the entry.
Can I convert only selected cells?
Yes. Put the formula beside the first selected entry and fill it down only through the rows you want to convert. For nonadjacent cells, convert each range separately or use Flash Fill on a clearly arranged output range, then check that unrelated rows were not included.
Related
- How to Add Weekly Dates in Excel
- Add a White Background and One Border in Excel
- How to Adjust Zoom Level in Excel
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Formula to Calculate Your Age from Birth DateIf the birth date is in cell A2, enter =DATEDIF(A2,TODAY(),"Y") to calculate age in completed years as of…
- Excel Payment Calculator: Calculate Loan Payments With PMTTo make an Excel payment calculator for a fixed-rate loan, use PMT: with the loan amount in B1,…
- How to Concatenate in Excel and Keep Cell FormattingTo concatenate values and keep dates, currency, percentages, or leading zeros readable, convert those values to formatted text…
- Excel Query Function: What to Use InsteadExcel does not have a worksheet function named QUERY like Google Sheets does. In Microsoft 365 for Windows,…
- Excel Top N Formula: Find the Highest Values and RowsTo return the top N numbers in Excel for Microsoft 365, use =LARGE(B2:B20,SEQUENCE(3)) to spill the three largest…
- Budget vs Actual Excel Formula: Examples and SetupIf budget is in B2 and actual spending or revenue is in C2, the basic budget-versus-actual formula is…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.