Why does Excel keep one copy of 'Sarah Chen' but delete both 'Michael Lee' entries? Why does removing duplicates in column A alone leave duplicates intact when column B differs? Why does it ignore formatting, formulas, and leading spaces—but choke on invisible non-breaking spaces?
Quick Answer
Excel’s Remove Duplicates compares values across selected columns row-by-row, keeps the first occurrence, and deletes all subsequent identical rows—not based on visual appearance, but on raw stored values. It ignores formatting, treats "1" and 1 identically, and silently converts text numbers to numbers before comparing.
All the Methods
| Method | Steps | Best For | Limitations |
|---|---|---|---|
| Data tab → Remove Duplicates | Select range → Data tab → Remove Duplicates → choose columns → OK | One-time cleanup of clean, contiguous tables | No undo after closing workbook; can’t preview which rows will be deleted |
| Advanced Filter (Unique records only) | Data tab → Advanced → check 'Unique records only' → specify list range & copy to | Preserving original data + generating a clean output side-by-side | Requires header row; doesn’t modify source; no column selection flexibility |
| =UNIQUE() function (Microsoft 365) | Enter =UNIQUE(A2:C12) in a blank cell → spills result automatically | Dynamic, live-updating lists; ideal for dashboards or linked reports | Not available in Excel 2019 or earlier; won’t delete rows—only returns unique set |
| Conditional formatting + manual review | Highlight duplicates with CF → sort → manually delete visible dupes | High-stakes data where you must verify each deletion (e.g., client contracts) | Time-intensive; error-prone at scale; no automation |
| Power Query (Get & Transform) | Data → From Table/Range → Home → Remove Rows → Remove Duplicates | Large datasets, repeatable workflows, audit trail, and column-level control | Steeper learning curve; requires loading into Power Query editor |
Method 1 Deep Dive
Let’s walk through the classic Data tab method using this sample dataset in A1:C11:
| Name | Company | Amount |
|---|---|---|
| Sarah Chen | Acme Corp | $45,200 |
| Michael Lee | Beta Labs | $31,850 |
| Sarah Chen | Acme Corp | $45,200 |
| Javier Ruiz | Nexus Inc | $52,100 |
| Sarah Chen | Zenith Group | $45,200 |
| Michael Lee | Beta Labs | $31,850 |
| Anya Patel | Acme Corp | $29,400 |
| Sarah Chen | Acme Corp | $45,200 |
| Liam O’Sullivan | Beta Labs | $38,750 |
| Michael Lee | Beta Labs | $31,850 |
Now select A1:C11. Go to the Data tab → click Remove Duplicates. In the dialog, ensure ‘My data has headers’ is checked. Uncheck ‘Amount’ — because we only want uniqueness across Name + Company. Click OK.
Excel keeps row 1 (Sarah Chen, Acme Corp), deletes rows 3 and 8 (identical Name+Company), keeps row 2 (Michael Lee, Beta Labs), deletes rows 6 and 10. Row 5 (Sarah Chen, Zenith Group) stays — different company, so not a duplicate. The beauty of this approach is how brutally literal it is: no fuzzy matching, no partial text scans, just exact value equivalence.
Surprising tip: If column C contains formulas like =B2*1.07, Excel compares the result, not the formula. So even if two cells have different formulas but resolve to $45,200, they’ll match. And yes — Excel treats 45200, $45,200, and "45200" as identical if the underlying stored value is numeric 45200.
Method 2 Deep Dive
For repeatable, auditable, and scalable deduplication, Power Query is unmatched. Start with the same A1:C11 range. Select it → Data tab → From Table/Range → OK (make sure ‘My table has headers’ is checked).
You’re now in Power Query Editor. Click the Home tab → Remove Rows → Remove Duplicates. By default, it checks all columns. To replicate our earlier logic, hold Ctrl, click the Amount column header to deselect it, then click Remove Duplicates.
The magic happens next: Power Query shows a step in the Applied Steps pane called Removed Duplicates. You can rename it, reorder it, or click the gear icon to edit which columns are included — without re-running the entire query. What makes this elegant is that every step is recorded, reversible, and portable. Export back to Excel via Home → Close & Load.
Try this: change Sarah Chen’s Company in row 5 from “Zenith Group” to “Acme Corp”. Refresh the query — the duplicate count updates instantly. No manual reselection. No risk of forgetting to uncheck Amount again.
Cheat Sheet
| Action | Shortcut | Notes |
|---|---|---|
| Open Remove Duplicates dialog | Alt + A + M |
Works only when a data range is selected |
| Select entire table (with headers) | Ctrl + A (twice) |
First Ctrl+A selects current region; second expands to full used range |
| Toggle column selection in dialog | Click column name | No shortcut — but you must uncheck unwanted columns before clicking OK |
| Undo last Remove Duplicates | Ctrl + Z |
Only works before saving/closing — Excel doesn’t log this action in Undo stack after save |
| Load data into Power Query | Alt + A + T |
‘T’ for ‘From Table/Range’ — fastest path into the editor |
