How To Delete Duplicates In Excel

Deleting Duplicates In Excel
Deleting Duplicates in Excel spread sheet

We have multiple ways to delete duplicate values in excel.  We have remove duplicate option itself in 2007 Excel version or higher versions.  If we need to delete manually in the sense, we have to follow below methods to do this.

1st Method:

  • Sort data either in A to Z or Z to A methodology in the required column
  • Next to that column keep formula = currentcell = Below cell.

(Note: For example if you need to identify duplicate values in the 1st column. Then in the 2nd column, in cell B1 you need to keep the formula as =A1=A2, then drag this till end of the data of the 2nd column)

  • If you get TRUE as result in the cells of 2nd column, those all are duplicates. You can just filter those to delete.

2nd Method:

Assume that you have to find duplicates in the 1st Column, then  just select B1 cell and keep formula  as “=Countif(A:A,A1). Then drag this till ending data cell of 1st Column. If you get more than one as number in the 2nd Column, those all are duplicates. Now you can delete by filtering

3rd Method:

We have direct option as remove duplicates, there you can just select and go to Data Menu -> Select Remove duplicates option to Choose column ->Click Ok. Then also it deletes duplicates.  Here you can use shortcut as Press ALT +A +M to get remove duplicates window after selection data by CTRL+A.


Please enter your comment!
Please enter your name here