Flag duplicates with COUNTIF
- In the first empty column, enter
=COUNTIF($A$2:$A$1000,A2)>1(adjust the range to your data) and fill down. - Every TRUE marks a value that occurs more than once, including its first occurrence.
- To keep the first and flag only the repeats, use the expanding-range form:
=COUNTIF($A$2:A2,A2)>1. - Filter the helper column for TRUE, review, and delete the rows.
The point of the formula route is the review step: nothing is deleted until you've looked at what matched.
Multi-column keys: COUNTIFS
=COUNTIFS($A$2:$A$1000,A2,$B$2:$B$1000,B2)>1 flags rows where columns A and B repeat as a pair. It is the formula version of ticking two columns in the Remove Duplicates dialog.
Excel 365: UNIQUE
=UNIQUE(A2:C1000) spills a deduplicated copy next to your data, recalculating live. Paste it back as values when you're happy. Not available before Excel 2021/365.
Know when to stop writing formulas
For a plain "just delete them", Data → Remove Duplicates is faster than any formula. See the Excel guide and tool. And all of the above match exactly: an extra space or different capitalization defeats them. When the mess is in the values themselves, drop the file into our in-browser tool (case-insensitive, column selection) or use fuzzy matching for names. That works best on lists under a few thousand rows, and the file never leaves your browser.
FAQ
Frequently asked questions
What formula finds duplicates in Excel?
=COUNTIF($A$2:$A$1000,A2)>1 in a helper column returns TRUE for every value that appears more than once. To flag only the second and later occurrences (so the first stays), use =COUNTIF($A$2:A2,A2)>1, noting the expanding range.
How do I check duplicates across multiple columns?
Use COUNTIFS with one range/criteria pair per column: =COUNTIFS($A$2:$A$1000,A2,$B$2:$B$1000,B2)>1 flags rows where both columns repeat together.
Does Excel have UNIQUE like Google Sheets?
Yes, in Excel 365 and Excel 2021: =UNIQUE(A2:C1000) spills a deduplicated copy of the range. Older versions don't have it, so use the COUNTIF helper column or Data → Remove Duplicates instead.
Formula or the Remove Duplicates button: which should I use?
Formulas when you want to review before deleting or keep the original intact; the button (Data → Remove Duplicates) when you just want them gone. Both match exactly, so for case-insensitive or fuzzy matching, use a tool that normalizes values first.
Last updated