Method 1: highlight them with conditional formatting
- Select the column or range you want to check. One column at a time is usually what you want, for the reason in the next section.
- On the Home tab, open Conditional Formatting → Highlight Cells Rules → Duplicate Values.
- Leave the dropdown on Duplicate, pick a fill colour, and press OK.
Every value appearing more than once is now coloured, including its first occurrence. Nothing has been changed or deleted, and Conditional Formatting → Clear Rules takes the colour off again. Switching the dropdown from Duplicate to Unique inverts it, which is a fast way to answer the opposite question: what appears exactly once?
Method 2: flag them in a helper column with COUNTIF
Colour is good for eyeballing. A helper column is better when you want to count, filter, sort, or hand the file to somebody else. In the first empty column, enter =COUNTIF($A$2:$A$1000,A2)>1 and fill down. Every TRUE is a value that occurs more than once. To leave the first occurrence unflagged and mark only the repeats, use the expanding range form =COUNTIF($A$2:A2,A2)>1: the range grows as it fills down, so a value is only TRUE once it has been seen before.
Drop the >1 and you get the count itself, which is more useful than a boolean when you want to know whether something appears twice or eleven times.
Method 3: filter down to just the flagged rows
Neither method is much help until you can see the duplicates on their own. Turn on filtering (Data → Filter) and then either filter the helper column for TRUE, or, if you used conditional formatting, open the filter dropdown and choose Filter by Colour and the fill you picked. Sorting by that column or colour works too and keeps everything on screen. From there you can review the group, copy it to another sheet, or delete the visible rows.
Method 4: UNIQUE, if you have Excel 365
=UNIQUE(A2:A1000) spills a list of the distinct values next to your data, live, without touching the original. It answers "what are the real values here" rather than "which rows repeat". Pair it with =COUNTIF($A$2:$A$1000,E2) alongside the spilled list and you have a small frequency table in two formulas. UNIQUE needs Excel 2021 or Microsoft 365; earlier versions do not have it.
The four cases where all of this misses duplicates
Multi-column keys. Duplicate Values highlights cells, not rows. Select first name and last name together and it colours every repeated first name and every repeated last name separately, which is not the question you asked. The formula answer is =COUNTIFS($A$2:$A$1000,A2,$B$2:$B$1000,B2)>1, one range and criteria pair per column.
Case. Neither the highlight rule nor COUNTIF distinguishes ACME from acme. That is usually a relief, but it means Excel cannot tell you where the case differences are, only that they exist somewhere.
Whitespace and stray characters. The reverse problem, and the more common one. A trailing space, a non-breaking space pasted from a web page, or a different apostrophe makes two identical-looking values genuinely different to Excel, so neither method flags them. Run =TRIM(CLEAN(A2)) over the column first, or you are auditing formatting rather than data.
Size. Conditional formatting is recalculated as the workbook does, so a Duplicate Values rule over a very large range makes the sheet sluggish. Apply it narrowly and clear it when you are done.
Seeing what would be removed, without removing it
If what you actually want is a count and a clean copy rather than a coloured sheet, drop the file into the Excel duplicate remover on this site. It reads .xlsx, .xls, .csv and .tsv in your browser, lets you click the exact columns that define a duplicate, and reports how many unique rows were kept and how many duplicates were removed before you download anything. Your original file is never modified and never uploaded, so running it is a read-only look at the answer. Exact matching handles 500,000 rows in well under a second, which is past the point where conditional formatting is pleasant to use. Legacy binary workbook? See removing duplicates from an .xls file. Once you have decided what to delete, the Excel formula guide covers the removal side, including the Remove Duplicates button.
FAQ
Frequently asked questions
How do I find duplicates in Excel without deleting anything?
Select the range, then Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values. Excel colours every value that appears more than once and changes nothing else, so you can look before you decide. Clearing the rule later leaves the data exactly as it was.
Is Excel's duplicate highlighting case sensitive?
No. Both the Duplicate Values rule and COUNTIF treat "ACME" and "acme" as the same value. If case genuinely matters, you need EXACT inside a SUMPRODUCT, because no built-in duplicate feature in Excel offers a case-sensitive mode.
Why does the highlight mark cells that are not duplicate rows?
Because Duplicate Values works cell by cell across whatever you selected, not row by row. Select two columns and it colours any value repeated anywhere in either of them. To find rows that repeat as a pair, use COUNTIFS or a helper column that joins the columns together.
How do I show only the duplicate rows?
Two ways. With conditional formatting applied, open the filter dropdown on that column and choose Filter by Colour. With a COUNTIF helper column, filter it for TRUE. Both give you a view containing only the flagged rows, which you can then copy out or delete.
Does highlighting duplicates slow a large sheet down?
It can. A conditional formatting rule is re-evaluated as the workbook recalculates, and Duplicate Values over tens of thousands of cells is expensive enough to notice while you type. Apply it to the column you care about rather than the whole sheet, and clear the rule once you have your answer.
Last updated