Many of us face difficulty when there are many duplicate rows in our spreadsheet. Sometimes, we accidentally copy rows, or sometimes, we are making a composite spreadsheet and encounter duplicate rows. This a repetitive and time consuming task but there are several tricks that make the task of deleting duplicate rows a simpler one. This article will introduce you some easy ways to remove duplicate rows in Excel.
Remove duplicate rows in Excel using Remove Duplicates Function
- Open the excel sheet for which you want to remove duplicate rows.
- Select the range for which you want to remove duplicate rows. For example, if you want to delete all duplicate rows, select entire sheet by using Ctrl + A.
- Go to Data tab, and click Remove Duplicates.
- You will see Remove Duplicates dialog box and leave all checkboxes checked under the list box. An important point to note here is that if your data has headers; please check My data has headers option.
- Click OK and a prompt box will appear to tell you that the duplicate rows have been deleted except for the first identical row.
- Open your database in Excel and select the sheet.
- Select the data for which you want to check for duplicates.
- Click Data tab and then go to Advanced Filter in the drop down menu. It is located under “Filter” or “Sort & Filter.”
- Check the box “Unique Records Only.” This will hide duplicate values.
- You can also specify the area of sheet where you want your filtered cells to be taken.
- You can save the list under a new name to delete duplicates completely.
We hope that this post helps you to remove duplicates easily from your database in Excel. If you know other ways to remove duplicate rows in Excel, let us know about them in the comments.