How to Find and Fix Typos in an Excel Column (Cities, Names, Categories)

Free-typed columns such as city, country, job title or category drift over time: Boston, Bostn, boston, BOSTON, Boston MA. Filters and pivot tables then treat each spelling as a different thing. The fix has two parts, finding the variants and replacing them safely.

Step 1: see every distinct value with its count

Select your data and insert a PivotTable. Drag the column into Rows and again into Values (set to Count). Sort the counts from high to low. The popular spellings are at the top; the typos are the small counts that look almost like a big one further down. For a few hundred values, scan the list with the counts beside it.

Step 2: normalise case and spaces first

Half the variants are only capitalisation or stray spaces. Remove those before looking for real typos:

=PROPER(TRIM(A2))

PROPER capitalises the first letter of each word. It is fine for cities and names, but it will turn "USA" into "Usa" and "McDonald" into "Mcdonald", so check the result on abbreviations. See standardising names in Excel for the exceptions.

Step 3: build a replacement table

On a second sheet make two columns: Wrong and Right. Fill it from your pivot list:

Then in a new column look each value up, keeping the original if there is no correction:

=IFERROR(VLOOKUP(A2,Fixes!$A$2:$B$100,2,FALSE),A2)

The original is kept whenever the value is not in your table, so nothing changes unless you decided it should. If the lookup misbehaves on values that look identical, see fixing VLOOKUP.

Step 4: let Excel suggest near-matches

On Windows, Microsoft's free Fuzzy Lookup add-in for Excel can match each value in one list to the closest value in another and shows a similarity score. Use it to compare your column against a clean reference list of cities or categories. Treat the score as a suggestion: review matches below roughly 0.9 yourself.

What is safe to fix automatically

• A keystroke slip where the value is one swap, drop or double letter away from one common spelling (Bostn, Bosotn) and nothing else is that close.
• Case and spacing differences.
• Your own known abbreviations, listed in the replacement table.

Anything that could be a different real value should be reviewed by a person, with the original kept.

A whole file at once

DecodingPro compares each value in a column with the spellings that column already uses. It corrects a keystroke typo only when one common spelling is the clear match, leaves look-alike real values untouched, flags the rest with the possible answers, and logs every change. In our internal benchmark about 90% of keystroke typos were corrected automatically, the rest flagged, and real look-alike values such as Milano next to Milan were left alone.

Questions people ask