Remove Duplicates and Bounces with Excel

This feature requires Manager user level or higher.

MailChimp was built to deliver email, but not to clean lists. Although we do manage bounces for you as you send each campaign, we don't provide a tool for cleaning old lists. We recommend that you reconfirm your list before you use it with MailChimp, or clean it with Microsoft Excel before you upload it to MailChimp.

Below, you'll learn how to use Excel to clean your list.

Remove Duplicates

To remove duplicates from your list, you'll paste your list into one column and run a formula to copy addresses without duplicates into a new column.

  1. Open Microsoft Excel.
  2. Paste your entire list into Column A.
  3. Sort the entire column alphabetically, descending (A to Z).
  4. Click the "B" column label to highlight all of Column B.
    Highlight Column B in Excel file
  5. With Column B highlighted, input the following formula in the fx field.
    =IF(TRIM(A1)=TRIM(A2),"",TRIM(A1))
  6. With the entire Column B still selected and the formula in the fx field, click Edit in the navigation.
  7. Choose Fill in the drop-down menu, and choose Down. This loads Column B with the addresses from Column A, with duplicates already removed.
  8. To copy this column into a new spreadsheet, click to select Column B.
  9. Click Edit, and choose Copy from the drop-down menu.
  10. Create a new spreadsheet.
  11. In the new spreadsheet, click Edit.
  12. Choose Paste Special from the drop-down menu, and choose "Values."

Further edit or sort this spreadsheet, and save it as your final list. Note that if your list is very large, and you had a lot of duplicates, you may see a number of blank fields at the top of your list. Scroll down to find your email addresses.

Remove Bounces

You'll use a similar process to remove bounces. Export cleaned addresses from your MailChimp lists, and paste those into Excel. Then, you'll remove duplicate addresses, which in this case, are your bounces.

  1. Open Microsoft Excel.
  2. Start in cell A2 (Column A, Row 2) and paste your entire email list into Column A. Leave cell A1 blank.
  3. Log in to MailChimp and export your cleaned addresses. Paste those into Column A, below your master addresses.
  4. Sort Column A alphabetically, descending (A to Z). Sort the entire column alphabetically (A > Z).
  5. If Excel deletes your A1 cell after you sort, add a row at the top to make sure cell A1 is blank again.
  6. Click cell B2 and input this formula into the fx field.
    =IF(OR(TRIM(A1)=TRIM(A2),TRIM(A2)=TRIM(A3)),"",TRIM(A2))
  7. With cell B2 still highlighted and the formula in the fx field, click and drag the bottom-right corner of the B2 cell down to the bottom of your list.
  8. Column B should now display all the addresses from Column A, with duplicate addresses removed.
  9. To copy this column into a new spreadsheet, click to select Column B.
  10. Click Edit, and choose Copy from the drop-down menu.
  11. Create a new spreadsheet.
  12. In the new spreadsheet, click Edit.
  13. Choose Paste Special from the drop-down menu, and choose "Values."

Further edit or sort this spreadsheet, and save it as your final list. Note that if your list is very large, and you had a lot of duplicates, you may see a number of blank fields at the top of your list. Scroll down to find your email addresses.

Was this article helpful?
What can we do to improve articles like this?