How to Remove Duplicates in your Email List using Excel

Published by: Erica Sanford    |   Category: "How To" Articles, Blog, Email List    


How to Dedupe or Remove Duplicate Emails using Excel

Deduping an email list using Excel is easier than you think.

Since we are asked quite often about removing duplicates in an email list, we thought it might be time to post a simple “How To” guide explaining how easy it really is to dedupe an email list using Excel.

Using Excel to remove, view or hide duplicates in 3 steps or less.

  1. The simplest and quickest way of removing duplicates in Excel is clicking the Remove Duplicates button in the Data Tab

De-Duping Emails using Excel

  1. After clicking Remove Duplicates, a selection box pops up. You will need to choose the column(s) you want to filter by. I chose every column, so only one entry from each email remains.

Deduping emails selecting column

3. Click OK in the selection box. As you can see in the image below, Excel will tell you how many duplicates it found as well as how many unique values remain. The duplicates will be removed with only the first instance of the records remaining.

This will treat the first instance of the records as the master value and delete all others.

Finding Duplicate Emails

What if you want the first instance of the duplicate record to not be in your list either? Or, you are interested in viewing each duplicate instance and determining for yourself if you want to keep it? Luckily this is made simple with conditional formatting.

  1. Again with your data selected go to Home → Conditional Formatting → Highlight Cell Rules → Duplicate Values.

Email Conditional Formatting

  1. The box below will appear. You can leave the selection in the box as is with “Duplicate” chosen and click OK. All of the duplicate values will now be highlighted in red for you to see. If your list is short like this one you can go through and manually delete them.

Selecting Duplicate Emails

If your email list is larger, follow these steps to sort your formatted list for easy viewing.

  1. Go to Data → Sort. The box shown in the image below will appear.

Sort Duplicate Emails

  1. Choose the appropriate Column, Sort On, and Order then click OK. You will now see that all your highlighted duplicates have moved to the top of the list grouped together for easy deletion!

The previous two options were easily done, but maybe you don’t want to delete the duplicates from your list – Maybe you just want to hide them. Here is way to quickly dedupe your data by hiding the rows that contain duplicate data.

  1. Go to Data → Filter → Advanced Filter. Another dialogue box will appear.

Filter Email Addresses

  1. With your cursor in the “List range” text box, highlight the range you want to hide duplicates from. In my case I selected B2-D17. Also, make sure you have the“Unique records only” box checked. Then click OK. Only unique values will remain visible, but remember since this was only a filter,  the values have not been deleted; they are just hidden in rows.

Now that was pretty easy – Wasn’t it?

Remember, even if you have a list of unique emails, you should consider using Purelist to scrub and clean your emails or have your email list cleaned and validated before sending to it. Having undeliverable emails in your list or sending to complainers, spam seeds or dead domains will only cause your IP Reputation to be damaged. Keeping your email list clean, valid, deliverable and free from spam seeds or BOTS will help you deliver your marketing emails to your users inbox on a more consistent basis.



Sorry, comments are closed for this post.

Comments are closed.