Standardize Names & Normalize Data in Excel: One True Format for Every Value

"JOHN SMITH", "John Smith", and "john smith" are the same person — but not to Excel. Standardization is the step where you force every value to follow one format so the same thing is always written the same way. This tutorial covers names and general data normalization.

Table of Contents

• Standardize Names
• Normalize Data

Standardize Names

What it is. Making every name in your data follow one format — same casing, same spacing, same order.

Why it is useful. Mixed formats split one person into many entries and break matching, mailing, and reports.

Syntax.

• PROPER(text) — capitalizes the first letter of each word.
• LOWER(text) / UPPER(text) — forces a single case.
• TRIM(text) — removes the spacing noise first.

Example. A2 = " jOHN smith " =TRIM(PROPER(A2)) → John Smith

Explanation. TRIM removes the extra spaces, PROPER fixes the casing, and you get one clean, consistent name.

Reordering "Smith, John". Use Data → Text to Columns (delimiter: comma) to split into Last and First, then rejoin with =C2 & " " & B2 to get "John Smith".

Let AI do it. Copilot: "Reformat the Name column to first-name last-name, title case."

Notes. PROPER isn't perfect: it capitalizes after apostrophes ("O'Brien") and can mangle words like "McDonald". For tricky names, use a mapping table instead.

Best practice. Decide one canonical format (for example "First Last") and convert the whole column to it. Never have two formats in one column.

Common mistake. Standardizing the case but leaving the spacing — always TRIM first, or "John Smith" and "John Smith" still won't match.

Normalize Data

What it is. Bringing all variations of the same thing to one standard value — the same city, product, or status written one way only.

Why it is useful. "NYC", "New York", and "new york" are one city, but grouping them is impossible until they all read the same.

Techniques.

• Unify casing with UPPER or PROPER.
• Replace variants with SUBSTITUTE: =SUBSTITUTE(A2,"NYC","New York").
• Mapping table + XLOOKUP for many variants at once — the reliable way.

Example. City column contains "NYC", "NY", "new york".

=XLOOKUP(UPPER(TRIM(A2)), D:D, E:E, TRIM(PROPER(A2)))

Explanation. UPPER + TRIM makes the lookup immune to casing and spaces. XLOOKUP finds the alias in the mapping table and returns the standard name. Anything it can't find falls back to a cleanly-cased value.

Best practice. Keep a master list of accepted values (your "source of truth") and point every formula at it. New variants get added to the table, never patched ad hoc.

Common mistake. Normalizing with UPPER alone. It unifies case but does nothing for "NYC" vs "New York" — variants survive and your data is still split.

Let AI do it. Copilot can propose a mapping for you: "List the different spellings of each city and suggest one standard name."