One minute Excel Tutorial – Remove Duplicates


remove duplicate buttonData entry is a cumbersome job and often key punch operator ends up recording the same data again. Or if the data has been collected at different sources and on consolidation many similar instances can occur. This leads to repetition and duplicate entries of records. Well excel handles these nuisance as fast as one click!

Duplicates Vs Clicks = Click WINS!

Its super easy. Have your active cell within data range and click Data tab and in Data Tools group click Remove Duplicate button. A dialogue box appears. Make the selection of columns. Excel will then check for rows within the same column(s) that you selected whether any entries are exactly same and removes them. And it will not only remove that cell but an entire row.

Once the process completes a message will appear stating the records removed and unique values found. Example In the following figure same bank transactions data has been pasted on the right of the same worksheet. As you can see in the picture Instrument # 66750 has been entered twice and Instrument # 66860 has been entered thrice. So on running remove duplicate process only the Instrument No. column was selected and excel identified any duplications and removed them and left only a single record. As three duplicates have been removed you can see that by comparing the rows in the data ranges

remove duplicate single column

Columns selected here will work as a criteria to find similar or duplicate entries. If you select one column then any entries that are exact same only in that specific column will be used to remove the record and entire row will be deleted. If you select two columns than this time data of both columns will be collectively considered to identify duplicate i.e. repetition must exist in both columns now in the same row. Basically by adding more column you are making criteria more wider or bigger. Have a look at the following animation when more than one column is selected.

remove data multi column

Watch OUT! – Duplicate de Complicate

One down side of this method is that rows need to have exact same data. Even an alphabet here and there makes the data unique in the eyes of Excel where in reality it might be a duplicate. Have a look at following two figures. In this figure three rows have been marked with red border line. You can see in these three rows everything is same except in one instead of S. Haroon, Haroon is written. This makes this record unique.

remove data same data slight difference

Same is the case in our other example in the same data. Look for the two rows marked red. You might even miss the difference.

 remove data same data slight difference 2

If you look closely you will find that in one cell it is P, Babar and in the other it is P. Babar i.e. a difference of comma and a dot probably a mistake by data entry person and even that makes the two unique and not the same