How to eliminate duplicates from a list in excel?

Here is a step by step pictorial presentation of how to find and eliminate duplicate values from a column in a spreadsheet. Before we go for that I tell you logic behind that. We will sort the list in which we have to find duplicates. Due to sorting the list, duplicate entries will appear together. Now we will compare each cell in the list against it's previous cell. ie A2 against A1, then A3 against A2 and so on. If the value of a cell matches the value in previous cell, then it will be declared as a duplicate cell.

1. Here is a list that contains some duplicate and non-duplicate values.


2. Keep first row for header and give some meaningful header as shown in fig.


3. Then sort the list by selecting the list and clicking 'sort ascending' button.


4. After the list is sorted apply this formula in cell B2.
Formula: =IF(A2=A1,"Duplicate","Nonduplicate")


5. Then copy this formula and paste it for remaining cells.



6. This is how you will get duplicate values.


7. Then select the header and apply filter on the list.


8. Then click the dropdown menu of filter to see the options. Select whichever you want and work on it.