Excel Tip: How to easily find and delete duplicate data

by Michelle Mangen on November 2, 2009

in Excel Tips & How-To

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:

  1. 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!

Excel Tip: How to easily find and delete duplicate data

  • Pingback: Tweets that mention Excel Tip: How to easily find and delete duplicate data | Social Media Virtual Assistant - Social Media Marketing Services -- Topsy.com

  • http://goodncrazy.com Carissa

    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.

    • http://www.thevirtualasst.com Michelle Mangen

      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!

  • Pingback: uberVU - social comments

  • marsha

    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

    • http://www.thevirtualasst.com Michelle Mangen

      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.

  • http://www.successofficesolutions.com Kristina Andaya

    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

    • http://www.thevirtualasst.com Michelle Mangen

      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!

  • http://www.billygean.co.uk Billygean

    Hi there

    I am using this formula for work, however when I cut and paste your formula my excel tells me the formula I have typed contains an error. When I click okay it then highlights the word “duplicate” within the formula. Any ideas? If I can’t figure this out I am going to have to ctrl + f for EVERY piece of data in my very enormous spreadsheet!

    Thanks for any help

    BG

    • http://www.thevirtualasst.com Michelle Mangen

      Billy — try this —-

      =IF(MAX(COUNTIF(A1:A11,A1:A11))>1,”Duplicate”,”No Duplicate”)

      Also, be sure that the A1:A11 is changed to reflect the actual range you are comparing duplicates for.

  • http://www.billygean.co.uk Billygean

    Hi Michelle

    Sadly it’s still happening. I have changed the values to reflect my cells (A1 – A210) but when I press enter I still get the same error. It also seems to want to change the word “no” in “no duplicate”.

    BG

    • http://www.thevirtualasst.com Michelle Mangen

      Billy – do you want to send me your document and I’ll see if I can figure it out in a few minutes?

Previous post:

Next post: