Remove Duplicate From Columns in Microsoft Excel

Today, I learned something interesting which I would like to share across to the readers. This is about eliminating duplicates from a column in an Excel workbook. The Spread sheet contained a column with 1000 odd rows and the challenge was to remove the duplicates and copy the unique rows to a different column. Most of you who would be reading this post would have encountered similar requirements and hope this post guides to get rid of identical values from a given worksheet. The test data we will be using for this exercise is provided below:

Assuming that these are placed in H8:H24 in a worksheet, the steps to eliminate duplicates are as below.

1) Select Data –> Sort & Filter –> Advanced. Following dialog box will be presented

2) Make sure that the following options are selected

a) “Copy to another location” is selected

b) “Unique Records Only” is checked.

3)In the “List Range” enter the range of values that needs to be filtered. For our example, we will be entering $H$8:$H$24

4) In the “Criteria Range” enter the same range of values i.e. $H$8:$H$24

5) In “Copy to”, specify a cell from where the unique values needs to be listed. Make sure you specify a dummy range area, else any existing values could get overwritten.

6) A completed example is shown below

7) Hit “OK”.

8) That is it..we are done..If you have followed the steps correctly, in the column Q the final filtered output would look like the one shown below


That concludes our simple example of eliminating duplicates from a list of values in Microsoft Excel.