How to use the remove duplicates in excel tool to remove duplicates by simply specifying what you consider a duplicate.
- For updated video clips in structured Excel courses with practical example files, have a look at our MS Excel online training courses . You can even try the Free MS Excel tips and tricks course.
- To see if this video matches your skill level (see the suggested skill score below) do our free MS Excel skills assessment.
- If you are based in South Africa look at the live courses we offer in Johannesburg and Cape Town.
- - 02:18
- - Skill Level: 4
- - What's my skill level
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.