The filter has to be reapplied.Īnother limitation is that while your advanced filter can be in a different location on the worksheet, it must be on the same worksheet as the original dataset. One drawback with advanced filters is that the filter isn’t dynamic, meaning that the extracted list does not update itself if the values in your original dataset change.

Since Eugene Ross and Bonnie Watson are duplicated, the duplicate rows are deleted and a new dataset is created beginning at cell F1.

In the Copy to field, enter the cell reference where the new dataset will begin.

Since we aren’t looking to extract or filter special values, the Criteria range field should be empty.

Confirm or adjust the data area by double-checking the list range.

From the Advanced Filter window, selecting the Copy to another location radio button gives you the option of placing the new dataset somewhere else on the active worksheet.

This will open up the Advanced Filter window.

Go to the Data tab on the ribbon and click Advanced within the Sort and Filter command group.

You may want to do this to compare the “before and after” datasets side by side. The advanced filters feature enables you to copy the data right there on the worksheet just by clicking a button. Note: If you’re nervous about deleting data, you may want to copy the worksheet first to retain the original data as this method will result in duplicates being permanently deleted. To demonstrate this, we will tick only the First Name column.Įxcel removed three duplicate values (Eugene, Bonnie, and Robert) and their associated rows, leaving only rows with unique first names. Any row where the values in these columns are identical will be reduced to a single row, removing any duplications. Excel will evaluate each row based on data across all values in the ticked columns.

Tick the columns where you want to search for and remove duplicates.

If your data has headers, ticking the “My data has headers” box in the Remove Duplicates window will ensure that your first row isn’t part of the removal process.

Click anywhere within the dataset where you’d like Excel to delete duplicates.

The Data tab carries a Remove Duplicates icon right on the ribbon.

Removing duplicates in Excel is often just three or four clicks away. But what about if you wanted to go a step further - actually removing duplicate values? To do that, you can choose from any of the methods below. If no further action is required, you’re good to go. From the Duplicate Values window, with Duplicate selected in the dropdown menu, choose the format you would like to be applied to cells that are duplicated.Įxcel finds two pairs of duplicates and highlights them.Click Conditional Formatting > Highlight Cells Rules > Duplicate Values. Now the only thing needed is to apply conditional formatting to column E. In the example below, we used the TEXTJOIN function. So we’ll create a kind of “working column” where you can use any concatenation method to join all the values in a row into a single cell. But our real concern is to identify rows that are true duplicates - identical in every way. In the following dataset, each column has a duplicate value somewhere (there are two Roberts, two Longs, four Jersey Cities, and two FLs). If you only want to identify duplicate values in Excel but not remove them, conditional formatting may be just the feature you’re looking for.ĭepending on the type of duplicates you’d like to isolate, you may consider creating an extra column to join the entire row or range of cells into a single string.

Delete duplicate rows in excel