How to Merge Excel Files and Remove Duplicate Rows (Without Losing Data)

Duplicate rows are the quietest problem in a merge. They do not cause an error. They do not break the file. They just sit there, looking like real data, and they make every total too high. A customer counted twice. An order counted twice. A survey response counted twice. If you merge files without thinking about duplicates, you will have them. This guide explains where duplicates come from, how to remove them safely, and how to tell the difference between a row that is a duplicate and a row that only looks like one.

The key idea is simple: a duplicate is a row that repeats information you already have. But "repeats" can mean three different things, and each one needs a different fix. A row that is identical in every column is a true duplicate. A row that shares an ID but has different values is not a duplicate — it is the same thing described twice, and it needs Merge by ID. A row that shares a key but differs in other columns is a judgment call, and the audit report is how you make it.

Where duplicate rows come from

Duplicates are not a sign that someone did something wrong. They are a normal result of how files are made.

Overlapping exports. Two survey batches cover overlapping dates, so the responses from the overlap appear in both files. Two branch reports both include a head-office order. Two supplier lists both include the same products. Whenever two files cover the same ground, the shared rows appear twice in the merged file.

Re-sent files. A colleague sends a corrected version of a report, and you merge both the original and the correction. Every row in the original appears twice — once wrong, once right — and a simple deduplication that removes identical rows will not help, because the corrected rows are not identical to the originals. They share an ID, but their values differ. That is a Merge by ID job, not a deduplication job.

Repeated exports of the same data. A system exports the same week twice, or a download is run twice by accident. The files are identical, or nearly so, and every row is duplicated.

Data entry. The same customer typed in twice with a slightly different name, or the same order entered by two people. These are not identical rows, and no automatic deduplication will catch them reliably. They need a human to look at them — which is why the audit report matters.

Deduplication: whole row or key columns?

In Append rows mode, MergeExcel offers one-click deduplication with a choice: remove rows that are identical in every column, or remove rows that repeat a key you name. The choice changes the result, so it is worth understanding.

Whole-row dedupe (key "all"). Two rows are duplicates only if every value matches: same ID, same name, same date, same amount, same everything. This is the safe choice. It never removes a row that contains different information, because a row with different information is not identical. Use it when the files overlap exactly — the same export twice, or two batches that share rows word for word.

Key-column dedupe. You name the columns that identify a row — for example, "Customer ID, Order Date" — and rows that repeat the same key are treated as duplicates, even if other columns differ. The first row with a given key is kept; later rows with the same key are removed. This is stronger, and it is also riskier: if two different orders share a customer and a date, the second one will be removed, and that is data loss. Only use key dedupe when the key truly identifies a row — when two rows with the same key cannot be two different real things.

A good test for a key: ask whether two rows with the same key could ever be different real events. "Customer ID, Order Date" fails the test if a customer can place two orders on the same day. "Order ID" passes the test if every order has a unique ID. "Customer ID" on its own fails the test if a customer can appear more than once — which is exactly the case Merge by ID is for.

Merge by ID: when duplicates are not duplicates

Sometimes the rows that share an ID are not duplicates at all. Two survey batches contain the same customer, but one has the customer's phone number and the other has the customer's order history. Two supplier lists contain the same product, but one has the price and the other has the stock level. Stacking these rows gives you the customer twice, with half the information in each row. Deduplicating them throws away half the information. Neither is right.

Merge by ID is the third option. Rows with the same ID are folded into one row, and every distinct value is kept, joined with " | ". The customer appears once, with the phone number from one file and the order history from the other. If the two files disagree — two different phone numbers — both are kept, and the audit report counts the cells where that happened. Nothing is silently overwritten, and nothing is silently dropped.

Use Merge by ID when the same ID appears in more than one file and the rows carry different information. Use deduplication when the rows carry the same information. If you are not sure which one applies, run the merge both ways on a copy of the files and compare the row counts: deduplication removes rows, Merge by ID folds them, and the difference tells you how much information was at stake.

Our guide to merging Excel workbooks without losing data explains the same distinction from the data-loss side, with a checklist for preparing files so neither mode can hurt you.

How to remove duplicates step by step

Step 1: Upload the files to MergeExcel. Drag them onto the upload area, or click to choose. Check the file list. If a file is a corrected version of another file, remember that — it changes which mode you need.

Step 2: Pick Append rows. Deduplication is an Append rows option. If your files need Merge by ID instead, pick that mode — it has no dedupe option, because folding rows by ID is its own way of handling repeats.

Step 3: Turn on "Remove duplicate rows". Choose the key. Start with "all" unless you have a specific reason to use key columns. If you do use key columns, type the exact header names, separated by commas — "Customer ID, Order Date" — and check the spelling against the headers in your files.

