Find duplicate entry in a range of cells in excel

If you have a large data size and you would like to find and remove the duplicate entries in a range of cells. We can do that please have a look at the below example.





We have a list of names in the range of A1 to A9. We did find and counted the duplicate entries in the specified range. The formula counted the names and assigned numbers according to the number of entries for each of them.

In the above example the name John was entered four times and the name Jim was entered two times.
1. Enter the formula =COUNTIF(A1:A9,A1) in the cell B2,
2. Drag that formula down to the end of the list at the left. (I.e. from B2 to B9.)
3. Then delete the rows which are not equal to 1.

Now the list is ready for use without the duplicate entries.