Sigmera.

Best tool for preparing a mail merge

Preparing a mail merge means getting the recipient list right before a single letter or email is generated. The merge itself is the easy half: Word or Gmail reads a row and drops the values into placeholders. Everything that goes wrong goes wrong in the list — a greeting that reads "Dear ," because the name column holds a full name rather than a first name, the same person receiving two copies, a postcode that lost its leading zero somewhere between the export and the envelope. A merge engine will not warn you about any of it. It prints exactly what the file says.

What to look for

  • One column per merge field. Microsoft is explicit that column names should match the fields you insert, and that addressing readers by first name requires separate first and last name columns. A single Full Name column cannot produce "Dear Jane".
  • Leading zeros survive the merge. Microsoft documents that ZIP and postal codes must be formatted as text, because leading zeros such as 00399 are dropped during a mail merge otherwise — and a dropped zero is an undeliverable envelope.
  • One sheet, one header row. Word reads an Excel data source well only when the data is well-formatted and on a single sheet, with all the merge data present on the first sheet. Multi-tab workbooks and stacked header rows are the usual reason a merge sees the wrong fields.
  • No duplicate recipients. Nothing in a merge engine deduplicates. Two rows for the same person means two letters, or two identical emails from a campaign that was supposed to look personal.
  • Addresses that are actually deliverable. A trailing space or a malformed address does not stop a merge — it produces a send that silently fails, and the bounce lands against your sending domain.
  • The list stays on your own machine where possible. A mailing list is personal data, so a cleaning step that uploads it adds a data transfer and a processor relationship you did not need.

The tools, compared

Word mail merge with an Excel data source
high fit

The default answer for letters, labels, and envelopes, and the one with the best-documented rules. Microsoft's preparation guide is worth reading before you start rather than after: column names should match the merge fields you plan to insert, all the data has to be on the first sheet, and every change should be finished before the workbook is connected to the Word document — reconnecting mid-merge is where people lose an afternoon. Two formatting traps are called out by name. ZIP and postal codes need the Text format or their leading zeros are dropped during the merge. And a column formatted as Percentage is multiplied by 100, so format percentages as text if you want the number you actually typed. Word handles the merge itself well; it does nothing to check the list.

Visit site
Microsoft Excel (as the data source)
high fit

Excel is where the number formats Word depends on are actually set, so for the format-as-text step there is no substitute. It is a weaker tool for the rest of the preparation. Splitting a Full Name column means Text to Columns or a stack of LEFT, RIGHT, and FIND formulas that break on middle names and two-word surnames. Deduplicating is a Remove Duplicates pass that matches on exact values, so " [email protected] " and "[email protected]" survive as two people. And if the list arrived as a CSV, opening it by double-click has already stripped the leading zeros you are about to format back in — the value is gone, and a Text format only repaints the display.

Visit site
Gmail mail merge with Google Sheets
medium fit

Good for a personalised email blast if your account qualifies, and the Sheets link is genuinely convenient: any column in the spreadsheet can be used as a merge tag in the message. Two limits decide whether it fits. It is restricted to Workspace Individual, Business Standard and Plus, Enterprise Standard and Plus, and Education Standard and Plus — a free Gmail account does not have it. And the ceiling is 1,500 recipients per message and 1,500 mail merge sends a day, with 500 sends held back for ordinary email. It also does not print, so letters, labels, and envelopes are out of scope entirely.

Visit site
Sigmera
medium fit

Deliberately half the job: Sigmera does not run a mail merge and never will. What it does is turn a messy export into the well-formed single-sheet list that Word and Gmail both assume you already have — split the full-name column into first and last, remove duplicate recipients matched on a normalised email rather than an exact string, lowercase and trim the address column, standardise phone numbers, then write a clean CSV or XLSX. It runs in the browser, so the mailing list is not uploaded. It has no opinion about the merge document itself, and if your list is already clean it adds nothing.

Visit site
Typing the letters individually
low fit

