Check the box for My Data Has Headers, if applicable, then click OK.In the Remove Duplicates dialog box, select the column(s) that you want to remove duplicates from.On the Ribbon’s Data tab, click Remove Duplicates.Select any cell in the list, or select the entire list.In this example, the list in cells A1:A10 contains a few duplicates.įollow these steps to remove the duplicates. Advanced Filter works the same way, but without the check marks. Only if all the included columns are duplicated, will an item be removed.

You can use the check marks in the Remove Duplicates dialog box, and select all the columns you want to include. The first instance of each item is left, and all subsequent entries are deleted.Ĥ) I assume if I’m working with record set (>1 column), I need to concatenate data from columns to create a unique identifier for each record, then run the Duplicates on the new column I created. Advanced Filter would do the same.ģ) How does it decide WHICH duplicate to remove in the data set? No, those won’t be treated as duplicates, because the space character in the second entry makes it different.

In the Advanced Filter feature, those would be seen as 2 unique items.Ģ) What about non-visible characters in the cell? Does it consider “Pen” and “Pen ” the same? As a user I would view this as a duplicate, but Excel may not. There isn’t a settings option that I can see, where you can adjust this. If the list has a 10 and a ’10, they’ll be treated as duplicates. No, it treats the text strings and numbers the same. (Thanks Jason!)ġ) Does the new Duplicates capability discern between text strings and numerical values that look the same on the screen? Update: Jason Morin asked a few questions about the Remove Duplicates feature, and how it works, so I’ll answer the questions here. If you use an Advanced Filter instead, you have the option of hiding duplicates, or creating a unique list in a new location. In Excel 2007, there’s a new command on the Ribbon to make it easier to remove duplicates from a list.īe careful with the Excel 2007 Remove Duplicates feature though – it really removes the duplicates. In Excel 2003 and earlier versions, you can use an Advanced Filter to remove duplicates.

How to remove duplicates in excel