You can leave the criteria range blank field.Excel will guess the range of data, but you can adjust it in the List range.Filtering the list in place will hide rows containing any duplicates, while copying to another location will create a copy of the data. You can choose either filter the list in place or Copy it to another location.Step 3: This will open the Advanced Filter window. Step 2: Go to the Data tab and click on the Advanced filter Command button. NOTE: If you select any cell within the data set, Excel will automatically select the entire data range when you activate the advanced filter. Step 1: Select the data that you want to remove the duplicates. Follow the following steps to remove duplicate rows by using the Excel Advanced Filter. This gives you a data set that contains the first occurrence of a duplicated row but does not contain any further occurrences. The Excel advanced filter has an option that allows you to filter unique records (rows of data) in a spreadsheet and copy the resulting filtered list to a new location. This is possible from the advanced filters. There is also another way to remove any duplicate values in your data from the ribbon. When you press ok, Excel will remove all the duplicate values it finds and give you a summary count of how many values were removed and how many values remain.Įxcel deletes the duplicate rows, as required and display only the remaining unique records in the sheet. Step 5: Once you have ensured that the required fields are checked in the dialogue box, click on the ok button.
There are also handy Select All and Unselect All buttons above you can use if you’ve got a long list of columns in your data.
Follow the following steps to remove duplicate rows by using this command. When you use the Remove Duplicates feature, it will permanently delete the duplicate data.īefore you delete the duplicates, it’s good to copy the original data to another worksheet, so you don’t accidentally lose any information. There is a dedicated command to do it in the ribbon. Removing duplicate value in data is a very common task. Remove Duplicate Rows using Remove Duplicate Command In the box next to values with, pick the formatting you want to apply to the duplicate values. Step 4: Then, a Duplicate Values box appears. This seems a little more messy, because worksheets with a lot of cross referencing may have the formulas inadvertently altered.How to Delete Duplicate Rows in Excel 665×555 39.8 KB However, it involved copying data and deleting the original list. Turn on the filter on the column with the countif functionįor reference, Microsoft did provide a similar solution to the problem.Copy the formula down to all rows to be filtered.Do not lock the row reference 1st row reference, or it will not work properly. In this example, it is =COUNTIF(A2:A$21,A2) Note that the 2nd row reference is locked (A$21), so that no additional data will be unintentionally selected. If there is more than 1 match in the search area, it is a duplicate! The countif function is used to count the number of times a certain value came up. Find an empty column, (Column D in this example), and type in the formula.Turn off the filter – only unique cells are remaining.Highlight and delete rows with blank cells.Highlight Column D and turn on the filter (In Excel 2010, its Data -> Filter button).Copy and paste what you type till the last unique row.Pick an empty column on the right (Column D in the example), type in anything on a unique row.For “List Range”, select the data to be filtered, in this example, column A.Countif to detect any duplicates for deletion.Excel’s filter function allows the user to filter out rows that are duplicates.There are 2 “clean” methods to solve this problem without moving the cells: