📎 This article includes 1 downloadable practice file ↓
Duplicate invoices mean paying twice; duplicate customer rows mean wrong counts and double emails. Before you delete anything, decide what counts as a duplicate — the same invoice number? Same customer and amount? The whole row identical? Each needs a slightly different formula.
In this article
Duplicates in one column
=IF(COUNTIF($D$2:$D$201, D2) > 1, "Duplicate", "")Marks every copy, including the first. To mark only the second and later copies:
=IF(COUNTIF($D$2:D2, D2) > 1, "Repeat", "")The expanding range counts occurrences so far, so the first stays unmarked — useful when you want to keep one copy.
Duplicates on two or more columns
=IF(COUNTIFS($D$2:$D$201, D2, $I$2:$I$201, I2) > 1, "Same customer + amount", "")For whole-row checks, compare every column you care about, or join them into a key: =A2&"|"&D2&"|"&I2 and count the key.
Highlight with conditional formatting
- Quick: Home › Conditional Formatting › Highlight Cells Rules › Duplicate Values (one column only).
- Multi-column: select A2:P201, new rule with a formula:
=COUNTIFS($D$2:$D$201, $D2, $I$2:$I$201, $I2) > 1The $ before the column letters keeps every cell in the row checking the same columns, so whole rows light up.
List the duplicated values (Excel 365)
=UNIQUE(FILTER(D2:D201, COUNTIF(D2:D201, D2:D201) > 1)) ' values that repeat
=UNIQUE(D2:D201, , TRUE) ' values that appear exactly onceRemoving them
Data › Remove Duplicates keeps the first occurrence of each combination of the columns you tick. Sort first (for example newest first) so the copy you want to keep is on top — and always work on a copy.
Where people go wrong
| Problem | Fix |
|---|---|
| “Raj Traders” and “Raj Traders ” not seen as duplicates | TRIM the column first |
| Invoice INV-01 and inv-01 treated as the same | COUNTIF ignores case; use SUMPRODUCT(--EXACT(range,D2)) for case-sensitive checks |
| Long numbers (16+ digits) flagged wrongly | Excel keeps only 15 digits — store card/account numbers as text |
| Deleted the wrong copy | Sort before Remove Duplicates; keep a backup |
Practice
Download the combo practice workbook below: a Sales sheet of 200 orders plus tasks for the formulas on this page, each with an automatic ✓ check and an Answers sheet.
More: all formula combos · Excel function course.
📎 Practice files for this article
- 📗Formula combinations practice workbook50 tasks: lookups, running totals, ranks, top-N, unique counts, FY, age, text extraction, list comparison, weighted averages, ageing, rolling totals, duplicates and working days — with automatic checks.⬇ XLSX · 39 KB
Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.
Stuck on a step? Ask a question and the AI answers using this article.