Continue to WhatsApp?

The chat opens in WhatsApp, in the app or in a new tab. You leave our website, and data such as your IP address is sent to WhatsApp (Meta).

EXCEL AND LISTS

Find duplicates in Excel: spot duplicate customer records and merge them safely

A customer calls to ask why she received two payment reminders for the same invoice. Another customer moved long ago, but his old address lives on in a second record. You suspect the customer list is not clean. And you know that one careless click on Remove Duplicates can destroy more than it tidies up.

Wooden drawers of an old card index with labelled handles, a picture of customer records and their duplicates
Photo: Ilya Semenov on Unsplash

Duplicate customer data rarely costs a lot in one go. It costs follow up questions, postage, awkward phone calls and sales reports nobody fully trusts any more. On top of that, Excel's standard tools only recognise exact duplicates. They cannot see that “Müller Haustechnik GmbH” and “Mueller Haustechnik Ges.m.b.H.” are the same customer.

This guide shows step by step how to find duplicates in Excel, even when spellings differ, and how to merge them without losing data.

The short answer: how do I find duplicate records in Excel?

A duplicate is a record that describes the same real person or company a second time. Exact duplicates are identical apart from upper and lower case, and Excel finds them with built-in tools. Similar duplicates differ in spelling, spaces or legal form. For those you need a cleaned match key or fuzzy matching.

  1. Make a copy

    Work in a copy of the customer list, so the original stays untouched if something goes wrong.

  2. Flag exact duplicates

    Check customer number, VAT ID and email with COUNTIF. Anything above 1 is a duplicate.

  3. Build a match key

    Combine company name and postcode into a cleaned key without spaces, umlauts and legal form.

  4. Review similar cases

    Check matches on the key and from fuzzy matching by hand. Not every match is a duplicate.

  5. Merge, do not delete

    Choose a leading record for each case, carry over missing details and set the other record to inactive.

How do I highlight duplicates in Excel without deleting them?

The quickest way is conditional formatting. Select the column, for example the email addresses, and choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Excel colours every value that appears more than once. Nothing is deleted. You see the size of the problem before you change anything.

For filtering and sorting, a helper column is better. In the examples in this article, the customer number is in column A, the company in B, the postcode in C and the email in D, each starting in row 2. In a free column, enter =COUNTIF($D$2:$D$5000,D2) and fill it down. The number shows how often the address appears, and anything above 1 is a duplicate. Then sort by email so the duplicates sit together.

Worth knowing: COUNTIF does not distinguish between upper and lower case, so “Info@…” and “info@…” count as the same. A trailing space, however, turns the same address into a different value. Microsoft recommends removing such spaces first with TRIM or CLEAN.

How do I find duplicates across multiple columns?

Often only the combination is duplicated, for example company and postcode. COUNTIFS takes one condition per column: =COUNTIFS($B$2:$B$5000,B2,$C$2:$C$5000,C2). In Microsoft 365, Excel 2021 and Excel 2024 you can also use UNIQUE: =UNIQUE(B2:C5000,,TRUE) uses TRUE as the third argument to list every combination that appears exactly once. Anything missing from that list appears at least twice.

Finding similar customer records: the match key

Real customer duplicates are rarely identical. “Müller Haustechnik GmbH”, “Mueller Haustechnik GmbH” and “Müller Haustechnik GmbH ” with a trailing space are three different customers to Excel. Case is not the issue, Excel ignores it when counting. Umlauts, spaces and legal forms are, and exactly these cases slip through an exact comparison. The answer is a match key: a helper column that reduces the name to its core and joins it with the postcode.

A simple version, with the company in column B and the postcode in column C: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LOWER(B2),"ü","ue"),"ä","ae")," gmbh","")," ","")&"|"&C2. The formula lowercases everything, replaces ü and ä, removes the legal form and finally all spaces. LOWER sits innermost because SUBSTITUTE is case sensitive. Without it, “GmbH” would not be caught.

