How to Convert CSV to Excel for Mailchimp

To convert CSV to Excel for Mailchimp, open the CSV in Excel, clean it up — standard headers, one email per row, no duplicates, no junk rows — and then save it back as CSV for the Mailchimp import. Excel is the workshop, not the destination: Mailchimp imports CSV files, so the Excel step exists to fix the problems that make Mailchimp reject or mangle your list. (If a file needs no cleaning at all, the free CSV to Excel converter converts it in one step — but a contact list almost always benefits from the cleaning pass below.)

Mailchimp is picky about contact files for good reason. A single malformed email, a header it does not recognise, or a few thousand duplicate rows can turn a routine import into a half-lost list — contacts silently skipped, tags applied to the wrong people, or an audience full of role addresses that bounce. Ten minutes of cleaning in Excel prevents almost all of it, and this guide walks through the exact checklist Mailchimp expects.

What Mailchimp Actually Wants

Mailchimp's audience import reads a CSV with one contact per row and one field per column. The first row is the header row, and Mailchimp matches each header to an audience field: Email Address, First Name, Last Name, Phone Number, Address, plus any tags or groups you want to apply. Headers that do not match a field get skipped or need manual mapping, and rows without a usable email address get rejected.

Three rules cover most failures:

1. One email per row, in one column. A row with two emails, or an email buried inside a notes field, will not import cleanly.
2. Headers must be readable. Email, Email Address, EMAIL, and email_address all map fine; Col1, e-mail addr, or a blank header do not. Mailchimp lets you map columns manually, but clean headers make the import automatic.
3. No extra junk. Title rows ("Spring Sale Contacts"), blank rows, totals rows, and merged cells all confuse the importer. The data must start at row 1 with headers and continue as one clean table.

Step 1: Open the CSV Properly in Excel

Do not double-click the CSV. Double-clicking makes Excel guess at everything: dates flip, leading zeros vanish, and long IDs turn into scientific notation. Instead, open Excel, go to Data > From Text/CSV, select your file, and check the preview before loading. Set the delimiter to Comma and the encoding to UTF-8 (or 65001). If names contain accents and they look garbled, the file is probably in a different encoding — the guide to fixing CSV encoding errors in Excel explains how to diagnose that.

Once the data is loaded, immediately save it as .xlsx (File > Save As). You will do all the cleaning in the workbook, and only export a fresh CSV at the very end. Working in .xlsx means undo, filters, and formulas all behave properly.

Step 2: Clean the Headers

Rename the header row to Mailchimp-friendly names:

• Email column → Email Address
• Given name → First Name
• Family name → Last Name
• Phone → Phone Number
• Any tag column → Tags (Mailchimp accepts a Tags column; separate multiple tags with commas inside the cell, or use one column per tag group)

Delete columns Mailchimp does not need: internal IDs, order totals, notes fields full of free text, and any column that is blank all the way down. Fewer columns means fewer mapping mistakes. If you need an internal ID for your own records, keep it in the workbook but delete it from the exported CSV — Mailchimp does not need it, and it adds a column that could be mis-mapped.

Watch out for merged header cells and multi-row headers ("Contact Info" spanning three columns). Unmerge everything (Home > Merge & Center > Unmerge Cells) and make sure every column has exactly one header in row 1.

Step 3: Fix the Email Column

The email column decides whether a row imports. Work through it with Excel's tools:

1. Trim spaces. In a helper column, use =TRIM(LOWER(A2)) to strip leading/trailing spaces and normalise case. Copy the results and Paste Special > Values back over the originals.
2. Remove blanks. Filter the email column for blanks. A row with no email cannot import — either fill in the address from your source system or delete the row. Never invent an address.
3. Flag malformed addresses. Use a helper column with =IF(AND(ISNUMBER(SEARCH("@",A2)),ISNUMBER(SEARCH(".",A2,SEARCH("@",A2)))),"OK","CHECK") to flag rows missing @ or a dot after it. Review every CHECK row by hand: typos like gmail.con or name@company are common and worth fixing from the source data.
4. Remove role addresses you do not want mailed. Addresses like info@, support@, admin@, and noreply@ belong to inboxes, not people, and they bounce or complain more often. Filter for them and decide — for a marketing audience, removing them is usually right.

Do not buy or append email lists to "fill out" the audience. Mailchimp prohibits purchased lists, and they generate spam complaints that can get an account suspended. Import only people who gave you permission to email them.

Step 4: Remove Duplicates and Junk Rows

