How to Find Similar Names in Excel with Fuzzy Matching

To find names that are almost—but not exactly—the same in Excel, first clean the text, then use Power Query’s fuzzy matching option to compare the names and review likely matches. For a quick check of exact repeats, use conditional formatting instead. Start with a copy of your workbook and a single column of names; fuzzy matches are suggestions to verify, not proof that two records refer to the same person.

What You’ll Need

Excel for Microsoft 365 on Windows, with a table or column containing the names you want to compare.
A unique record ID for each row if you are comparing a list against itself. This makes it possible to remove self-matches and identify the original records.
A saved copy of the workbook. Keep your original names unchanged and do cleanup in a helper column or Power Query.

These steps focus on names in one workbook. Power Query fuzzy matching is also useful when checking one list against another, such as a new sign-up list against an existing customer list. On Mac or Excel for the web, Power Query features can differ; if the fuzzy matching option is missing, use Excel for Microsoft 365 on Windows for that part.

Step-by-Step

Make a working copy and check the source data. Save the workbook under a new name before changing anything. Put one name in each row, with a clear header such as Name. Remove completely blank rows from the working range, and check that names have not been split across columns. If you have a record number, keep it in a separate column; two people can share a name, so the name itself should not be used as a unique identifier.

Turn the range into an Excel table by selecting a cell in it and choosing Insert > Table. Confirm that My table has headers is selected. Tables make it easier to send a consistent range into Power Query and include new rows later. Give the table a useful name from Table Design > Table Name, such as Names.
Clean common differences before comparing. Extra spaces, inconsistent capitalization, and stray punctuation can make the same name look different. Keep the source name in column A and add a helper column called CleanName. With names in A2:A100 and the formula entered in B2, use:

=IF(A2="","",LOWER(TRIM(CLEAN(SUBSTITUTE(SUBSTITUTE(A2,".",""),",","")))))

Fill the formula down. For example, J. Smith becomes j smith, and extra ordinary spaces at the beginning or end are removed. CLEAN removes certain nonprinting characters, while TRIM reduces repeated ordinary spaces. This formula removes periods and commas; it deliberately does not remove hyphens or apostrophes, which can be meaningful in names. Adjust the cleanup only when it suits your data.

Excel’s TRIM does not remove every unusual space, including some nonbreaking spaces copied from websites. If those remain, use Find and Replace to replace the troublesome character with a regular space, or correct those few records manually. Always compare the cleaned value with the original before treating a match as reliable.
Mark exact repeats first. This gives you an easy baseline and separates exact duplicates from approximate matches. Select the cleaned-name cells, then choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Pick a format and select OK. Excel highlights identical cleaned values, including differences in capitalization or the punctuation removed by your helper formula.

For example, if J. Smith and j smith both clean to j smith, they will be highlighted as duplicates. This rule does not find a typo such as Jon Smith versus John Smith. Do not delete highlighted rows automatically: repeated names may belong to different people, and a duplicate could be a valid transaction or contact record.
Load the list into Power Query. Click in the Excel table and choose Data > From Table/Range. The Power Query Editor opens. Check that the name and ID columns have sensible headers and that the name column is set to the Text data type. If you made a CleanName helper column, use it for matching; otherwise, use the original name column and expect punctuation and spacing to affect results.

For a list you are comparing with itself, make a second query from the same table: in the Queries pane, right-click the query and choose Duplicate. Give the two queries distinct names, such as NamesLeft and NamesRight. Retain the record ID in both. For two different lists, load each list as its own query instead. The left table is the list you want to check, and the right table supplies possible matches.
Set up a fuzzy merge. In Power Query Editor, select the left query, then choose Home > Merge Queries > Merge Queries as New. In the merge window, select the right query. Click the name column in the top preview and the corresponding name column in the lower preview. If matching using a cleaned helper, select that column on both sides. Choose a join kind that keeps the records you need; Left Outer is a practical choice when you want every left-side record, including those without a match.

Select Use fuzzy matching to perform the merge. Open the fuzzy matching options if you need to adjust them. The similarity threshold controls how close two text values must be before Power Query treats them as a possible match; the default is a starting point, not a guarantee. A higher threshold is stricter and usually returns fewer candidates. A lower threshold can uncover more spelling variations but may also bring in unrelated names. You can also limit the maximum number of matches returned for each row.

For instance, Jon Smyth may be suggested as a match for John Smith, depending on the threshold and the rest of the text. The score or candidate is not a confidence that the people are the same. Names with common words or short spellings can produce misleading suggestions, so keep other identifying information available for review.
Expand results and remove self-matches. The merged query includes a column containing matching rows. Select its expand button, choose the right-side name and ID fields you want to inspect, and clear the option to use the original column name as a prefix if you prefer shorter headers. Keep both IDs in the result so you can tell which records are being compared.

