Why does Excel keep Sarah Chen’s $45,200 entry but delete her identical $45,200 record two rows down? Why does sorting before deduping change the outcome entirely? Why does ‘Remove Duplicates’ ignore your manually sorted order unless you sort first?
Quick Answer
Excel always keeps the first occurrence of each unique row (scanning top-to-bottom) and deletes all subsequent duplicates—regardless of values in other columns. It does not choose based on highest value, most recent date, or alphabetical order. The position in the list—not content—decides what stays.
All the Methods
| Method | Steps | Best For | Limitations |
|---|---|---|---|
| Data Tab → Remove Duplicates | Select range (e.g., A1:D12), Data → Remove Duplicates → choose columns → OK | Fast cleanup of contiguous tables with headers | Ignores hidden rows; can’t preview which rows will be deleted |
| Advanced Filter (In-Place) | Data → Advanced → check 'Unique records only' → OK | Preserving original layout while extracting uniques | Overwrites original data if no destination set; no undo after filtering |
| =UNIQUE() function | Enter =UNIQUE(A2:D12) in a blank cell (e.g., F2) | Dynamic, spill-based lists that auto-update with source changes | Requires Excel 365 or 2021; won’t modify original table |
| Power Query (Get & Transform) | Data → From Table/Range → Home → Remove Rows → Remove Duplicates | Large datasets, repeatable workflows, multi-step cleaning | Adds latency for small tasks; requires loading into Power Query editor |
| Conditional Formatting + Manual Delete | Highlight duplicates → sort by color → delete red rows manually | Full visual control over which duplicates to keep | Time-intensive; error-prone at scale |
Method 1 Deep Dive
Let’s walk through the classic Data → Remove Duplicates workflow using this realistic sales log (A1:D12):
| Name | Company | Amount | Date |
|---|---|---|---|
| Sarah Chen | Acme Corp | $45,200 | 2024-03-15 |
| James Wu | NexaTech | $32,800 | 2024-03-16 |
| Sarah Chen | Acme Corp | $45,200 | 2024-03-18 |
| Maya Patel | Strata Labs | $29,500 | 2024-03-17 |
| Sarah Chen | Acme Corp | $45,200 | 2024-03-19 |
| Liam O’Sullivan | Veridian Inc | $51,100 | 2024-03-20 |
Select A1:D12. Go to the Data tab. Click Remove Duplicates. In the dialog, ensure “My data has headers” is checked. Uncheck Date — we want to treat Sarah’s three entries as duplicates *only* on Name + Company + Amount. Click OK.
The status bar flashes: “12 rows found. 2 duplicate(s) removed.” That means Excel kept A1:A12’s first occurrence of each combo — so row 1 (Sarah, Acme, $45,200) stays. Rows 3 and 5 vanish. You’re left with 10 rows.
Here’s the counterintuitive bit: If you had sorted this table by Date descending *before* clicking Remove Duplicates, row 5 (2024-03-19) would become the first occurrence — and rows 1 and 3 would be deleted instead. Excel doesn’t “choose” — it obeys order. Always.
Keyboard shortcut? Yes: Alt + A + M (press Alt, then A, then M — don’t hold them). Try it on your next dataset.
Method 2 Deep Dive
Now let’s use Advanced Filter — ideal when you need to extract uniques *without altering* your source. Keep your original A1:D12 intact. Click any cell inside it. Go to Data → Advanced.
In the dialog, select “Filter the list, in-place”. Check “Unique records only”. Leave “List range” auto-filled as $A$1:$D$12. Click OK.
Excel hides all but the first instance of each duplicate group. You’ll see row numbers like 1, 2, 4, 6, 7, 9, 10, 11, 12 — with rows 3 and 5 now hidden. This isn’t deletion — it’s filtering. To copy these visible rows elsewhere: select A1:D12 → Ctrl + C → right-click → Paste Special → Values.
Why use this instead of Remove Duplicates? Because it’s reversible. Press Ctrl + Shift + L to toggle the filter off — and your full dataset reappears instantly. No undo chain required. Also, unlike Remove Duplicates, Advanced Filter respects manual row hiding: if you hide row 4 yourself first, it won’t appear in the filtered result.
The beauty of this approach is its non-destructiveness — perfect for auditing or when stakeholders demand full traceability.
Cheat Sheet
| Action | Shortcut / Steps | Notes |
|---|---|---|
| Open Remove Duplicates | Alt + A + M | Works only when a cell in your data range is selected |
| Open Advanced Filter | Alt + A + Q | Ensure headers are present — dialog auto-detects them |
| Apply UNIQUE() formula | =UNIQUE(A2:D12) in F2 | Spills results automatically; includes header if source does |
| Toggle AutoFilter | Ctrl + Shift + L | Critical for checking Advanced Filter output or restoring visibility |
| Sort by column (ascending) | Select column → Data → Sort Smallest to Largest | Do this *before* Remove Duplicates if you want newest or highest-value entries preserved |
| Highlight duplicates | Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values | Useful for spot-checking before mass removal |
