Remove Duplicates Command in Excel

In Excel 2003 and earlier versions, you can use an Advanced Filter to remove duplicates. In Excel 2007, there’s a new command on the Ribbon to make it easier to remove duplicates from a list.

Be careful with the Excel 2007 Remove Duplicates feature though – it really removes the duplicates. If you use an Advanced Filter instead, you have the option of hiding duplicates, or creating a unique list in a new location.

How It Works

Update: Jason Morin asked a few questions about the Remove Duplicates feature, and how it works, so I’ll answer the questions here. (Thanks Jason!)

1) Does the new Duplicates capability discern between text strings and numerical values that look the same on the screen?

No, it treats the text strings and numbers the same. If the list has a 10 and a ’10, they’ll be treated as duplicates. There isn’t a settings option that I can see, where you can adjust this. In the Advanced Filter feature, those would be seen as 2 unique items.

2) 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.

No, those won’t be treated as duplicates, because the space character in the second entry makes it different. Advanced Filter would do the same.

3) How does it decide WHICH duplicate to remove in the data set?

The first instance of each item is left, and all subsequent entries are deleted.

4) 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.

You can use the check marks in the Remove Duplicates dialog box, and select all the columns you want to include. Only if all the included columns are duplicated, will an item be removed. Advanced Filter works the same way, but without the check marks.

Remove Duplicates

In this example, the list in cells A1:A10 contains a few duplicates.

Remove Duplicates 01

Follow these steps to remove the duplicates.

  • Select any cell in the list, or select the entire list
  • On the Ribbon’s Data tab, click Remove Duplicates.

Remove Duplicates 02

  • In the Remove Duplicates dialog box, select the column(s) that you want to remove duplicates from
  • Check the box for My Data Has Headers, if applicable, then click OK.
Remove Duplicates dialog box
Remove Duplicates dialog box
  • A confirmation message will appear, showing the number of duplicates removed, and the number of unique items remaining. Click OK to close the message.

RemoveDups04

Watch the Remove Duplicates Video

Here’s a very short video that shows the steps to remove duplicates in Excel 2007.

______________

17 thoughts on “Remove Duplicates Command in Excel”

  1. A few questions for those who still use an older version of Excel:

    1) Does the new Duplicates capability discern between text strings and numerical values that look the same on the screen?
    2) 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.
    3) How does it decide WHICH duplicate to remove in the data set?
    4) 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.

  2. dear sir 15th, 2009 6:30am
    thankyou for your step by step explaining about how to remove duplicate.
    and also to inform to you my long pending question about this came to end in your valuable
    answer

    thank you
    kumar

  3. Excuse me for commenting to a more than half a year old post of yours but I am about catching up on all your instructive and easy to understand blog posts (thank you) and had come to December 2009.

    If I use Excel 2007 Remove Duplicates on your above list (Pen, Pencil etc.) I get the following:
    Pen
    Pencil
    Binder
    Gel Pen
    Open boxes
    and a message box saying: 4 duplicate values found and removed; 5 unique values remain.

    Now with the original list in A1:A10 I use Conditional Formatting to highlight unique values. The following items get highlighted:
    Gel Pen
    Open Boxes

    That is 2 unique values. Seems to me there is some inconsistency here or do I completely miss something obvious?

  4. @Hans,

    I think you are trying to compare apples to oranges… the two options you refer to are identifying different things with similar sounding names. When you remove duplicates, you delete the 2nd, 3rd, etc. occurrence of the item leaving the first one in the list. So, Pen appears three times in the list… the first Pen is kept and the 2nd and 3rd ones (the duplicates) are removed. What **remains** is a list of items that do not repeat within the list (making the resulting list a list of unique items). On the other hand, the Conditional Formatting option to identify Unique values in the original list is not removing items until unique values remain; rather, it is simply identifying items that do not repeat within the list. These “unique” things are not the same “unique” things.

  5. […] 2007 introduced the Remove Duplicates feature, but I still use an Advanced Filter to create lists of unique items. The Remove Duplicates […]

  6. Is there a way to delete duplicates at a row level rather than at a column level?
    A1 = Pen
    B1 = Pencil
    C1 = Pen
    D1 = Binder
    E1 = Pencil
    F1 = Open Box
    At the end result, I just want Pen, Pencil, Binder, Open Box at a row level.

  7. Hi,
    My List has employees prior experience details in multiple rows (Maximum of 10 companies details).
    I need to keep only the latest 3 company details in my list and the rest need to delete from the list.
    Is there any way to get this?
    Regards
    Giridhar

  8. plz sir how to creat the state list linked to district list and linked to madal list and Habitation list in excel 2007 by combobox or active x controle or drop down list plz solu my prob.,

Leave a Reply to Interactive Video Experiment Cancel reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.