Have you been looking for an easy way to find duplicates in Excel?
There are many different formulas and avenues you can take to find duplicate data in Excel. This particular function is my favorite because your list does not have to be in any particular sorted order.
Using this function can save you hours of time, especially if you have hundreds or thousands of rows of data in Excel.
With your list in Excel you will enter in this formula into any adjacent cell:
=IF(MAX(COUNTIF(A1:A11,A1:A11))>1,”Duplicate”,”No Duplicate”)
What this does (says): If the count of data is more than one to return the word “duplicate.” If there is one or less to return “no duplicate”.
Once this is done the fastest way to remove your duplicates is to filter the data on the duplicate/no duplicate column.
- In Excel 2007: go to Data and select the filter icon (the funnel)
- In Excel 2003: go to Data>Filter>Auto Filter
Important Tip:
- You need a header row for the “Filter” to work properly – HOWEVER, enter your header row AFTER you enter in the formula to find the duplicates. Otherwise your results will be skewed (aka – wrong)
This blog post is dedicated to @CarissaRogers a GoodNCrazy Mom of all trades… Jack of none. She participates in #GNO (Girls Night Out Twitter chat) and shares recipes, blogging tips and parenting fun!