When a table is merged with a duplicate of itself, each row can match itself. Filter out rows where the left ID equals the right ID. The same pair can also appear twice in reverse order—for example, ID 12 matched to ID 18 and ID 18 matched to ID 12. If your IDs are numeric, keep only rows where the left ID is less than the right ID to retain one direction. If IDs are not ordered numbers, create a consistent pair key or remove mirrored pairs after loading. Do not remove records merely because their names match.
Review candidates in context and load the results. Sort or filter the results so you can inspect each candidate pair. Compare additional fields such as email address, phone number, location, or an existing customer ID where appropriate. Mark each pair as confirmed, rejected, or needing more information in a separate review column. Avoid merging records based only on a similar name.

When the output looks right, choose Home > Close & Load to return the results to Excel. Keep the query output separate from the source table. If you later add records, refresh the query with Data > Refresh All, then review newly returned candidates again. A refresh updates the results; it does not confirm matches for you.

Common Mistakes

Treating a fuzzy match as a confirmed duplicate. A similar name is only a lead for investigation. Two people can have identical names, while one person can appear under a nickname, changed surname, or typo. Check another identifier before changing or combining records.

Cleaning away meaningful characters. Removing every apostrophe, hyphen, or space may make some names easier to compare but can also make distinct names look alike. Apply the same cleanup rules to both lists, preserve the original text, and remove only characters that are genuinely irrelevant for your records.

Choosing a threshold without reviewing examples. A low threshold may flood the output with weak candidates; a high one may miss useful variations. Test a few known examples and inspect both good matches and false positives before using the results for a full list.

Forgetting that self-merges create extra rows. A record can match itself, and pairs can appear in both directions. Keep IDs in the merge and filter those cases deliberately rather than deleting rows based on the names alone.

Expecting a formula or duplicate rule to do fuzzy matching. Conditional formatting finds exact repeated text, and simple formulas can standardize text. Neither one provides Power Query’s approximate text merge. Use the method suited to the kind of similarity you need.

Tips

Keep a clean, unmodified source column alongside the helper column or query output. This makes every suggested change traceable.
Start by comparing a small sample that includes known variations, such as a missing letter, a nickname, or different punctuation. Use it to judge whether your cleanup and threshold are useful.
When comparing two lists, standardize both sides in the same way. Cleaning only one list can reduce the number of useful matches.
Use a unique ID to track records through the merge. Names are not reliable keys, even when they look distinctive.
Keep a review status and a notes column in the final output. This lets another person see which pairs were confirmed and why.
If you only need to find exact duplicates, use the conditional formatting method rather than setting up a fuzzy merge. It is faster and less likely to return ambiguous candidates.

Troubleshooting

No fuzzy matching checkbox appears: Confirm that you are in the Power Query merge dialog and have selected text columns on both sides. Power Query features differ by Excel version and platform. If the option is unavailable in your installation, try Excel for Microsoft 365 on Windows; Excel for the web and some Mac builds may not expose the same merge options.

The merge returns no candidates: Check that both selected columns contain text and that you selected the intended fields. Make sure the names are not blank, and apply identical cleanup to both lists. If you are confident the candidates are close, lower the similarity threshold slightly and inspect the extra results before relying on them.

There are too many unrelated results: Raise the threshold, reduce the maximum matches per row, or make the cleanup more consistent. Then compare a sample of the returned pairs. If names are short or common, use other fields to narrow the review rather than expecting name similarity alone to decide identity.

Duplicates remain after cleaning: Look for nonbreaking spaces, unusual punctuation, middle initials, suffixes, or spelling differences that your chosen cleanup does not address. Correct a few exceptional cases manually or add a carefully tested transformation. Do not broaden the cleanup until you know it will not erase meaningful distinctions.

New rows do not appear in the output: Make sure the source is an Excel table and that the new rows were added inside it. Then select Data > Refresh All. If the query uses a fixed range instead of a table, update its source before refreshing.

Frequently Asked Questions

Can Excel find similar names without an add-in?

Yes. Power Query in supported Excel desktop versions includes fuzzy matching for merges. You can use it to compare one list with another or a list against a duplicate of itself. Availability of the feature can vary by platform and version.

Does conditional formatting find misspelled names?

No. The Duplicate Values rule highlights exact repeated cell values. A helper column can normalize selected differences such as case, extra spaces, periods, and commas, but it will not identify arbitrary spelling variations. Use a fuzzy merge to generate approximate candidates.

What similarity threshold should I use?

There is no universal best threshold. Begin with the default, test known examples, and adjust according to how many false positives and missed candidates you can review. A stricter setting returns fewer, closer suggestions; a looser setting returns more possibilities that need checking.

Can fuzzy matching tell me whether two names belong to the same person?

No. It compares text and returns likely matches; it cannot establish identity. Confirm candidates using reliable additional fields and your organization’s review rules before combining or deleting records.

Will the cleanup formula change my original names?

No. The formula creates a separate cleaned value in the helper column. Keep the source column intact so you can inspect and restore the original spelling, punctuation, and capitalization.

Can I compare two separate name lists?

Yes. Load both tables into Power Query, then use Home > Merge Queries > Merge Queries as New and select the matching name columns. Turn on fuzzy matching and review the returned candidates just as you would for a self-merge.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.