PROPER Function to Capitalize Names in Excel

When you think about Excel functions, you probably think about performing calculations with numbers. But 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 thePROPER 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 Spreadsheets, too.

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

Unfortunately, you can see that not everyone has been careful to capitalize the first and last name of the person they want to nominate, so the spreadsheet looks messy. You could go through the column and correct all 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. That formula will look like this:

=PROPER(A2)

As you may remember from our lesson on Simple Formulas, it’s important to make sure that 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.

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

Great! Now all the names of the award nominees are correctly capitalized on the spreadsheet. There’s one problem, though; we still have the original uncapitalized names in column A. We can’t just delete column A, since 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 (for example, C2) and then select the Values button from the menu that appears. If you’re using Google Spreadsheets, you can right-click and go to Paste special > Paste values only.

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

Of course, people’s names aren’t the only things that need to be capitalized! You can use the PROPER function to correctly capitalize lists of books, movies, countries, or even street names. If you have another idea for how to use the PROPER function, let us know in the comments below!