If you’re tired of messy data with inconsistent capitalization, then you’re going to love Excel’s PROPER function. It’s an awesome, easy-to-use tool that cleans up text by capitalizing the first letter of every word while making the remaining letters lowercase. Whether you’re dealing with names, addresses, or anything else, the Proper Function in Excel can help keep your data squeaky clean and professional.

Check out the video below for a quick tutorial to follow along.

In this post, I’ll walk you through how to apply the PROPER function step-by-step and solve a few common problems that people run into while using it.

What is the PROPER Function in Excel?

The PROPER function in Excel is designed to capitalize the first letter of each word in a cell—perfect for cleaning up names, addresses, or just about any text that needs standard formatting.

Why Use the PROPER Function?

Here are some reasons you might want to use the PROPER function in Excel:

  • Fix inconsistent capitalization (e.g., all caps or all lowercase)
  • Standardize names, places, brands, and other text data
  • Improve the appearance of text-heavy data without manually editing each cell

The PROPER function simplifies this process instantly, allowing you to apply consistent formatting with minimal effort.

Step-by-Step Guide: How to Use the PROPER Function in Excel

Let’s go through an example where we’re cleaning up a list of names and other data that have been inconsistently capitalized.

Step 1: Select Your Target Cell

First, identify the cell where you want to apply the PROPER function. In this example, we’ll use cell C2, which contains a name with random capitalizations. Click on this target cell to select it.

Step 2: Insert the PROPER Function

You can insert the PROPER function either by typing it directly or using Excel’s function insertion tool.

  1. Go to the formula bar, click on the “fx” button to open the “Insert Function” dialog box.
  2. Search for “PROPER” and select it, then click OK.

At this point, a dialog box will pop up asking for the text input.

RELATED TOPIC: How to Use the IF Function in Excel

Step 3: Apply the Function to Your Data

Tell Excel where you want it to look. In our case, we’ll tell it to look at A2 (a column full of names with inconsistent capitalizations).

  1. In the function arguments box, for “Text,” type A2.
  2. Then, hit Enter.

Now, Excel will capitalize the first letter of each word and correct the remaining letters in C2.

Step 4: Copying the Formula Down

Once you’ve tested that the PROPER function in your Excel data works properly, drag the formula down or double-click the small square at the corner of your cell. This will apply the formula to multiple cells in the same column.

Step 5: Paste Values to Avoid Errors

Here’s the tricky part: if you try to directly copy and paste cells filled with formulas (like when you’re copying data from column C back into column A), you might run into errors like #REF!.

To avoid that:

  1. Right-click on cells in column C.
  2. Select Copy.
  3. Then, right-click on column A.
  4. Instead of “Paste,” select Paste Values Only.

This ensures you’re pasting the actual text values and not the formula referencing the original cells.

RELATED TOPIC: How to Use the VLOOKUP Function in Excel

Fixing Melissa Datasets with PROPER

Now let’s talk about something I call a “Melissa dataset.” It’s when you enter data really quickly, typing everything in lowercase. I do this all the time when I’m entering a lot of data. Then, I use the PROPER function in Excel afterward to fix all the capitalizations.

Here’s how to format a Melissa dataset:

  1. Select the cell where you want the corrected text.
  2. Insert the PROPER function from the dialog box or type =PROPER(E2) if your original text is in E2.
  3. Hit Enter to see Excel fix the text!

Afterward, remember to drag the formula down to cover all rows and use Paste Values Only to avoid any errors.

Dealing with All-Caps Data

Do you have data that’s in ALL CAPS? The PROPER function will also help with that.

For example, in the tutorial, I applied the PROPER function in Excel to a series of names that were all typed in uppercase. Excel quickly changed the text so that the first letters of each word were capitalized, while the rest became lowercase.

Just apply the same steps:

  1. Select the target cell (L2, for example).
  2. Use the Insert Function dialog, select the PROPER function, and point it at your all-caps cell.
  3. Hit Enter, and Excel will fix the text.

Again, always remember to Paste Values Only when copying to avoid formula issues later.

Fixing Addresses with PROPER in Excel

The PROPER function in Excel isn’t just for names—it works great for addresses, too!

For example, if you’ve got an address like “2225 your street, your town, california,” the PROPER function will correct it to “2225 Your Street, Your Town, California.”

Simply point the function to the cell with the address, and Excel will handle the rest, making your address look clean and standardized.

RELATED TOPIC: How to Use the IFERROR Function in Excel

Key Tips

Here are a few additional tips to make sure you’re getting the most out of the PROPER function.

  • Always Paste Values: When you’re finished, paste the values instead of formulas. This prevents errors and keeps your data clean.
  • Double-Check Formulas: If you’re working with large datasets and formulas, always double-check everything before pasting. An unnoticed formula error could cause problems down the line.
  • Start with Proper Formatting: Make sure your data is as consistent as possible before you begin applying the PROPER function.

Conclusion

The PROPER function in Excel is a useful tool for fixing messy and inconsistent text. It saves time and effort by automatically capitalizing the first letter of each word in a cell. However, make sure to use “Paste Values Only” to avoid formula errors down the road.

Whether you’re cleaning up names, addresses, or any other text-heavy data, this function can keep your Excel sheets neat and professional.

Want to learn more? Be sure to subscribe to the channel and stay tuned for even more Excel tips and tricks!

Get Started with Microsoft 365

Be sure to subscribe to my YouTube Channel for more Microsoft Excel and Microsoft 365 Tutorials.

This page may contain affiliate links. These are tools I personally use and love. I may receive a small commission should you make a purchase using one of these links. This helps keep the tutorials and website posts coming and up to date. Thank you for your support! For more information please see my full disclaimer.

RELATED ARTICLESMORE FROM AUTHOR