How to Clean an Excel Contact List for Mail Merge (Word and Gmail)

You ran the mail merge, previewed the results, and there it is: 'Dear JOHN SMITH ,' on one letter, a completely blank letter three records later, and a «First_Name» field that refuses to pull anything at all. Word's mail merge and Gmail's mail merge both do exactly what they are told. When the output looks wrong, the problem is almost never the merge settings. It is the spreadsheet feeding them.

This guide walks through every common contact-list problem that breaks a mail merge, shows what each one looks like in the final letters or emails, and covers how to fix all of them in one pass instead of hand-editing 800 rows the night before a send.

What Word and Gmail actually require from your list

Both tools have the same basic contract. Word's mail merge (Mailings, Start Mail Merge, then Select Recipients and Use an Existing List) reads the first row of your sheet as field names and every row below it as one recipient. Gmail's built-in mail merge works the same way from a linked Google Sheet, using column headers as merge tags like @firstname. That contract has strict, mostly undocumented rules:

That last point is the one that burns people. Mail merge has no cleanup layer. 'JANE DOE' merges as 'JANE DOE'. A trailing space after 'Jane ' merges as 'Dear Jane ,' with a floating gap before the comma. Fixing this in the Word document is impossible; you have to fix the source list.

The five list problems that break merges

Contact lists that feed mail merges are usually stitched together from a CRM export, an event signup sheet, and someone's typed-in additions. Here is what that typically looks like:

First Name , LAST NAME,Email Address ,Company,Amount Due
Jane , DOE,jane.doe@acme.com ,Acme Corp,$450.00
ROBERT,smith,rob@example.com,N/A,$1,200.00
  maría,GONZÁLEZ,maria@tienda.mx,Tienda MX,--
,,,
sarah ,O'BRIEN, sarah@obrienco.com,O'Brien & Co,$75.00

Every line of that sample contains a real merge-breaker:

Step 1: Fix the headers so merge fields actually connect

Start with row 1, because if the field names are wrong nothing else matters. Headers should be short, consistent, and free of leading or trailing spaces and punctuation. Word converts spaces to underscores when it builds field names, and Gmail's merge tags are case-sensitive, so the safest format is lowercase with underscores:

Before: First Name , LAST NAME, Email Address ,Company,Amount Due
After:  first_name,last_name,email_address,company,amount_due

Now your Word document uses «first_name» and «last_name», your Gmail merge uses @first_name, and there is no ambiguity about whether the field was created from 'First Name' or 'First Name ' with a ghost space. If your export uses cryptic system names like 'CONTACT_FNAME__C' from a CRM, rename them to something readable before you build the document, because you will be reading these field names inside your letter template.

Step 2: Trim the whitespace hiding in every column

Leading and trailing spaces are the most common cause of 'Dear Jane ,' spacing problems and of bounced emails in Gmail merges. They come from copy-paste, from CRM exports that pad fields, and from people typing a space after a name out of habit. You cannot see them in Excel without clicking into each cell.

Before: 'Jane ' + ',' -> letter reads: Dear Jane ,
After:  'Jane'  + ',' -> letter reads: Dear Jane,

Email columns deserve special paranoia here. ' sarah@obrienco.com' with a leading space is not a valid address to most sending tools, and a Gmail merge will either skip the row or fail the send. Trim every text column, not just the names.

Step 3: Standardize name casing before it lands in a greeting

Nothing says 'mass mail' like 'Dear ROBERT,'. Names typed in ALL CAPS or all lowercase need converting to title case so 'ROBERT' becomes 'Robert' and 'smith' becomes 'Smith'. If your list mixes both, standardize the whole column rather than hunting for offenders.

Before: ROBERT smith / maría GONZÁLEZ / sarah O'BRIEN
After:  Robert Smith / María González / Sarah O'Brien

Step 4: Turn placeholder junk into real blanks

Whoever built your list probably typed 'N/A', 'n/a', 'none', or '--' into cells they did not have data for. Mail merge does not know these mean 'empty'. It merges them literally, so a letter reads 'Company: N/A' and your conditional 'skip if empty' rules in Word (the IF field or the 'skip blank lines' option in address blocks) never trigger, because the cell is not blank, it contains the text N/A.

Before: company = 'N/A'  -> letter prints: Company: N/A
After:  company = ''     -> Word's blank-line suppression skips the line entirely

Converting every placeholder variant to a genuinely empty cell is what makes Word's built-in blank suppression and Gmail's fallback text ('Hi @first_name' with a default of 'Hi there') work the way the documentation promises.

Step 5: Delete blank rows and dead columns

A fully blank row in the middle of your data produces a blank letter or a skipped email, and a row where only one cell has a stray value ('yes' in column F, nothing else) produces a letter addressed to nobody. Delete both kinds. Do the same for columns: exports often carry ten empty system columns, and every one of them shows up in Word's Insert Merge Field list, cluttering the exact menu you are about to work in.

Why fixing this manually in Excel gets old fast

Everything above is technically possible in Excel: =TRIM(A2) in a helper column, =PROPER(B2) for casing, Find and Replace for each N/A variant, Go To Special for blank rows, then copy, paste as values, and delete the helper columns. It works once. The problems are the second time and the tenth time.

Clean the whole list in one pass with PipeSheets

PipeSheets is built for exactly this kind of repeatable pre-merge cleanup. You upload the CSV or XLSX, run a cleaning pipeline, and download a merge-ready file. For a contact list, the pipeline looks like this:

If you do not want to configure anything, the Quick Clean preset covers the core of this in one click: trim whitespace everywhere, standardize nulls, drop fully empty rows and columns, and normalize headers to snake_case. Add a title-case step for the name columns and you are done. The output downloads as CSV or XLSX, so it drops straight into Word's Select Recipients dialog or uploads to Google Sheets for a Gmail merge, and the file is UTF-8 safe, so 'María González' survives the round trip instead of arriving as 'María'.

The real payoff is repeatability. Save the pipeline once, and next month when the CRM spits out the same messy export, you run the same pipeline in seconds instead of rebuilding helper columns from memory.

Pre-merge checklist

Before you hit Finish and Merge or Send, run down this list against your cleaned file:

A mail merge is unforgiving because it is fast: one bad column ships 500 bad letters in the time it took to read this sentence. Clean the list first and the merge itself becomes the easy part. Upload your contact list to PipeSheets, run Quick Clean plus a title-case step, and your next merge previews clean on the first try.