Changing Text Case

When working with text data, you will often notice that the same type of values are written in different cases. For example, one name might be written as john doe, another as JOHN DOE, and another as John Doe. Even though they represent the same value, this inconsistency makes the data look unstructured and harder to work with.

Such differences can also affect sorting, filtering, and presentation. That's why one of the important steps in data cleaning is standardizing text case.

Let's consider a simple example:

Here, all values represent names, but the formatting is inconsistent. Some are lowercase, some uppercase, and some mixed.

Text Case Functions

Excel provides three main functions to fix this:

UPPER → converts all text to uppercase;
LOWER → converts all text to lowercase;
PROPER → capitalizes the first letter of each word.

=UPPER(A2) → "JOHN DOE"
=LOWER(A2) → "john doe"
=PROPER(A2) → "John Doe"

In most real cases, PROPER is used for names, because it gives a clean and readable format.

Create a new column called Formatted Name.

Apply a formula to convert all values in the Full Name column into a consistent format, where each word starts with a capital letter.

Fill the formula down for all rows.

Use =PROPER(A2) to capitalize each word in the text and apply the formula to all rows.