Excel Cheats: Part Three

Welcome to Part Three of the series Excel Cheats: What You Didn’t Know! In this post I will show you how to quickly and effortlessly eliminate duplicate items from your worksheet. Let’s Begin!

Removing Duplicate Items
Let’s say you have a class roster. This class roster contains both students’ first and last names in separate columns. You know that there are duplicate items in your list but it is too long for you to manually go through and remove duplicates. Excel 2010 makes this process quick and easy! Go to the Data tab, and select Remove Duplicates from the Data Tools section.

Remove Duplicates in Excel 2010Next, a window will pop with a variety of options. In my example you can see that the box next to My data has headers is checked (see image below). This is because I have a row of headers: Last Name, First Name, Gender, and Age, that should not be removed. By letting Excel know that your first row is headers, it is also easy to identify which columns you want to check/remove duplicates.If your worksheet does not have headers, then you can remove the check mark from this box and column letters will be used instead for identification purposes.

Remove Duplicates OptionsIn this example, I will click on the boxes next to Last Name and First Name. This is because I am only looking for duplicates among those items. Also, since it is possible that more than one student may have the same last name or the same first name, I DON’T want to just search for duplicates by last name or just by first name. Checking off both will ensure that you only remove duplicates if BOTH the first and last name are the same. Click OK when you are done selecting the appropriate columns.

Remove Duplicates MessageYou should receive a message letting you know how many duplicate items were removed. You now have a list without duplicate items! A Final Thought: It is always a good idea to save both versions of your document. The first version (without duplicate items removed) and the second version (with duplicate items removed). This way you don’t lose any important data and can always go back to the original file at any time. Good Luck!