Included because it is what a lot of people fall back to after a merge goes wrong, and it is worse than it feels. Below about twenty recipients the arithmetic is arguable; above it, hand-typing reintroduces the exact errors a merge exists to prevent, with no audit trail of who was sent what. If the objection is that setting up a data source is fiddly, note that Word accepts more than Excel — Outlook contacts, an Access table, a Word table, and delimited text files where fields are separated by tabs or commas and records by paragraph marks. One caveat from the same page: the data source has to be local or on a file share, because saving at an HTTP location is not supported for mail merge.

Visit site

Why Sigmera

The failure modes in a mail merge are all list problems, and they are all visible before you send. A greeting reads "Dear Jane Okafor" instead of "Dear Jane" because nobody split the name column. Two hundred people get one letter and eleven get two, because the list was stitched together from a CRM export and a conference sign-up sheet and nothing checked for overlap. A postcode arrives as 399 instead of 00399. Sigmera is built for that pass and nothing more: split full names into first and last, remove duplicates matched on a lowercased, trimmed email rather than an exact string, clean the address column, standardise phone numbers, then export a single-sheet file that Word can read the first time. It reads a CSV as text, so leading zeros are still there when you hand the file to Excel to format — unlike a double-click, which strips them before you get a say. Everything runs client-side in your browser, so the mailing list, which is personal data under the GDPR and usually the most complete list of real people you own, is never uploaded to a server. Previewing is free and unlimited; the download counter only ticks when you export the finished file.

Sources

  1. 1.Prepare your Excel data source for a Word mail merge — Microsoft Support
  2. 2.Mail merge using an Excel spreadsheet — Microsoft Support
  3. 3.Data sources you can use for a mail merge — Microsoft Support
  4. 4.Send a mail merge in Gmail — Google Workspace Help

Frequently asked questions

How do I prepare a spreadsheet for a mail merge?
Put all the data on the first sheet with one header row, give every merge field its own column, and finish all edits before connecting the file to Word. Name the columns to match the fields you will insert. Format ZIP and postal codes as text so leading zeros survive, and format percentage columns as text too, since the Percentage category multiplies the value by 100. Then deduplicate the list, because nothing downstream will.
Why does my mail merge say "Dear" with no name?
The column the greeting points at is empty for that row, or it holds a full name while the merge field expects a first name. Microsoft's guidance is to keep separate columns for first and last name precisely so a greeting can address someone by first name. Split the name column before the merge, check the rows the split flagged as ambiguous, and reconnect the data source.
Why did my mail merge drop the leading zeros from postcodes?
Because the column was formatted as a number. Microsoft documents that leading zeros in codes such as 00399 are dropped during a mail merge unless the column is formatted as text. There is a second trap if the list started as a CSV: opening it by double-click already discarded the zeros at parse time, so formatting the column as text afterwards changes only the display. Re-import the original file and set the column to Text on the way in.
How do I stop a mail merge sending duplicate letters?
Deduplicate the list first — no merge engine does it for you. Match on a normalised key rather than an exact string, because " [email protected] " and "[email protected]" are the same person to a recipient and two different people to Excel's Remove Duplicates. Lowercase and trim the email column, run the dedupe on that, and review the flagged groups before you keep one row from each.
Can I run a mail merge from a CSV instead of an Excel file?
Yes. Word accepts delimited text files where fields are separated by tabs or commas and records by paragraph marks, alongside Excel workbooks, Outlook contacts, Access tables, and Word tables. The file has to be stored locally or on a file share — saving at an HTTP location is not supported. A CSV is the safer container for identifiers, because it holds the characters you gave it; the risk starts when a spreadsheet opens it and infers types.
How many recipients can a mail merge handle?
It depends on the sender, not the list. Word printing letters or labels has no practical recipient ceiling. Gmail mail merge is capped at 1,500 recipients per message and 1,500 mail merge sends per day, with 500 sends reserved for regular email, and it requires an eligible Workspace edition. For anything larger, or for tracked campaigns, an email service provider is the right tool and the list preparation described here still applies.
Do I have to upload my mailing list to clean it?
No, and it is worth avoiding. A mailing list is personal data, so an online cleaner that uploads it introduces a transfer and a processor you have to account for. Sigmera parses the file with JavaScript running on your own machine — the list is never sent anywhere, which keeps the preparation step out of scope for that question entirely.