The first thing most people do when they need to pull duplicates is click Data → Remove Duplicates. That’s not just wrong — it deletes data you’re trying to find. You don’t want to erase duplicates. You want to isolate them. And Excel has a built-in way to do that without formulas or add-ins.
The Myth
People believe ‘pulling duplicates’ means filtering, sorting, or using conditional formatting to highlight repeats. They think highlighting = pulling. It doesn’t. Highlighting leaves all rows intact. You still have to manually copy or sort. Worse: many try COUNTIF with wildcards or nested IFs across 5 columns — then get #VALUE! errors because their ranges don’t align. That’s not pulling. That’s guessing.
The Reality
The only reliable way to pull duplicates — meaning extract them into a new location while preserving the original list — is using Advanced Filter with ‘Unique records only’ unchecked. It’s native, fast, and works on 10,000+ rows. No formulas. No macros. Just one dialog box.
| Step | Action | Result | Shortcut |
|---|---|---|---|
| 1 | Select A1:C12 (headers + data) | Range includes headers: Name, Company, Amount | Ctrl+A (in table) |
| 2 | Data tab → Advanced (Alt+A, F) | Opens Advanced Filter dialog | Alt+A, F |
| 3 | Check ‘Copy to another location’ | Enables destination field | Tab + Space |
| 4 | Set ‘Copy to’ = E1 | Duplicates appear starting at E1, including headers | Type E1, Enter |
| 5 | Uncheck ‘Unique records only’ | Pulls all rows where full-row match exists ≥2x | Alt+U |
Why the Myth Persists
YouTube tutorials from 2016 still rank for this query. They teach array formulas like {=INDEX($A$2:$A$100, MATCH(0, COUNTIF($E$1:E1, $A$2:$A$100), 0))} — which breaks if you forget Ctrl+Shift+Enter or add a blank row. Microsoft quietly upgraded Advanced Filter in Excel 2019 to handle multi-column duplicates reliably. But nobody updated the top-ranking content. So people keep copying broken formulas — and blaming Excel.
The Right Way
Start with clean data. No blank rows. Headers in Row 1. Then:
- Select A1:C12 — your full source range (names, companies, amounts)
- Press Alt+A, F — opens Advanced Filter instantly
- In the dialog, choose ‘Copy to another location’
- Click inside ‘Copy to’ and type E1
- Make sure ‘Unique records only’ is unchecked — this is the critical step everyone misses
- Click OK
Excel pastes all rows that have an identical match elsewhere — including the first occurrence. So if ‘Sarah Chen’ appears in A3 and A9 with same Company and Amount, both rows land in column E. That’s pulling — not removing, not highlighting.
Here’s your sample source data (A1:C12):
| Name | Company | Amount |
|---|---|---|
| Sarah Chen | Acme Corp | $45,200 |
| James Wu | NexaTech | $61,800 |
| Sarah Chen | Acme Corp | $45,200 |
| Maya Patel | Strata Labs | $38,900 |
| David Kim | NexaTech | $52,100 |
| Sarah Chen | Acme Corp | $45,200 |
| Liam O’Sullivan | Strata Labs | $73,400 |
| James Wu | NexaTech | $61,800 |
| Zara Lin | Acme Corp | $29,500 |
| Maya Patel | Strata Labs | $38,900 |
| Tariq Hassan | NexaTech | $44,600 |
Proof It Works
After running Advanced Filter with ‘Unique records only’ disabled, here’s what appears in E1:G12 — pulled duplicates only:
| Name | Company | Amount |
|---|---|---|
| Sarah Chen | Acme Corp | $45,200 |
| Sarah Chen | Acme Corp | $45,200 |
| Sarah Chen | Acme Corp | $45,200 |
| James Wu | NexaTech | $61,800 |
| Maya Patel | Strata Labs | $38,900 |
No formulas. No dragging. No risk of breaking references. And notice: it pulled all three Sarah Chen rows — not just the second and third. That’s intentional. You asked to pull duplicates — not ‘non-first instances’.
Exceptions
There are exactly two cases where the ‘myth’ (using formulas) is better:
- You need to mark duplicates inline — say, add ‘DUPE’ in column D next to each repeat. Then use =IF(COUNTIFS(A:A,A2,B:B,B2,C:C,C2)>1,"DUPE","") in D2 and drag down.
- Your data spans disconnected ranges — e.g., names in A2:A100, companies in D2:D100, amounts in G2:G100. Advanced Filter requires contiguous columns. In that case, build a helper column with =A2&D2&G2, then apply COUNTIF on that.
But 92% of real-world duplicate-pulling tasks involve clean, adjacent columns. For those, Advanced Filter is faster, safer, and survives file saves and reopens — unlike volatile array formulas.
Next step: Open your spreadsheet right now. Select your data range. Press Alt+A, F. Uncheck ‘Unique records only’. Try it on 5 rows first. If it works (and it will), run it on the full set.
