How to Find and Remove Duplicates in Excel

Download 9 Excel Templates for Marketers [Free Kit]

“I’ve never been a natural, all I do is try, try, try.”

These Taylor Swift lyrics, in the song “mirrorball“, perfectly explain my relationship with numbers, math, and anything concerning data analytics.

However, as a marketer, data analysis is one of the most important aspects of my job. But like most marketers, who prefer strategy and creativity, numbers and Excel reports don’t come naturally to me.

That’s why it’s important to know how to work in Excel and find shortcuts to help make the process easier.

Today, we’ll dive into one of those processes — how to find and remove duplicates in Excel.

1. Find and highlight duplicates in Excel through conditional formatting.

The first step of removing duplicates will be to find them. An easy way to do this is through conditional formatting.

You can do that by following these steps:

Make sure you’re on the Home tab.
Select the entire table by hitting the button on the top left.
Click Conditional Formatting → Highlight Cells Rules → Duplicate Values.
In the area titled “Format With”, change how you want duplicates to be highlighted. You can choose highlighting, bolding text, changing the color of text, etc.

And voila. Your duplicates are now highlighted. It should look something like this:

2. Count duplicates in Excel.

Now that you’ve found your duplicates, you might want to count them and see how many there are, especially if you have a large dataset.

To do this, you can use this formula =COUNTIF(A:A, A2). The formula means that Excel is going to count how many times a certain value is used in a certain place.

The column A:A stands for the data table you’re looking at. This will probably be a different value on your Excel sheet. Then, A2 references the value you want to count the frequency of.

To do this, follow these steps:

Create a new sheet in your Excel doc.
I found that the easiest way to count duplicates in Excel is to create a new sheet in your Excel workbook.

Then, copy and paste the column that you want to count duplicates in. In the example below, I copied and pasted the blog titles from the editorial calendar to see if there were any duplicate titles.

Then, create another column for “occurrences.” This is where we’ll put the formula. Your new sheet should look something like this:
Insert the formula.