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.
This example teaches you how to remove duplicates in Excel. Click any single cell inside the data set. On the Data tab, in the Data Tools group, click Remove Duplicates. Select the range of cells that has duplicate values you want to remove. Tip: Remove any outlines or subtotals from your data before trying to remove duplicates. Click Data Remove Duplicates, and then Under Columns, check or uncheck the columns where you want to remove the duplicates.
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.
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.
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.
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.
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”.
Select the rows that are duplicate and delete them.
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
Remove Duplicates And Original In Excel
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