Excel Tips: Use the PROPER Function to Capitalize Names in Excel

Lesson 21: Use the PROPER Function to Capitalize Names in Excel


Use the PROPER function to capitalize names in Excel

When you think about Excel functions, you probably think about performing calculations with numbers. While it's true that you can use functions to do lots of handy things with numbers in Excel, some functions can help you format text too. One good example is the PROPER function, which capitalizes the first letter of every word in a cell. If you have cells containing proper nouns, like names or titles, you can use the PROPER function to make sure everything is capitalized correctly. The PROPER function works in Google Sheets too.

For example, let's say your company wants to give someone a lifetime achievement award. You've asked your coworkers to enter their nominations for the award into this spreadsheet:

screenshot of Microsoft Excel

Unfortunately, you can see that not everyone has been careful to capitalize the first and last names of the people they want to nominate, so the spreadsheet looks messy. You could go through the column and correct the names manually, but using the PROPER function will be faster and easier.

In this example, the names of the nominees are in column A, so we'll put our formula in column B. In cell B2, we'll type a formula that tells Excel to capitalize the name in cell A2, which contains the first name on our list. The formula will look like this:


As you may remember from our Simple Formulas lesson in our Excel Formulas tutorial, it's important to make sure you start any Excel formula with an equals sign. Once you've entered the formula, press the Enter key, and cell B2 will display the name from A2 with the correct capitalization: Thomas Lynley.

screenshot of Microsoft Excel

Now all we have to do is click and drag the fill handle through cell A14, and column B will display all of the names in the list with the correct capitalization:

screenshot of Microsoft Excel

Great! Now all the names of the award nominees are correctly capitalized in the spreadsheet. There's one problem, though: We still have the original uncapitalized names in column A. We can't delete column A because our formula in column B refers to it. Instead, we can copy the values from column B into a new column by using the Paste Values feature in Excel.

To do this, select cells B2:B14 and click the Copy command (or press Ctrl+C on your keyboard). Then right-click the cell where you want to paste the values (C2, for example), followed by selecting the Values button from the menu that appears. If you're using Google Sheets, you can right-click and go to Paste special > Paste values only.

screenshot of Microsoft Excel

Now we have a column that displays the corrected names but that doesn't depend on a formula or cell reference. This means we can delete our original columns (column A and column B). There we have it: a nice, neat spreadsheet with all the names of the nominees correctly capitalized.