How to spot and remove duplicates
To easier spot duplicates you can highlight them using conditional formatting. Mark the column where you want to highlight duplicates. On the “HOME” tab, click “Conditional formatting” and select “Highlight Cells Rules”, and then “Duplicate Values”.
Here you can select how you want the duplicates to be formatted. I’ll click “OK” and now the duplicates in my dataset are clearly visible. If I scroll down, I can see that I have even more duplicates further down in the list. To see all duplicates at the top right-click and select “Sort” , “Put selected Cell Color on Top”. Now all duplicate records are sorted on top.
To automatically remove duplicates, mark any cell in your dataset, on the “DATA” tab lick “Remove Duplicates”. Leave all the columns checked to only remove rows where the values in all columns are the same, and then click “OK”.
As you can see, Excel removed 23 duplicate records.