When two Excel rows describe the same customer, product, or supplier but each row contains different details, Remove Duplicates is the wrong first move. It can keep one row and delete the other, taking a phone number, address, tag, or note with it. The safer job is to produce one merged record, flag any real disagreement, and leave the originals intact until the output has been reviewed.
That distinction matters in real imports. A customer may appear once with an email and phone number, then again with the same email and a mailing address. Those rows are duplicates for identity, but neither is disposable. This guide shows how to define a match, decide which fields can be combined, and build a review queue for values that should not be chosen automatically.
Start with the key, not the rows
A duplicate row is a technical comparison. A duplicate record is a business decision. Before combining anything, name the field or combination of fields that identifies one entity in your data.
| Possible key | Usually appropriate when | Risk to check first |
|---|---|---|
| Customer ID or account ID | The source system assigns a stable ID. | Exports from different systems may use different IDs for the same person. |
| Email address | One person normally has one business email in the workflow. | Shared inboxes, family addresses, and recycled addresses can make it ambiguous. |
| SKU plus location | Inventory records are unique only within a warehouse or channel. | Using SKU alone may combine records that must remain separate. |
| Name plus another field | No stable ID exists and the data is low risk. | Names are weak identifiers; similar people can be combined by mistake. |
Put the key in an Excel Table before you begin. Tables make formulas easier to audit and make Power Query imports more repeatable. If you cannot state the key in one sentence, do not merge automatically. First create a candidate list for a human to review.
Why Remove Duplicates cannot merge a record for you
Microsoft’s Remove Duplicates guidance explains that the command compares the columns you choose, keeps the first matching occurrence, and removes the other matching rows. The chosen columns define the comparison, but the result is still row removal; it does not infer which phone number is current or whether two different addresses belong to the same person. Microsoft also advises that removing duplicates permanently deletes data, which is why a copy or a preview comes first.
Use this rule: deduplicate only after a record has become safely mergeable. If two matching-key rows are truly identical, removing one is fine. If they carry complementary values, create a new output. If they contain different nonblank values for an important field, route the pair to review.
The four field policies that prevent accidental data loss
Do not apply one rule to every column. A merge becomes safer when each field has a declared policy before formulas or queries run.
| Field policy | Examples | Safe automatic behavior |
|---|---|---|
| Fill-safe | Phone, website, secondary contact, optional note | Use the one nonblank value when all other matching rows are blank. |
| List-safe | Tags, source labels, interests, non-authoritative notes | Keep distinct values in a delimited list only when the downstream system accepts that format. |
| Authoritative | Account status, billing address, opt-in consent, owner | Take a value only from a named source of truth or a documented timestamp rule. |
| Conflict-prone | Different phone numbers, dates of birth, tax IDs, legal names | Do not choose automatically when two or more different nonblank values exist; flag the record. |
This is the information most cleanup guides skip. “Keep the most complete row” is useful only when that row has every correct value. A completeness score cannot tell you whether a filled cell is stale, shared, or contradictory. A visible REVIEW result is safer than a confident but wrong merge.
Work through a small example before touching the full file
Suppose the key is Email. The source has these two rows:
| Phone | City | Tag | |
|---|---|---|---|
| ana@example.com | (555) 0101 | Webinar | |
| ana@example.com | Chicago | Customer |
There is no conflict: Phone has one nonblank value, City has one nonblank value, and Tag is list-safe. A sensible output is one row with Ana’s phone, Chicago, and a tag field such as Webinar; Customer if that is acceptable in the destination system.
Now change the second phone to (555) 0199. The output must not silently select either number. It should keep the key, show both candidate values or a review marker, and wait for the rule owner. This is also why matching a name alone is often too risky.
Make a non-destructive merge sheet with modern Excel formulas
For a modest, one-time cleanup, create an output sheet rather than editing the import. Put a unique key list in the first column, then calculate each output field from the source table. The technique is useful because every result points back to the original records and can be inspected before values are pasted.
Assume your source is an Excel Table named Contacts, with columns Email, Phone, and Tag. A unique key list can begin with:
=UNIQUE(Contacts[Email])Microsoft documents that UNIQUE returns a list of unique values. It is a preview/output formula: it does not delete source rows. For fields where one nonblank value is acceptable, filter matching source values and return the only distinct candidate. A practical pattern is:
=LET(
matches,FILTER(Contacts[Phone],Contacts[Email]=[@Email],""),
count,SUM(--(matches<>"")),
values,UNIQUE(FILTER(matches,matches<>"","")),
IF(count=0,"",IF(COUNTA(values)=1,INDEX(values,1),"REVIEW"))
)The important behavior is not the exact syntax; it is the final branch. One distinct nonblank phone can be filled. More than one returns REVIEW. Test the formula on a tiny copy first, because Excel version support and column names vary.
For a list-safe field such as a marketing tag, you may intentionally combine all distinct nonblank values:
=TEXTJOIN("; ",TRUE,UNIQUE(FILTER(Contacts[Tag],Contacts[Email]=[@Email],"")))Do not use that approach for values that should remain atomic. Joining two billing addresses or consent states into one cell only hides a conflict.
Use Power Query when the same import returns every week
Power Query is the stronger route when the same export arrives repeatedly or the data is too large for hand-maintained output formulas. Load the source through Data > From Table/Range, keep the original query untouched, and build a separate merge query.
- Standardize the key first: trim obvious stray spaces and make a deliberate decision about casing or blank keys.
- Select the key column or columns, then choose Home > Group By.
- Use the All Rows operation so each matching-key group remains available for inspection instead of being immediately collapsed.
- For each output field, apply the field policy: one nonblank candidate may be filled; list-safe values may be combined; multiple different authoritative values become a review flag.
- Load both the merged output and a conflict-only review table. Do not overwrite the imported source.
Microsoft’s Power Query Group By documentation explains that Group By can group equal values from one or more columns and that All Rows holds the underlying matching rows in an inner table. That is exactly the staging point needed for a careful merge. It preserves the evidence behind each output row instead of pretending a duplicate-removal command knows the correct business value.
Separate exact duplicates from complementary duplicates
After you have a merged output and a conflict table, you can still have genuinely identical rows in the original export. Those are the appropriate cases for a duplicate-removal tool: the records match under the selected key and every field you care about is already the same.
If you need to make a clean working copy of a confirmed-safe XLSX file, Remove Duplicates for Excel lets you select the comparison columns and download a cleaned copy. Its public page says the workbook is processed locally in the browser and supports `.xlsx` files. Treat that as a cleanup step for exact duplicates, not as a substitute for a field-level merge policy. Keep the original and check whether formulas, merged cells, or other workbook features require extra care.
Build a conflict queue instead of burying decisions
A good merge process has two outputs:
- Merged records: keys with fields that were filled under clear rules.
- Conflict queue: keys with two or more different nonblank candidates in a field that matters.
Give the conflict queue useful columns: the key, source row numbers, field name, candidate values, source system, and the decision made. That small audit trail prevents a second cleanup run from reopening the same question. It also makes a handoff possible when the person who understands account status or consent is not the person preparing the workbook.
Checks to run before the original rows are retired
- Count source keys, merged keys, exact duplicate groups, and conflict groups. Explain every count difference.
- Sample records with a blank-to-filled merge, a list-safe merge, and a true conflict.
- Check that no blank key was merged into one giant group.
- Confirm the destination system can accept any combined tag or note format.
- Preserve the untouched import and save the merged output under a new name before any destructive deletion.
Microsoft’s duplicate-removal guidance is a useful final reminder: selected columns define the duplicate comparison, and removal is permanent. Use that command only after your key and merge decisions have made the leftover rows truly disposable.
The practical rule
Use Remove Duplicates when one selected row can legitimately survive. Use a merge workflow when each matching row may hold part of the truth. Start with the key, classify every field, preserve conflicts for review, and let deletion be the last action—not the first.