Extend the formula as needed for ö, ß, full stops and other legal forms such as Ges.m.b.H., e.U., KG or UG. Replace legal forms before you strip full stops and spaces, and write them with a leading space, so " kg" rather than "kg". Otherwise the same letters also disappear from the middle of a name such as “Backgarten”. Then count duplicates on the key column with COUNTIF as above.

  • VAT ID (UID-Nummer in Austria, USt-IdNr. in Germany): the strongest key for business customers. Austrian numbers consist of ATU plus eight digits, German ones of DE plus nine digits. Remove spaces before comparing.
  • Email address: good for private customers. Compare without spaces, and in lower case when you build the address into a key.
  • Phone number: only useful once you standardise prefix and format, treating +43 and 0043 the same.
  • Name and postcode: useful as a supplement, but branches of the same company in one town cause false matches.

Power Query: fuzzy matching also catches typos

Even the best match key does not catch typos such as “Haustechnick”. For that, Power Query in Excel for Microsoft 365 offers fuzzy matching, a comparison that pairs similar rather than identical text. It only works on text columns and ignores case by default. Here is how:

  1. Load the list

    Click into the table, choose Data > From Table/Range and save the query with Close & Load To > Only Create Connection.

  2. Merge it with itself

    Choose Data > Get Data > Combine Queries > Merge and select the same customer list on top and below. Select the company name column in both.

  3. Switch on fuzzy matching

    In the same dialog, tick Use fuzzy matching to perform the merge. The similarity threshold defaults to 0.8. Lower values find more, but also more false matches.

  4. Review the result

    Expand customer number and postcode of the matched partners. Remove rows with the same customer number on both sides, because there a customer only matches itself. Each real pair then appears twice, once in each direction. The rest is your checklist.

Start at 0.8 and lower it in small steps, checking a sample each time. The article on how to compare two Excel lists shows the same technique for merging two different lists.

An example from the back office

How the match key brings spellings together
Company in the systemPostcodeMatch keyFinding
Müller Haustechnik GmbH4020muellerhaustechnik|4020leading record
MUELLER HAUSTECHNIK GMBH4020muellerhaustechnik|4020duplicate, merge
Müller Haustechnik GmbH4600muellerhaustechnik|4600branch, not a duplicate
Mueller Haustechnick GmbH4020muellerhaustechnick|4020only found by fuzzy matching

How do I merge duplicates instead of deleting them?

Data > Remove Duplicates keeps the first occurrence and deletes the others. With customer data, the first row is often the oldest. The new address, the current contact or open items may belong to the second record, and exactly those details would then be gone. Our duplicate finder also keeps the first entry of each group in its cleaned list. For merging, use the full list with markers instead, which shows every group without removing anything. A safer approach loses nothing and keeps every step traceable:

  1. Choose the leading record: usually the one with open items, running orders or the latest purchase.
  2. Carry over details: copy missing or newer data from the duplicate, such as email, phone or contact person.
  3. Set the duplicate to inactive: do not delete it. Block it and add a note “replaced by customer number …”, so old documents stay traceable.
  4. Involve accounting: if the customer also exists twice as a debtor in BMD or DATEV, agree the merge with your accounting team.
  5. Fix the cause: find out where duplicates come from, such as guest checkouts in the web shop or new records created without a search.

Data protection is another reason for clean customer data. Article 5 of the GDPR requires personal data to be accurate and, where necessary, kept up to date. Access under Article 15 and erasure under Article 17 also cover all data on a person, including what sits in a duplicate record. With a clean list, you do not miss any of it.

Administration employee on the phone at her desk between two screens, clarifying customer details
Photo: Centre for Ageing Better on Unsplash

Common mistakes when looking for duplicates

  • Deleting straight away: Remove Duplicates works without asking row by row. Highlight and review first, delete afterwards, and only in a copy.
  • Comparing exactly only: COUNTIF, the Duplicate Values rule and Remove Duplicates ignore upper and lower case, but not spaces, umlauts or legal forms. Without a match key most customer duplicates stay invisible.
  • Comparing the whole row: Remove Duplicates across all columns only finds rows that match in every field. One new phone number is enough for a duplicate to survive.
  • Overlooking formats: Remove Duplicates compares what the cell displays. The same date, formatted once as 08.03.2026 and once as 8 March 2026, counts as different.
  • Threshold too low: fuzzy matching at 0.5 finds a lot, possibly pairing “Huber Bau” with “Auer Bau”. Lower the value only step by step.
  • Clean once and forget: without a rule for new records, the duplicates are back within a year.

