Remove Duplicates in Excel 2007

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.

  1. Select any cell in the list, or select the entire list
  2. On the Ribbon’s Data tab, click Remove Duplicates.
    • Remove Duplicates 02
  3. In the Remove Duplicates dialog box, select the column(s) that you want to remove duplicates from
  4. Check the box for My Data Has Headers, if applicable, then click OK.
    • Remove Duplicates 03
  5. 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.


______________

You may also like...

17 Responses

  1. […] the Contextures Blog today, I posted a short article on Removing Duplicates in Excel 2007, with a short […]

  2. Jason Morin says:

    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.

  3. Thanks for the questions, Jason. I’ve added answers in the blog post.

  4. kumar says:

    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

  5. Kumar, thanks for letting me know that the information helped you.

    Mike, you’re welcome too. It’s nice that I’m finally getting some respect around here. ;-)

  6. AlexJ says:

    “Sir Debra” – (you may be the first one!). Does that make you a Peer of the Realm?

  7. You’re right, AlexJ, and that does sound like quite an achievement, so perhaps I’ll take the weekend off. ;-)

  8. Hans Knudsen says:

    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?

  9. Rick Rothstein (MVP - Excel) says:

    @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.

  10. Contextures Blog » Excel AutoFilter or Advanced Filter? says:

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

  11. Kurt says:

    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.

  12. Giridhar B says:

    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

  13. Bhaskar Pawar says:

    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.,

  1. March 9, 2012

    […] which we augmented with a list from the web. It was sliced and diced using Text to Columns and Remove Duplicates from the Excel 2010 Data […]

  2. February 22, 2013

    […] Is the problem that the data is duplicated in your original data? If so, please see this link. Remove Duplicates in Excel 2007 | Contextures Blog Be sure to use this on a COPY of your original data. You should also delete any data such as dates […]

  3. November 26, 2013

    […] Excel 2007, the Remove Duplicates tool was introduced, and I wrote about that a few years ago. It’s a quicker way to strip duplicates out of a list, but it works on the […]

Leave a Reply to Kurt Cancel reply

Your email address will not be published. Required fields are marked *