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. This tutorial makes every value follow one consistent format, using an AI prompt or Excel's own functions.

Table of Contents

• Standardize Names
• Normalize Data

Standardize Names

What it is. Reformatting every name to one consistent style — same casing, spacing, and order.

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

Do it with AI

The prompts.

• "Reformat the Name column to 'First Last', title case."
• For reversed names: "Convert 'Smith, John' to 'John Smith'."

Both rows become "John Smith".

Do it manually in Excel

• Casing + spacing: =TRIM(PROPER(A2)) — TRIM clears spaces, PROPER capitalizes each word.
• Force one case: =LOWER(A2) or =UPPER(A2).
• Reorder "Smith, John": Data → Text to Columns (delimiter: comma) to split into Last and First, then rejoin with =C2 & " " & B2.

Verify

Ask Copilot "Show me every name you changed" and check apostrophes ("O'Brien"), "Mc" names, and double-barreled names. Manually, scan the column for any two formats left behind.

Common mistake. Fixing casing but not the order — "John Smith" and "Smith, John" still won't match. Fix both.

Best practice. Decide one canonical format first ("First Last") and convert the whole column to it — never keep two formats in one column.

Normalize Data

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

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

Do it with AI

The prompts.

1. Map first: "List all the different spellings of each city and suggest one standard name for each."
2. Apply: "Replace every city name with its standard name."

Every row becomes "New York".

Do it manually in Excel

• Replace variants: =SUBSTITUTE(A2,"NYC","New York") for one alias at a time.
• Mapping table + XLOOKUP (the reliable way for many variants):

=XLOOKUP(UPPER(TRIM(A2)), D:D, E:E, TRIM(PROPER(A2))) — maps each alias to its standard, and cleanly-cases anything it can't find.

Verify

Ask Copilot "Show me the mapping you used" — this is where errors hide. Manually, check that "Lincoln" the city and "Lincoln" the car were NOT merged.

Common mistake. Trusting the AI's grouping blindly. Two genuinely different entries must not be merged — always review the mapping.

Best practice. Keep a master list of accepted values (your source of truth) and point every formula or prompt at it.