Find Duplicates
Put =COUNTIF($A$2:$A$500,A2) in a helper column and fill it down: any row reading more than 1 occurs more than once. Nothing in the data itself changes, so filter or sort on that column to pull the repeats together. Anchoring only the start of the range — =COUNTIF($A$2:A2,A2)>1 — flips the meaning to "this is a later copy", leaving the first occurrence unflagged, which is the version you want if the next step is deletion. When a duplicate is defined by several columns at once, COUNTIFS takes one range and criteria pair per column, and the helper column is still the only thing added.
The formula
=COUNTIF($A$2:$A$500, A2) how many times this row's value appears in the whole column
=COUNTIF($A$2:A2, A2) > 1 TRUE from the second occurrence onward — note the expanding range
=COUNTIFS($A$2:$A$500, A2, $B$2:$B$500, B2) > 1 a duplicate means A and B both repeat together
=COUNTIF($B$2:$B$500, A2) > 0 this value also exists somewhere in column B
=SUMPRODUCT(--EXACT($A$2:$A$500, A2)) > 1 case-sensitive: Smith and SMITH stay separateA worked example
A2:A11 holds ten order IDs in this order: 1001, 1002, 1003, 1002, 1005, 1001, 1007, 1008, 1002, 1010. Column B is empty and headed "Repeat?".
=COUNTIF($A$2:$A$11, A2) in B2, filled down to B11
B reads 2, 3, 1, 3, 1, 2, 1, 1, 3, 1 — every row carrying 1001 shows 2 and every row carrying 1002 shows 3, first appearances included. Swapping in =COUNTIF($A$2:A2, A2) gives 1, 1, 1, 2, 1, 2, 1, 1, 3, 1 instead, so filtering that column for values above 1 selects exactly the four rows you could delete without losing an order.
Which one do I need?
| If you want to… | Use |
|---|---|
| Every copy should be marked, first occurrence included | =COUNTIF($A$2:$A$500,A2)>1 in a helper column |
| Only the later copies should be marked | =COUNTIF($A$2:A2,A2)>1 — the anchored start with a relative end is what makes it look backwards only |
| A colour is easier to read than a number | Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values |
| A duplicate means the same name AND the same date | COUNTIFS, one range and criteria pair per column that forms the key |
| Which values in column A also occur in column B | =COUNTIF($B$2:$B$500,A2)>0 — a cross-column check, not a within-column one |
| The two lists sit on different sheets | The same COUNTIF with the other sheet in the range: =COUNTIF(Sheet2!$A$2:$A$500,A2)>0 |
| You want the tally per value rather than a yes or no | Summarise the counts instead of flagging rows |
| You have finished looking and the extra rows should go | Data > Remove Duplicates — destructive, and it will not tell you which rows it took |
Frequently asked questions
Is there an Excel function that finds duplicates?
There is no dedicated one. COUNTIF and COUNTIFS in a helper column are the standard answer, conditional formatting's Duplicate Values rule is the no-formula route, and Remove Duplicates only deletes — it never shows you what it found.
Why does COUNTIF flag long ID numbers that are not identical?
COUNTIF compares numbers at 15 significant digits, so 16-digit and longer values — card numbers, IBANs, some barcodes — look equal to it from the 16th digit onward. Force a text comparison instead with =COUNTIF($A$2:$A$500, A2&"*"), which compares the full string.
How do I find duplicates across an entire workbook?
Either add one COUNTIF per sheet and sum them, which is fine for three or four sheets, or stack every sheet into a single table with Get Data > From File > From Workbook and run one COUNTIF over the combined column. The second scales and refreshes; the first does not.
How do I show only the duplicate rows?
Add the helper column, then Data > Filter and filter it to values greater than 1, or sort it largest to smallest so the repeats collect at the top. Both leave the data intact, so you can undo the view without undoing anything else.
New guides and tools, once a month
DE + EN · double opt-in · no spam