Duplicates are the most common Mailchimp import headache: the same person imported twice can receive the same campaign twice. Excel makes this easy:

1. Select the whole table (click any cell inside it, then Ctrl+A).
2. Go to Data > Remove Duplicates. Tick "My data has headers", then select only the Email Address column as the duplicate key. If you tick every column, rows that differ in one field (a different phone number) will survive as duplicates.
3. Excel reports how many duplicates it removed. Sanity-check the number: if it removed half your list, something is off — perhaps the same contact appears once per order, and you actually need one row per contact, not per order.

Then delete junk rows: test rows (test@test.com), internal staff rows you do not intend to mail, rows with placeholder names ("N/A", "Customer"), and any row whose status column says unsubscribed or bounced in your source system. Importing people who already unsubscribed is both pointless and a compliance problem — Mailchimp will re-suppress them if they are already in the audience, but a fresh audience will not know they opted out.

Step 5: Standardise Names, Dates, and Tags

Names: Split combined names ("Maria Garcia") into First Name and Last Name with Data > Text to Columns (space delimiter), or Flash Fill (Ctrl+E) for messy lists. Capitalise consistently with =PROPER(A2) — "maria" becomes "Maria". A merge tag that prints "hi maria" looks sloppy in a campaign.

Dates: Mailchimp date fields need real dates, not text that looks like a date. Select the date column, go to Data > Text to Columns, and set the format to the one your file uses (DMY or MDY). Dates that flip day and month are a classic CSV problem — the guide to fixing CSV date formats in Excel covers the DMY vs MDY trap in full.

Tags and groups: Decide your tagging before import, not after. A Tags column with values like Buyer, Newsletter lets you segment from day one. Keep tag names short, consistent, and free of typos — "Buyer" and "buyer " (with a trailing space) become two different tags.

Pre-Import Checklist

Step 6: Export the Clean CSV and Import

When the workbook is clean, export it: File > Save As > CSV UTF-8 (Comma delimited). If Mailchimp warns about features, click Yes — you are intentionally saving a plain CSV. Then in Mailchimp: Audience > Add Contacts > Import Contacts > Upload file, choose your CSV, and check the column mapping screen. Mailchimp shows a preview: confirm Email Address maps to Email Address, First Name to First Name, and so on. If a column shows "Skip", fix the header in Excel and re-export rather than forcing a bad mapping.

Import to a small test segment first if the list is large — 50 to 100 contacts — and send yourself a test campaign to check merge tags (*|FNAME|* should print a real name, not a blank). Once the test looks right, import the rest.

FAQ

Does Mailchimp import Excel (.xlsx) files directly?

Mailchimp's importer takes CSV (and TXT) files. The Excel step is for cleaning: open the CSV in Excel, fix headers, emails, and duplicates, then save back as CSV UTF-8 and import that file. Never send Mailchimp a raw export without cleaning it first.

Why did Mailchimp skip some of my contacts?

The usual reasons are a missing or malformed email, a duplicate of a contact already in the audience, or a row Mailchimp classified as a role address or bounce risk. After import, Mailchimp shows an import summary — check it, fix the flagged rows in Excel, and re-import only those rows.

How do I handle first and last names in one column?

Split them before import. Use Data > Text to Columns with a space delimiter, or Flash Fill (Ctrl+E) for inconsistent lists, then tidy with =PROPER(). Mailchimp's First Name / Last Name fields need separate columns to personalise campaigns correctly.

Can I import tags and groups from Excel?

Yes. Add a Tags column with comma-separated tags per row, and Mailchimp will apply them on import. For groups, add one column per group category and map each column to the matching group field on the mapping screen.

What is the biggest mistake when importing a CSV into Mailchimp?

Importing a raw, uncleaned export. Duplicates, junk rows, bad headers, and malformed emails cause most failed imports — and importing people who previously unsubscribed is a compliance problem. Ten minutes of cleaning in Excel prevents nearly all of it.

Final Checklist

• Headers are clean, one row, no merged cells, Mailchimp-friendly names.
• Every row has exactly one valid email; blanks and malformed rows fixed or removed.
• Duplicates removed on the email column only.
• Unsubscribed, bounced, test, and placeholder rows excluded.
• Names split and capitalised; dates are real date values; tags consistent.
• Exported as CSV UTF-8 and column mapping verified on Mailchimp's preview screen.

If all six checks pass, your list will import the first time — and your first campaign will reach real people, once each, with their names spelled right.