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.
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.
- 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.
- A confirmation message will appear, showing the number of duplicates removed, and the number of unique items remaining. Click OK to close the message.
Watch the Remove Duplicates Video
Here’s a very short video that shows the steps to remove duplicates in Excel 2007.
______________
[…] the Contextures Blog today, I posted a short article on Removing Duplicates in Excel 2007, with a short […]
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.
Thanks for the questions, Jason. I’ve added answers in the blog post.
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
Yes…Thank you Sir.
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. 😉
“Sir Debra” – (you may be the first one!). Does that make you a Peer of the Realm?
You’re right, AlexJ, and that does sound like quite an achievement, so perhaps I’ll take the weekend off. 😉
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?
@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.
[…] 2007 introduced the Remove Duplicates feature, but I still use an Advanced Filter to create lists of unique items. The Remove Duplicates […]
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.
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
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.,