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!
![Reblog this post [with Zemanta]](http://img.zemanta.com/reblog_e.png?x-id=4e3c279a-8142-4345-bf7f-6ed0d625c322 "Excel Tip: How to easily find and delete duplicate data")
Via RSS
|
{ 2 trackbacks }
{ 6 comments… read them below or add one }
WHOA. The part where you create the filter and then run it. Then DELETE the data and magically make it return with the opposite filter.. rocks. Srsly.
Carissa:
It is awesome isn’t it! I have so many reasons why I love Filters – they also come in really handy when trying to pull “select” data out of specific areas — especially when you have a spreadsheet that has a lot of info in it. For example: I work with a spreadsheet handling monthly water and sewer charges for a 220+ apartment building complex. Every month I need to make sure there are no errors (meaning units that may have not gotten a digital reading). The best way for me to accomplish that is to use the Filters and then only review data that is blank (or zeros). So many fun uses for Excel.
Thanks again for being the inspiration behind this post! Now I’m down 2 of my 3 posts for the week….this ROCKS!
Hello,
I am working on a spreadsheet and have combined the values of 2 cells into one.
using formula: =(MID(I4,SEARCH(“[",I4)+1,SEARCH("]“,I4)-SEARCH(“[“,I4)-1))&”;”&H4
However, I need to know how to keep H4 from being added if that value already exists in I4.
Can you please help?
Thank you,
Marsha
Marsha – I’m a really visual person. Without seeing the spreadsheet it’s hard for me to know what the issue may be with your formula.
As usual you are the MASTER! You have no (or you probably do) idea how much time you just saved me each day!
Thank you again for ALWAYS providing me with the best tip and tricks!
Kristina
Kristina – yeah I always love to hear that my Excel videos saved people time! I’m so glad we re-connected through LinkedIn as a result of a guest blog post on Olinda Services website!