Limits, effort and when an office helper helps

A one-off clean up takes an afternoon in Excel. What costs money is repetition and what duplicates cause: double reminders, post to old addresses, wrong sales figures per customer. A cautious estimate: one clean up per quarter at 4 hours each is 16 hours a year. At an assumed internal cost of €38 per hour, meaning wages plus overheads, as preset in the cost calculator, that is €608, before the knock-on costs. Run your own numbers with the office work cost calculator.

For the clean up itself, use the free duplicate finder. If new customers regularly come from a second source, such as a web shop list, our free Excel comparison checks the new list against your existing records. It does not guess with duplicate keys but flags them as unresolved. To see which customers are not in your records at all, read finding missing records.

An office helper, a small program we set up for exactly this task, makes sense when new customer data keeps arriving from forms, emails or the web shop. It checks each new record for possible duplicates and passes suspected cases to a person to decide. Records are only merged after approval. It works with the programs you already use, so nobody has to learn new software. The list stays clean without anyone giving up an afternoon for it every quarter. Whether a formula, a macro or an office helper suits your case is covered in automating Excel: formula, macro or office helper. The page on automating Excel work gives the overview.

Whether an office helper pays off for your customer data depends on how often new records come in and from how many sources. Describe that in the free workflow check and you get a personal, free assessment within 1–2 working days. Small projects are done 3–7 days after the workflow is agreed, at a fixed price from €1,990 excl. VAT.

Sources and further reading

FAQ

Quick answers.

How do I find duplicates in Excel without deleting them?

Use conditional formatting: select the column and choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Excel colours every duplicate value and deletes nothing. For filtering, add a helper column with COUNTIF as well, which shows how often each value appears in the column.

How do I find duplicate rows across multiple columns?

Use COUNTIFS with one condition per column, for example =COUNTIFS($B$2:$B$5000,B2,$C$2:$C$5000,C2) for company and postcode. Any value above 1 marks a duplicate combination. In Microsoft 365, Excel 2021 and Excel 2024, UNIQUE with TRUE as the third argument lists every combination that appears exactly once.

How do I find similar entries with different spellings?

Build a match key in a helper column. It standardises case, umlauts, spaces and legal forms, and you then count duplicates on that key. For typos, use fuzzy matching in Power Query, available in Excel for Microsoft 365. Check every match by hand before you merge anything.

Is Excel's duplicate check case sensitive?

No. COUNTIF, the Duplicate Values rule and Remove Duplicates treat “Müller” and “MÜLLER” as the same. Spaces, umlauts and punctuation do count, though: “Müller” and “Mueller” are different values to Excel. For a case sensitive count, use =SUMPRODUCT(--EXACT($D$2:$D$5000,D2)).

Which row does Excel keep when removing duplicates?

Excel keeps the first occurrence and deletes all other rows with the same values in the selected columns. It compares the displayed value. With customer data, the first row is often the oldest. It is better to merge duplicates deliberately and set the surplus record to inactive.

Why are duplicate customer records a data protection issue?

Because Article 5 of the GDPR requires personal data to be accurate and, where necessary, kept up to date. A duplicate with an old address contradicts that. Access under Article 15 and erasure under Article 17 also cover all data on a person, including what sits in a duplicate record.

LET'S TALK ABOUT YOUR WORKFLOW

Write to us. We reply personally.

A question, an idea or a task that costs time every week? A few sentences are enough. You get an honest answer from the people who also build your office helper.

Ivan, Lukas & JanReply within 1–2 working days

  • Personal reply
  • No obligation
  • Based in Vienna, GDPR aware
Photo of Ivan
Rather talk directly?Ivan, your contact person+43 676 59 40 027office@pebblebyte.comMon to Fri 08:00–18:00
Have a specific task? Get your free workflow check

Your message

Please do not enter passwords or confidential customer data. We only use your details to handle your request. No newsletter, no sharing for advertising. Details in our privacy notice.