If you’re nervous about deleting data, you may want to copy the worksheet first to retain the original data. 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. Remove duplicates with the Remove Duplicates command

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.

Find and merge duplicate rows in excel