Duplicates
How to Find and Highlight Duplicates in Excel
Select the column, then Home, Conditional Formatting, Highlight Cells Rules, Duplicate Values. To mark only the repeats and keep the first occurrence, put =COUNTIF($A$2:A2,A2)>1 in a helper column: it returns TRUE from the second appearance onward. Nothing is deleted, so you can review before you remove anything.
Deleting duplicates is permanent and easy to get wrong. Finding them first costs a minute and shows you what you would lose. This guide covers finding, marking and counting duplicates. When you are ready to delete, see removing duplicates in Excel without losing data.
Method 1: highlight with conditional formatting
- Select the column (click the column letter).
- Choose Home, Conditional Formatting, Highlight Cells Rules, Duplicate Values.
- Pick a colour and click OK.
Every cell whose value appears more than once is coloured, including the first one. The comparison ignores upper and lower case, so Anna@x.com and anna@x.com count as the same.
Method 2: mark the repeats and keep the first
In a helper column next to your data:
=COUNTIF($A$2:A2,A2)>1The range grows as you fill down, so the first appearance gives FALSE and every later one gives TRUE. Filter the helper column for TRUE to see only the extra copies.
To label them instead:
=IF(COUNTIF($A$2:A2,A2)>1,"Repeat","")Method 3: count how often each value appears
=COUNTIF($A$2:$A$5000,A2)Sort or filter this column for values above 1. A count of 3 means the value occurs three times in total.
Method 4: duplicates across several columns
Two people with the same last name are not duplicates; the same first name, last name and email probably are. Use COUNTIFS with every column that must match:
=COUNTIFS($A$2:$A$5000,A2,$B$2:$B$5000,B2,$C$2:$C$5000,C2)>1Method 5: list them (Microsoft 365 and Excel 2021)
To list each duplicated value once:
=UNIQUE(FILTER(A2:A5000,COUNTIF(A2:A5000,A2:A5000)>1))Older versions of Excel do not have UNIQUE and FILTER; use the helper column from Method 2 and a filter.
Why Excel misses duplicates that you can see
Excel compares exactly. These all look the same to you and different to Excel:
| Looks like | What differs | Fix before comparing |
|---|---|---|
John Smith and John Smith | A trailing space | TRIM |
Smith and smith | Case (only in some tools) | LOWER |
1234 and "1234" | A number versus text | Make both text or both numbers |
Acme Inc and Acme Inc. | Punctuation | Standardise with SUBSTITUTE |
A. Lee and Anna Lee | Different spelling of the same person | Needs a human decision |
Create a helper column with the clean version, for example =LOWER(TRIM(A2)), and run the duplicate check on that column.
Tip: two rows are duplicates only if they stand for the same real thing. Before deleting, compare the other columns: the second row may hold a newer phone number or address worth keeping.
Warning: COUNTIF treats * and ? as wildcards and cannot compare text longer than 255 characters. For long text or values that contain those characters, use a helper column that builds a short key.
A whole file at once
DecodingPro flags duplicate rows and duplicate IDs and lists them in the notes column, so you decide what to keep instead of having rows deleted without telling you.
You start with 100 free credits. Every change is logged, and anything unclear is flagged, never guessed.
Fix a spreadsheetQuestions people ask
How do I highlight the whole row when one column is a duplicate?
Select your data, choose Conditional Formatting, New Rule, Use a formula, and enter =COUNTIF($A:$A,$A2)>1. The dollar sign before A keeps the check on column A for every cell in the row.
Can I find duplicates between two sheets?
Yes. Use =COUNTIF(Sheet2!$A:$A,A2)>0 to mark values that also exist on the other sheet, or see comparing two lists in Excel.
Does conditional formatting find duplicates in large files?
It works, but it slows a sheet with tens of thousands of rows. A helper column with COUNTIF is easier to filter and to sort.