Mail Merge Excel and Word

One letter, one spreadsheet, one row per recipient. The mechanics take five minutes; the formatting problems take the afternoon, so both are covered here.

Mail merge takes one Word document and one Excel sheet and produces a personalised copy per row. The short version: Mailings → Start Mail Merge → Letters, then Select Recipients → Use an Existing List, then Insert Merge Field where each value belongs, then Finish & Merge. The rest of this is the part that actually costs time.

Step 1: get the spreadsheet right first

Most failed merges are failed spreadsheets. Before you open Word:

• One header row, at row 1. No title above it, no logo, no blank spacer row. Word reads row 1 as the field names and there is no way to tell it otherwise.
• One recipient per row, on one sheet. Word connects to a single sheet, not a workbook.
• No merged cells anywhere in the range.
• Plain header names — First Name, Company, Amount. They become the merge field names you will be picking from a list.
• Split what you will need separately. If you want "Dear Sarah," you need a first-name column. A single Full Name column cannot be halved later.
• Save and close the workbook before connecting. An open file can lock the connection.

Step 2: connect Word to the sheet

In your Word document, go to the Mailings tab. Click Start Mail Merge and pick what you are producing — Letters for one page per person, Email Messages to send, Labels or Envelopes for print.

Then Select Recipients → Use an Existing List, browse to the workbook, and choose the sheet when Word asks. Tick "First row of data contains column headers" if it is not already ticked.

Edit Recipient List is worth a look at this point: it lets you untick rows, sort, and filter — the easiest way to merge only this month's customers without touching the spreadsheet.

Step 3: insert the merge fields

Write the letter normally. Wherever a personalised value belongs, put the cursor there and click Insert Merge Field, then pick the column. The document shows a placeholder such as «First_Name».

Address Block and Greeting Line are pre-built groups for the common cases, and they are more trouble than they are worth when your columns do not match what Word expects. Inserting fields individually is more predictable.

Step 4: fix the numbers and dates

This is where the afternoon goes. Word does not read your Excel formatting — it reads the underlying value through a database driver. So a cell showing $1,234.50 arrives in the letter as 1234.5, and a date showing 15/03/2026 can arrive as 3/15/2026 or as the serial number 46096.

The fix is a formatting switch on the field itself:

1. Press Alt+F9 to switch the document to field codes. «Amount» becomes { MERGEFIELD Amount }.
2. Edit it to include a picture switch:
   { MERGEFIELD Amount \# "$#,##0.00" }
{ MERGEFIELD InvoiceDate \@ "d MMMM yyyy" }
3. Press Alt+F9 again, then Preview Results to check.

Step 5: preview properly

Click Preview Results and step through with the arrows. Do not check record 1 and stop. Step to a row with a long company name, a row with a blank field, and the last row. Blank fields are the usual embarrassment: a missing company leaves a stranded comma, and an empty first name produces "Dear ,".

Step 6: finish the merge

Finish & Merge gives three routes:

• Edit Individual Documents — produces one new document containing every letter, each on its own page. Use this to proofread, then print.
• Print Documents — straight to the printer.
• Send Email Messages — pick the column holding the addresses, set a subject, and Word sends one message per row through Outlook.

What mail merge cannot do

Worth knowing before you build a process on it:

• No per-recipient attachments. Merge to email puts the letter in the message body. There is no attachment field. Sending each client their own PDF needs a macro or an add-in.
• No separate files per record. Edit Individual Documents makes one long document, not fifty.
• No charts per recipient. Merge fields carry text and numbers from cells. A chart of that client's last six months is not something a merge field can hold.
• It is a one-shot. Next month you re-run the whole thing against a new export, and any figure you typed by hand into the letter body is now quietly out of date.

When the letter is really a report

Mail merge is built for many recipients and one short message. The job it gets pushed into is the opposite: a handful of clients, each needing a page or two with their own numbers and charts in it. At that point the merge fields run out — you can personalise a name and a total, not an analysis.

Two different tools for two different shapes:

• Many recipients, small personalisation. Mail merge, exactly as above. Nothing beats it.
• Few recipients, real content each. A document that asks its own questions. Quiriz for Word lets you write =QUIRIZ.ASK("hours by project this month", "table") where a table belongs and =QUIRIZ.ASK("total billed this month", "figure") inside a sentence, then answer every marker in one pass — and press Refresh all next month to ask the same questions of the new data. The prose you wrote stays; the numbers move.

We have written up the second shape in full: how to send a separate report to each client.

Frequently asked questions