remove duplicate rows in excel

Many times, while working on the Microsoft Excel spreadsheets the users encounter multiple duplicate rows. Sometimes, it happens accidentally whereas sometimes while working on multiple spreadsheets, the problem of duplicate rows arise in Excel.

For removing the duplicate rows quickly and effectively, we will discuss a few simple tricks in this article. These tricks will work in Microsoft Office 365 Excel version.

1. Using ‘Remove duplicates’ option

The Microsoft Excel software has a pre-installed option of ‘Remove Duplicates’. This option is present under the ‘Data‘ tab. By clicking this header, you will further see the ‘Remove Duplicates‘ option. After clicking the “Remove Duplicates” option, the duplicate rows will be automatically deleted.

data tab of excel

By using the default option of ‘remove duplicates’, if your table has headers like in the above case, then you will select the option of ‘My data have headers‘. By using this option, your first row will not be considered while removing duplicates.

Select one or more columns that contain duplicates. After clicking the ‘Ok’ button the duplicate rows will be removed automatically.

select columns

After deleting the duplicate rows, you will get a notification about how many duplicate values were deleted and how many unique values remain. In this way, you can easily remove duplicate rows in Excel.

Display output
Display output

2. Using a formula

You can also check for duplicate rows by using a formula comprised of ‘If-Else‘ condition. This “If else condition” will check all the row values and if the values are same, it will set the value to “duplicate”. Similarly, If the values are not duplicate then the value in the “duplicate column” will set to “not duplicate”. This formula will use simple equal signs among different cell values of two rows to compute duplicates.

=IF(AND(A2=A3,AND(B2=B3,AND(C2=C3,AND(D2=D3)))),"duplicate","notduplicate")

formula to remove duplicate rows
Apply formula to remove duplicate rows

By dragging the column downwards, the formula will be applied to whole table. As the first row’s values are compared to the row below it, therefore we will write “notduplicate” in the first cell of “Duplicate column”.

display duplicate in column
Display duplicate results in column

Select the rows that are duplicate and delete them.

delete duplicate rows
Delete duplicate rows

After deleting the duplicate rows, you will get a table with no duplicate rows and all the values of the table will be unique.

Conclusion

Removing duplicate rows in Microsoft Excel is a not tedious task. With the help of using the two methods mentioned in this article, you can ensure that your excel table will have unique values and no duplicate rows. You can identify and delete duplicate rows easily