How to Find and Remove Duplicates in Excel

Jun 15

No matter which profession you are in, one annoying enemy is always present — duplicates. They multiply themselves like clones, and no matter how many times you scroll and press Delete, they always seem to be never-ending.

Arrggh!

Today we’ll discuss simple and fast methods to find and remove these same values from our Excel worksheets.

Exterminate these pesky identicals, you will!

Here’s how…

Dealing with duplicates

Dealing with duplicates

Click here to get the Excel file to follow along.

Objectives

Typically there are 2 main tasks that we want to do with these duplicate values:

  • find and flag out the clones
  • clean up a list to have a unique list of values

Highlighting Duplicates

Highlight Duplicates

Let’s start with the first task — to find where the duplicates are and flag them out.

We’ll use the list below as our example throughout this article:

Apple
Pear
Banana
Apple
Watermelon
apple
ApPLe
Mango

Step 1 – Select the list of values.

Step 2 – Go to Home | Styles | Conditional Formatting | Highlight Cell Rules | Duplicate Values.

Conditional Formatting - duplicates

Step 3 – In the pop-up window, choose the highlight style (I chose Yellow Fill with Dark Yellow Text). Press OK.

Format Style - duplicates

Step 4 – All the clones will be highlighted, whether they are the first guy, in caps or lowercase. ALL of them are flagged out.

Highlighting duplicates

Removing Duplicates

Removing Duplicates

Shoot the clones down

Removing duplicates is a skill that beginners so often ask about. Now it’s time to say goodbye to the duplicates.

Step 1 – Select the list of values.

Step 2 – Go to Data | Data Tools | Remove Duplicates.

Step 3 – Select OK when the pop-up window appears.

Step 4 – You feel great that the duplicates are removed, leaving behind the leader (first instance of the value, in this case, in Cell A1).

Duplicates Removed

Comparing Lists for Duplicates

The third most common task when dealing with duplicates is to determine if a second list has items that are already in the first list.

To do that, we can simply use the COUNTIF function. Make sure that you lock the range with $.

Cell F2 =COUNTIF($A$1:$A$8,E2)
Comparing Lists

Using COUNTIF to compare 2 lists

The COUNTIF function simply counts the number of times this item has appeared in the first list. If it shows 0 like for grapes, then it means that grapes are not in the first list.

COUNTIF also shows that apple has been repeated 4 times in the first list, while pear does not have any duplicates in the first list.

Conclusion

Duplicates are irritating and they often can multiply out of control. Learning to control them is a huge step towards Excel mastery.

  1. Finding duplicates by using Conditional Formatting found in the Home tab.
  2. Removing duplicates using the Remove Duplicates feature found in the Data tab (Many of my friends always ask me why are these 2 features found on separate ribbon tabs).
  3. Comparing lists for duplicates by using the COUNTIF function to count the number of duplicates.

Tip: In this subsequent article, we also discussed how to use the new Power Query tool to merge multiple lists into a single list, and remove all instances of duplicate entries