Excel’s built-in “Remove Duplicates” feature works fine when duplicates are obvious — identical rows in every column. But what about when you need to find duplicates based on specific columns only? An order might appear twice with the same customer and date but different notes. A contact list might have the same person with slightly different phone numbers. Here’s how to find and handle duplicates based on the columns that actually matter.
The Data
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Customer | Date | Amount | Notes |
| 2 | Acme Corp | 3/15/2025 | $1,200 | Initial order |
| 3 | Bright LLC | 3/16/2025 | $850 | Rush delivery |
| 4 | Acme Corp | 3/15/2025 | $1,200 | Duplicate entry |
| 5 | Chen Inc | 3/17/2025 | $2,400 | Standard |
| 6 | Bright LLC | 3/16/2025 | $900 | Corrected amount |
Rows 2 and 4 are duplicates based on Customer + Date + Amount. Rows 3 and 6 match on Customer + Date but have different amounts — that might be a correction, not a true duplicate. The right approach depends on which columns you consider.
Method 1: COUNTIFS Formula to Flag Duplicates
Add a helper column that counts how many times each combination of Customer + Date appears:
=COUNTIFS(A$2:A$100, A2, B$2:B$100, B2)
Counts rows where both the Customer AND the Date match the current row. Any result greater than 1 means it’s a duplicate.
Wrap it in an IF for a clear flag:
=IF(COUNTIFS(A$2:A$100, A2, B$2:B$100, B2) > 1, "Duplicate", "Unique")
Result
| A | B | C | E | |
|---|---|---|---|---|
| 1 | Customer | Date | Amount | Status |
| 2 | Acme Corp | 3/15/2025 | $1,200 | Duplicate |
| 3 | Bright LLC | 3/16/2025 | $850 | Duplicate |
| 4 | Acme Corp | 3/15/2025 | $1,200 | Duplicate |
| 5 | Chen Inc | 3/17/2025 | $2,400 | Unique |
| 6 | Bright LLC | 3/16/2025 | $900 | Duplicate |
Adding more criteria
To check Customer + Date + Amount, just add another criteria pair: =COUNTIFS(A$2:A$100, A2, B$2:B$100, B2, C$2:C$100, C2). Now rows 3 and 6 would be flagged as unique since their amounts differ.
Method 2: Concatenation Key for Easy Filtering
Create a unique key by combining the columns you care about, then use COUNTIF on that key:
Step 1 — Create a key column
=A2&"|"&TEXT(B2,"YYYY-MM-DD")&"|"&C2
Creates a key like “Acme Corp|2025-03-15|1200”. The TEXT function ensures dates compare consistently.
Step 2 — Flag first occurrence vs duplicates
=IF(COUNTIF(E$2:E2, E2) = 1, "Keep", "Remove")
The range starts at E$2 and grows as it copies down. The first time a key appears, it’s “Keep”. Every subsequent occurrence is “Remove”. This lets you keep one copy and delete the rest.
Method 3: Built-In Remove Duplicates
For a quick cleanup when you just want to delete duplicates:
1Select your data range (including headers)
2Go to Data → Remove Duplicates
3Uncheck columns you don’t want to compare — this is the key step. Leave only the columns that define a “duplicate” checked.
4Click OK. Excel deletes duplicate rows and tells you how many were removed.
Destructive and unforgiving
Remove Duplicates deletes rows permanently with no undo after you save. Always work on a copy of your data. Also, it keeps the first occurrence and deletes later ones — if the later row is the corrected version, you’ll lose the correction.
Conditional Formatting to Highlight Duplicates Visually
To highlight duplicates without modifying data:
1Select column A
2Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values
For multi-column duplicates, add a helper key column first (Method 2) and apply the conditional formatting to that column.
When Formulas Aren’t Enough
Finding duplicates in a small dataset is straightforward. Real-world deduplication gets complicated fast:
- Fuzzy duplicates — “Acme Corp” and “Acme Corporation” are the same company but won’t match with COUNTIFS
- Keep the best record — when duplicates exist, you need to keep the one with the most recent date or highest amount, not just the first occurrence
- Cross-file deduplication — finding duplicates across multiple source files before merging
- Audit trail — logging which records were flagged as duplicates and why
- Recurring imports — running deduplication every time new data arrives
A custom VBA deduplication tool handles fuzzy matching, smart retention rules, cross-file comparison, and detailed logging — all with one click.
“David does phenomenal work. We brought him in during the middle of a project and he was able to pick up a significant workload very quickly.”
Report Creation Client · Upwork
Deduplicating Thousands of Records Manually?
Tell us about your data and we’ll build a tool that handles it automatically.
Start a Project