Remove Duplicates - Decide the record key first
Remove repeated records from a working copy while distinguishing a duplicate key from conflicting records that need review.
By the end, you can
- Choose the intended duplicate key.
- Keep full record fields together during removal.
- Recognize data loss and review conflicting repeated keys.
Before you start: Know which fields identify one record. Work on a copy before changing the source.
In this lesson
The key defines what counts as the same record
Remove Duplicates deletes repeated records from the selected range or Table. The chosen columns define the duplicate key, but a removed record loses all its selected fields. An employee-ID-only key treats two E102 rows as duplicates even if their department values disagree. That conflict should be understood before deciding which record to keep.
An import containing repetition and a conflict
E101| A | B | C | |
|---|---|---|---|
| 1 | Employee ID | Name | Department |
| 2 | E101 | Anika | Sales |
| 3 | E102 | Dev | Finance |
| 4 | E101 | Anika | Sales |
| 5 | E103 | Meera | Operations |
| 6 | E102 | Dev | Support |
Run the agreed ID-only rule on a working copy
- Keep the Original import intact and copy it to a WorkingCopy sheet. Review the E102 department conflict before accepting an ID-only rule.
- Select the complete A1:C6 record range and choose Data > Remove Duplicates.
- Confirm that the selection has headers. Under Columns, select only Employee ID as the duplicate key.
- Run the operation on the working copy. Excel retains the first occurrence in the current order and removes later records with the same key.
- Check the message: two duplicate records removed, three distinct-ID records remain. Compare the resulting departments with the agreed retention rule.
- Use Ctrl+Z immediately if the wrong key or scope was chosen; retain the original copy for reconciliation.
E101| A | B | C | |
|---|---|---|---|
| 1 | Employee ID | Name | Department |
| 2 | E101 | Anika | Sales |
| 3 | E102 | Dev | Finance |
| 4 | E103 | Meera | Operations |
A whole-record rule answers a different question
E101| A | B | C | |
|---|---|---|---|
| 1 | Employee ID | Name | Department |
| 2 | E101 | Anika | Sales |
| 3 | E102 | Dev | Finance |
| 4 | E103 | Meera | Operations |
| 5 | E102 | Dev | Support |
Use multiple key columns when the real entity is a combination, such as Customer ID plus Order Date. A displayed repeated customer name is not enough to prove two orders are duplicate records.
Common mistakes and tips
The UNIQUE formula produces a separate distinct result instead of deleting source records. The custom workspace does not implement native Remove Duplicates, so no challenge is linked as that destructive workflow.
Check your understanding
Choose an answer for instant feedback. You can try again; answers stay only on this page.