Remove Duplicates in Excel

How to use the remove duplicates in excel tool to remove duplicates by simply specifying what you consider a duplicate.

• 02:18
• Skill Level: 4
• What's my skill level
  

  All of our videos are listed by user Excel Skill Level.

  To find out your personal skill level, download our quick assessment.

  Once you've completed the test, send it to us and we'll evaluate for you.

  

• 

Remove Duplicates in Excel

One on the great new tools available in Excel is the Remove Duplicates in Excel button which is found under the Data Tab. It does exactly what it says – it removes duplicates.

If you go to a Data Base, you can clearly see that the items in yellow are exact duplicates of each other. Highlight the whole worksheet. Click on Remove Duplicates. What you’ll see is that a window opens, you’ll see it says “my data has headers” which in this case is appropriate- but you can switch it off if its not appropriate. What this is saying is that when it considers a duplicate, it is going to look for a duplicate in every single column – Sales person, Product A,B,C etc. Press OK. It tell me how many duplicates it has found and what it has done. When I click OK, the duplicates are all removed. Let’s just undo this and do it again- highlight the area, click Remove Duplicates and in this case we can say consider duplicates only in the Salesperson column- when I say OK, a lot more duplicates have been found – it has only left 8 unique values and when I click OK you’ll see there is only one Salesperson per line and no other duplicate values. Please note that because Excel is deciding what is and isn’t a duplicate, it is highly recommended that before you use this, you establish some sort of control total of what you expect the answer to be. Just check that Excel hasn’t accidentally deleted too many or for whatever reason, because of a space or something like that, it has not deleted an item.