Step 4: Merge and read the report. The report tells you how many duplicates were removed. That number is the first thing to check. If it is zero and you expected duplicates, the key was wrong, or the rows differ in a column you did not expect — a trailing space, a different date format, a value stored as text in one file and as a number in another. If the number is much larger than you expected, the key was too broad, and real rows were removed.

Step 5: Download and verify. Open the merged workbook. Count the rows. Find a row you know was duplicated and confirm it appears once. Find a row you know was unique and confirm it is still there. Sum one numeric column and compare it with the sum across the source files, minus the duplicates you removed. If you removed duplicates by key, check specifically for rows that shared the key but were different real events — those are the ones a too-broad key would have deleted.

Why duplicates survive: the near-duplicate problem

Automatic deduplication only catches rows that match exactly — or that match on the key you named. It does not catch near-duplicates: the same customer as "Jon Smith" in one file and "John Smith" in another, the same date as "2026-10-03" in one file and "10/03/2026" in another, the same amount as 100 in one file and 100.00 in another (which is the same number, but stored differently), or the same row with a trailing space in one value.

These are the hardest duplicates, and no merge tool can catch all of them reliably, because "similar" is a judgment, not a rule. What you can do is reduce them before you merge:

• Standardise names. Pick one spelling for each customer, product or place, and use it in every file. A list of standard names — even a simple one, kept in a spreadsheet — prevents most near-duplicates.
• Standardise dates. Use one format everywhere, preferably YYYY-MM-DD, which sorts correctly and is unambiguous. The U.S. Census Bureau publishes its data with consistent date formats for the same reason.
• Trim spaces. A value with a trailing space — "Smith " instead of "Smith" — is a different value to a tool, even though it looks the same to you. In Excel, the TRIM function removes leading and trailing spaces. Run it on the key columns before you merge.
• Make types match. A number in one file and text in another will not match as a duplicate, even when the digits are the same. Convert text numbers to real numbers before you merge.

After merging, the audit report helps you find the near-duplicates that survived: sort the merged table by the ID or the name, and look for rows that sit next to each other and look alike. A few minutes of looking catches what no rule can.

How many duplicates are normal?

It depends on the files. Two batches from the same survey with a week of overlap might share 5% of their rows. Two exports of the same data run by mistake share 100%. A folder of branch reports that were compiled independently might share almost none.

What matters is not the number but whether it matches your expectation. Before you merge, guess: how many rows do you think are duplicated? Then read the report. If the number is close to your guess, the merge behaved as expected. If it is far off — zero when you expected hundreds, or thousands when you expected a few — stop and look. Either the key was wrong, the files were not what you thought, or the duplicates are near-duplicates that exact matching cannot catch. The National Center for Education Statistics documents duplicate rates in its survey data releases for the same reason: a duplicate count is a check on the data, not just a cleanup step.

Frequently asked questions

How do I merge Excel files and remove duplicate rows? Upload the files, pick Append rows, turn on "Remove duplicate rows", and choose the key — "all" for whole-row duplicates, or the key columns that identify a row. The report tells you how many rows were removed.

Will deduplication delete data I need? Whole-row dedupe only removes rows that are identical in every column, so a removed row carried no information the kept row does not have. Key dedupe can remove real rows if the key is too broad — for example, deduplicating by "Customer ID" alone removes every repeat visit by the same customer. Choose the key carefully, and check the count in the report.

What is the difference between deduplication and Merge by ID? Deduplication removes rows. Merge by ID folds rows together and keeps every distinct value. Use dedupe when the repeated rows carry the same information; use Merge by ID when they carry different information. See our guide on combining Excel files for the full comparison of the three modes.

Can I remove duplicates after merging instead? Yes. Excel has a built-in Remove Duplicates feature, and you can use it on the merged workbook. But doing it during the merge is better: the audit report records how many rows were removed and why, and the count is part of the merge's evidence. If you remove duplicates afterwards in Excel, that step leaves no record.

Why are there still duplicates after I turned dedupe on? Probably near-duplicates: the same row with a different spelling, a different date format, a trailing space, or a number stored as text in one file. Standardise the key columns before you merge, and check the merged table by sorting on the key.

Is deduplication free? Deduplication is a Starter and Pro feature. The free plan stacks rows exactly as they are. Starter is $9 a month and also adds Merge by ID and the downloadable audit report. The full comparison is on the pricing page.

References

• U.S. Census Bureau, data documentation and quality notes: https://www.census.gov/
• National Center for Education Statistics, survey data documentation: https://nces.ed.gov/
• U.S. Bureau of Labor Statistics, published datasets: https://www.bls.gov/
• Microsoft Support, Excel help and how-to articles: https://support.microsoft.com/