Remove duplicates with a clear business key
Separate repeated rows from legitimate repeat orders, and account for blank keys.
In this guide
A repeated order ID does not always mean a repeated order. One export may contain several dates, line items or revisions for the same identifier. Removing duplicates before defining the key can delete legitimate records without an obvious error.
Define what a duplicate means for this job
The worked exercise removes repeated rows using the selected columns. It does not establish a universal rule for case, formatting or every Excel version. If your real records distinguish upper- and lower-case identifiers, first test that distinction in a disposable copy. Rows sharing a customer or order identifier may still represent different line items. Resolve that business rule before deleting anything, then retain both the untouched source and the removal count.
Prepare a copy
Download duplicates-input.csv and keep duplicates-expected.csv separately for comparison. The input occupies A1:C7 when headers start in row 1. Save a working .xlsx copy before deleting rows.
Import order_id and order_date as Text for this exact-row exercise; keep amount numeric using the sample's period decimal separator. If your importer has already converted the ISO dates, check that the six rows still describe the same dates before proceeding. The earlier Mac test used entered worksheet data, so follow the checks here when importing the downloadable CSV yourself.
Read the six-row example
The practice file has order_id, order_date and amount. A101 appears three times: two identical rows dated January 1 with an amount of 10.00, and another dated January 2 with an amount of 15.00. A102 has one row for 8.00. Two additional records have blank IDs and amounts of 5.00 and 6.00 on different dates.
Our rule is deliberately narrow: remove only fully identical rows across all three columns. Keep records with missing IDs for review. This is an exact-row exercise, not a claim that these three columns form a universal order key.
Remove only exact repeats
- Import order_id as text and retain an untouched copy of the six-row table.
- Select the complete table, including all three columns. Include the header row and identify it as headers in the dialog.
- Use Data → Remove Duplicates. Select order_id, order_date and amount together for this exercise.
- Confirm the operation on the copy, then record the number of rows removed.
- Compare the resulting table against duplicates-expected.csv. Keep both records with blank IDs visible for further investigation.
Selecting only order_id asks a different question: whether that identifier has appeared before. It would wrongly collapse the January 2 A101 transaction. It can also collapse records whose selected key is blank. Filtering out blank keys before a key-based deduplication is usually safer, but those records still need a review destination.
Reconcile more than the row count
| Measure | Before | After exact-row removal |
|---|---|---|
| Data rows | 6 | 5 |
| A101 rows | 3 | 2 |
| Rows with blank order_id | 2 | 2 |
| Amount total | 54.00 | 44.00 |
The 10.00 difference should correspond exactly to the duplicate row. A result with three rows or a different amount total suggests the wrong columns were selected. Count checks alone would not tell you which legitimate transaction disappeared.
Import and compare values consistently. For this fixture, matching means identical text fields. Real Excel tables may already contain converted dates or formatted numbers, so visually similar cells deserve a closer look before destructive cleanup.
Compare the retained records
| order_id | order_date | amount |
|---|---|---|
| A101 | 2026-01-01 | 10.00 |
| A101 | 2026-01-02 | 15.00 |
| A102 | 2026-01-01 | 8.00 |
| (blank) | 2026-01-01 | 5.00 |
| (blank) | 2026-01-02 | 6.00 |
This is the expected retained table, not an Excel screenshot. The notation (blank) describes an empty cell; do not type those characters into your data. Your date display may differ, but the dates and retained records must agree. If the outcome is wrong, use Undo immediately or reopen the untouched working copy. Recheck the selected columns and headers before repeating the removal. Do not add records back from memory to force the total to match.
When you need a different survivor rule
If an export has multiple revisions, define which revision wins: for example, the greatest trustworthy update timestamp within a documented key. Ties and missing timestamps need an explicit decision. Do not assume Remove Duplicates has inferred the latest or best record.
For recurring work, document the key, the survivor rule, the rejected-row count and the retained-row count. Store discarded records separately when you need an audit trail. Before deduplicating a monthly batch, account for each input file: a file loaded twice is a different problem from a repeated transaction.
Related checks
Before deleting rows because a reference lookup looks wrong, compare both lists and count exact matches. A repeated reference key needs a business rule, not an arbitrary first match.
Reference
Microsoft explains the selected-column behavior and recommends preserving the original data in Find and remove duplicates.
Verification scope
On September 29, 2026, we entered the six sample records into Excel for Mac 16.112.2 and used Data → Remove Duplicates with headers and all three columns selected. The result contained five data rows totaling 44, including two A101 records and both blank-ID records. This checks the native duplicate-removal operation on the entered sample; it is not a test of every CSV import path or a business-key survivor rule.