Does your Excel worksheet has a lot of duplicate entries? Follow these simple steps to quickly find and remove duplicate entries in Excel.
While working on a big Excel sheet, it is common to have a few duplicate entries. The chances of having duplicate entries in an Excel worksheet are particularly high when the data is being generated from multiple similar sources or when the sheet is being worked on by multiple users. For example, recently I had to go through an Excel sheet that has a lot of duplicate entries due to miscommunication between users adding data. In those kinds of situations, you first need to find all the duplicates, see if they are safe to delete, and then remove them from the worksheet. Though sounds complicated, it is pretty easy to do through the power of Excel options.
In my recent post, I wrote a simple way to find and highlight duplicates in Excel. So, go through the linked article to analyze the duplicate entries. After that follow the below steps to remove all duplicate entries in Excel.
Important note: Before removing duplicates in Excel, I recommend you create a backup of the Excel file you are working on. If something goes wrong, you can restore the data from the backup file. It is better to be safe than sorry.
Remove duplicate entries in Excel
To remove duplicate entries from an Excel worksheet, we are going to use the “Remove Duplicates” feature. This feature will scan the selected columns and removes all instances of duplicate rows except the first instance. Here is how to do it.
- Open the Excel spreadsheet.
- Select the columns with duplicate rows.
- Click on the “Data” tab at the top.
- Click on the “Remove duplicates” button.
- Make sure the appropriate column checkboxes are selected.
- Click the “Ok” button.
- Excel will remove all duplicate entries from the selected range.
First, open the Excel file that has the duplicate data you want to remove. Once opened, select all the columns that have duplicate data. In my case, I’m selecting the first two columns as they contain a couple of duplicate rows.
After selecting the columns, click on the “Data” tab at the top and then click on the “Remove duplicates” option.
Now, make sure the columns are selected in the Remove Duplicates dialog box. Next, click on the “Ok” button.
That is all. As soon as you click the button, Excel will remove duplicates.
One thing to remember is that the above method will only look for and remove duplicate rows. It will not remove individual duplicate cells. If you are looking to remove duplicate cells, you have to select individual columns and then follow the steps. That way, the remove duplicate function will scan the selected column and remove all instances of a duplicate cell except the first instance.
That is all. It is that simple to remove duplicates in Excel. If you are stuck or need some help, comment below and I will try to help as much as possible.
