Remove duplicates is an inbuilt button in excel which is used to remove any duplicity available in the data, it is a type of data tool so it is available in the data tab of data tools section, this method finds out duplicate data available in any one column or multiple columns to remove the duplicate values, there is one important thing which we need to keep in mind is that the first row with the values remain while the other duplicate values in other rows are deleted.
Excel Remove Duplicates (Table of Contents)
Remove Duplicates in Excel (2007, 2010, 2013 & 2016)
If you apply to remove duplicates in excel it only affected the selected data, any other data outside the selection range of cells or table are not affected due to this. Still, for safety purpose, it is recommended and a good idea to copy the original data to another excel sheet before removing duplicate values.
When you work on large data set or when records are put into the excel sheet from more than once or you’re copy-pasting the records from multiple sources, data duplication can happen. You might also find duplicates for one or more columns, such as a name or ID column, with the same data and want to limit the records to unique data for some fields in the excel sheet.
How to Remove Duplicates in Excel?
Let’s understand the working of removing duplicate values from a given dataset with the below examples. You can remove duplicate records in a very simple and easy way.
Example #1 – Using the Excel Remove Duplicate Command
Select the data set that you want to delete the duplicates entries from, and click on the remove duplicates button placed at “Data tool” within the Data tab in the Excel ribbon.
Once you click on a button you will get the below options, you will get the Remove Duplicates dialog box, as shown below:
Here you will get columns name on which you want to apply the delete duplicates in excel. In the given excel sheet example, you only want to delete only one record. And, if you want to delete duplicate in excel from all the columns then leave all three fields checked within the dialog box as shown in the above screen sheet.
- Once you checked all the options in the dialog box, click OK, it will delete the duplicate value in excel from the given data set and output will show the unique records remaining only.
Example #2 – Remove using the Excel Advanced Filter
Let’s consider the same data set. Now this time we will use the Excel advanced filter for removal of duplicate records.
How to remove duplicate rows using the Advanced Filter:
First, select the data that you want to delete the duplicates from columns E to G in excel sheet then select the Excel Advanced Filter option from the “Data tab” at the top of your Excel sheet as shown in the below dialog box:
The above Dialog Box Showing Options for the Excel Advanced Filter.
Here are the steps as follows:
- Select the option Copy to another location;
- Select raw data in List range option.
- In the Copy to the field, enter the location where you want to copy the new unique data.
- Note that this location must be in the current excel sheet. In this example, cell I4 of the current excel sheet “Sheet1” has been selected as the ‘copy to’ location;
- Check the “Unique records” only box as shown in the below image.
- Then Click OK.
The resulting spreadsheet, with the new data list from the column I4, is shown below: Results of Removing Duplicate Cells Using the Excel Advanced Filter:
Remove Duplicates Using VBA.
Suppose we have the data sets located in the excel sheet range from A3 to D19 and want to remove it by the macro. Then run the below macro to remove the duplicate data from a given data set. The following code sample removes duplicates with the first 3 columns:
ActiveSheet.Range(“A4:C19”).RemoveDuplicates Columns:=Array(1,2,3), Header:=xlYes
Things to Remember
- Copy the original range of cells or table to another worksheet or workbook before removing duplicate values.
This has been a guide to Removing Duplicates in Excel. Here we discuss how to delete duplicates in excel using Remove Duplicate Tool and Advanced Filter along with excel example and downloadable excel templates. You may also look at these useful excel tools –