How to Find and Remove Duplicates in Excel

How to Find and Remove Duplicates in Excel
How to Find and Remove Duplicates in Excel

How to Find and Remove Duplicates in Excel

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

Download 9 Excel Templates for Marketers [Free Kit]

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:

  1. Make sure you're on the Home tab.
  2. Select the entire table by hitting the button on the top left.Screenshot of an Excel doc with the entire sheet highlighted.
  3. Click Conditional Formatting → Highlight Cells Rules → Duplicate Values.Screenshot of removing duplicate values in Excel.
  4. 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.

Excel screenshot of formatting rules.

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

Excel sheet with duplicates highlighted.

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:

  1. 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:

    Excel sheet with duplicates counted.

  2. Insert the formula.

Now, you can insert the formula in the first cell under occurrences. You'll type in or copy and paste the formula. Then highlight A:A (we're going to replace this with your dataset), and click on the sheet in your Excel doc that has the data. Now, you can click on the top left to select the entire sheet, or you can just highlight the column or rows with your data.

For the second value, you'll want to go back to your second sheet, highlight the A2, and choose the value next to it on the left. In most cases, this will stay A2, A3, A4, etc.

See what this looks like in action here:

Excel doc with a count formula.

3. Remove duplicates with the remove duplicates feature.

Now it's time to remove the duplicates from your dataset.

Before you do this, I'd recommend duplicating/copying your dataset into another sheet or a whole other workbook altogether. You always want to keep your original data intact, even though you can use Excel to remove and filter the data you want. You don't want to lose data because of a wrong click.

Once you've made a copy of your data, it's time to remove the duplicates.

To remove duplicates, follow these steps:

  1. Select the worksheet that has duplicate values that you want to remove. Click Data → Table Tools → Remove Duplicates.Remove duplicates Excel function.
  2. Choose the columns where you want to remove the duplicates.

    In this case, I only want to remove duplicate blog titles. So I'll choose column D. I kept “My list has headers” checked because there are two rows of headers before the data starts on this sheet.

    Remember that Excel will remove the entire row where the duplicate value is.

    Remove duplicates column selector on Excel.

  3. Review data.

Excel will now show you have many duplicate values were found, removed, and how many unique values remain.

Screenshot of an Alert from Excel.

Now you can review your data. Comparing my first dataset to this dataset, you can see that all the rows containing the same blog title were deleted.

This is what the sheet used to look like:

Screenshot of a dataset in Excel.

And this is what it looks like now:

Screenshot of a dataset in Excel.

When you remove duplicates from Excel, it's important to note which column you want to remove duplicates from and remember that Excel is removing duplicates within a selected table range. You can highlight the whole workbook or you can just highlight the rows that have data in it.

Excel will automatically keep the first occurrence of the value.

Working on marketing reports or any marketing Excel sheet can leave you frustrated and banging your head against the wall (is it just me?). That's why using Excel templates and following these simple instructions on formulas can help you level up your game.

New Call-to-Action

Maggi Pier

Maggi Pier

Avid gardener, artist, writer, web designer, video creator, and Google my Business local marketing pro!