How to Use the PROPER Function to Capitalize Names in Excel

When you inherit a spreadsheet filled with data collected from a public web form, the text formatting is usually a disaster. If you have a column of customer names, you will inevitably see entries like “jOhn sMith,” “MARY JONES,” and “david brown.”

You cannot send a professional mail merge or generate a clean report with wildly inconsistent capitalization. However, manually retyping hundreds of names is a massive waste of time. Instead, you can instantly sanitize an entire column of text using Excel’s built-in PROPER function.

What the PROPER Function Does

The PROPER function applies “Title Case” to any string of text. It forces the very first letter of every word to be capitalized, and violently forces every subsequent letter in that word to be lowercase, regardless of how it was originally typed.

=PROPER(text)

How to Use PROPER on a List of Names

Imagine your messy, incorrectly capitalized customer names are sitting in Column A, starting in cell A2.

1. Click on cell B2 (an empty column directly next to your messy data).
2. Type the following formula:
   =PROPER(A2)
3. Press Enter.

If cell A2 contained “jOhn sMith”, cell B2 will instantly display the perfectly formatted “John Smith”.

Applying It to the Entire Column

You don’t need to type the formula for every single row.

1. Click back onto cell B2, where your newly formatted “John Smith” lives.
2. Look at the bottom-right corner of the cell’s green outline. You will see a tiny green square called the “Fill Handle.”
3. Double-click that tiny green square.

Excel will instantly copy the PROPER formula all the way down Column B, stopping exactly where your data ends in Column A. In two seconds, you have sanitized a list of ten thousand names.

Replacing the Old Data with the Clean Data

You now have a clean Column B, but it is entirely dependent on formulas looking at Column A. If you delete the messy Column A, your clean Column B will break and show a #REF! error. You must convert those formulas into permanent text.

1. Highlight all of your clean names in Column B.
2. Right-click the highlighted area and select Copy (or press Ctrl + C).
3. Without moving your mouse, right-click the exact same highlighted area again.
4. Under “Paste Options,” click the icon that looks like a clipboard with the numbers 123 (This is “Paste as Values”).

By pasting as values, you have deleted the underlying PROPER formulas and replaced them with the raw, hardcoded text. You can now safely delete the messy Column A, leaving you with a perfectly formatted spreadsheet.