How to mail merge in Word when the spreadsheet fights back

10 October 2026
By Matt Weston
Flat lay of fanned envelopes and one separate sheet, showing how to mail merge in Word produces many from one

The symptoms are always the same. A letter that should read “the balance outstanding is £1,234.56” arrives saying £1234.5600000001. An invoice date of 03/04/2026 lands as April 3rd in a letter going to a customer in Dudley. Half the recipients get a blank where their town should be. Somebody’s name appears twice. And none of it showed up in the preview, because the preview looked at record one and record one was fine.

Here is the part worth knowing, and Microsoft documents it plainly on a page almost nobody finds: the formatting you applied in Excel does not travel. Word takes the stored value, not what you see on screen. Format a column as currency and Word still receives the raw number. Make a date look British and Word still receives a date serial it will interpret however it likes.

So the useful version of learning mail merge is not the sequence of ribbon clicks, which Microsoft covers perfectly well. It is knowing what the spreadsheet has to look like before you start, and knowing the two field switches that force Word to display things the way you meant.

Quick summary

Formatting applied in Excel is lost in the merge. Microsoft states this directly: the data arrives in its native stored format
The fix is a picture switch inside the merge field. # for numbers, @ for dates, and both are case-sensitive
Press Alt and F9 to reveal field codes so you can edit them
Your source needs headers in row one, one record per row, no merged cells and no blank rows. Merged cells in particular will quietly cost you records
Mail merge sends through Outlook on your own account. It is not an email marketing tool and should not be used as one

Get the spreadsheet right and the Word half takes four minutes. Get the spreadsheet wrong and no amount of clicking in Word will save it.

The three moving parts

Every mail merge has the same anatomy, and naming them makes the failures easier to place.

  • The data source. Usually an Excel workbook, sometimes Outlook contacts. One row per recipient, one column per piece of information.
  • The main document. The letter, label or email, written once, with merge fields standing in for whatever changes.
  • The merge itself. Word walks the rows and produces one personalised copy per record.

When something goes wrong, it is almost always the first of the three. Hold that thought and most mail merge troubleshooting becomes quick.

How to mail merge in Word, start to finish

Get the spreadsheet right first

This is the step people skip and then pay for. Before you open Word:

  • Headers in row one, and only row one. No title row above them, no merged cells across them, no second header row. Word takes row one as the field names.
  • One record per row, no blank rows. A blank row in the middle is a blank letter.
  • No merged cells anywhere. Merged cells leave the value in the top-left cell and genuinely empty cells beneath it, so the rows underneath merge as blanks.
  • Numbers stored as numbers, dates as dates. A column of numbers left-aligned is text pretending to be numbers.
  • Split what you want to control separately. If you ever want “Dear Jane” rather than “Dear Jane Smith”, first name and surname need to be two columns.

If the data arrives as an export that needs the same tidy-up every month, Power Query will do it once and then repeat it on refresh, which turns a recurring chore into a button.

Start the merge and connect the data

In Word, go to the Mailings tab, choose Start Mail Merge, and pick what you are producing: letters, email messages, envelopes or labels. Then Select Recipients, then Use an Existing List, and point it at the workbook. Word will ask which sheet.

Close the workbook in Excel before you do this. An open file is the second most common reason a merge refuses to connect.

Insert the fields

Write the document as you would normally, then put the variable bits in with Insert Merge Field. Address Block and Greeting Line are prebuilt combinations, and they are worth using for addresses because they handle missing lines sensibly.

Use Rules if some recipients need different wording. An If…Then…Else rule will swap a sentence based on a field value, which saves splitting one merge into three.

Preview, then finish

Preview Results shows you real records, and the arrows step through them. Do not check record one and stop. Step to the middle, then the last one, then any record you know is awkward. Check for Errors will catch structural problems the preview will not.

Then Finish & Merge. Choose Edit Individual Documents rather than printing straight away. It produces one document containing every letter, which you can scan before anything reaches paper or a customer.

Microsoft’s own walkthrough of the ribbon sequence is on its mail merge page, and it is a decent reference for the clicks.

The thing that breaks most merges

Microsoft’s main mail merge page says nothing at all about number or date formatting, which is unfortunate, because that is what goes wrong most. The explanation lives on a separate support page: because Access and Excel hand Word the data in its native stored format, formatting applied in the data source is lost during the merge, and you have to format the merge field in the Word document instead.

Once you know that, both classic failures have the same shape of fix.

Money arriving as 1234.5600000001

Press Alt and F9. Every field in the document turns into its underlying code, so «Balance» becomes { MERGEFIELD Balance }. Now add a numeric picture switch inside the braces, before the closing one:

{ MERGEFIELD Balance \# "£#,##0.00" }

Press Alt and F9 again to turn the codes back into fields, then preview. The balance now reads £1,234.56.

Microsoft gives the same switch in its own examples, including a phone number pattern where literal characters are wrapped in single quotation marks, as in \# ###'-'###'-'####.

Dates flipping to American order

Same method, different switch. \@ handles dates, and the picture string goes in quotation marks:

{ MERGEFIELD InvoiceDate \@ "dd/MM/yyyy" }

That gives 03/04/2026 rather than 4/3/2026. For a letter you would more often want "d MMMM yyyy", which produces 3 April 2026.

One detail that catches people and is easy to miss: the switches are case-sensitive. MM is months and mm is minutes. A date that comes out as 03/14/2026 usually means lower-case months in the picture string.


Worth knowing

Before you blame Word, open the spreadsheet and check five things. In rough order of how often each one is the culprit:

  1. Merged cells. The value sits in the top-left cell and the rest are genuinely empty, so those records merge blank.
  2. Headers not in row one, or a title row sitting above them.
  3. Blank rows in the middle of the data, which become blank letters.
  4. Numbers or dates stored as text. If a column of figures is left-aligned by default, it is text.
  5. The workbook still open in Excel, which stops Word connecting at all.

Four of those five are fixed in Excel, not Word. That is the whole point: mail merge troubleshooting is mostly spreadsheet troubleshooting, and the people who find it frustrating are usually fighting it in the wrong application.


Mail merge is not an email marketing tool

Worth stating plainly, because it gets used as one and it causes real problems.

Merging to email sends each message individually through Outlook, on your own account, from your own mailbox. Each recipient sees only their own address, which is the one thing it does well. Everything else an email tool gives you is absent:

  • No unsubscribe mechanism, which matters a great deal if the content is marketing rather than operational
  • No tracking, so you have no idea what happened after you clicked send
  • No scheduling or throttling, so a few hundred messages leaving one mailbox in a few minutes can look like exactly the sort of behaviour that trips sending limits
  • No deliverability safety net. Bulk mail from a mailbox that is not properly authenticated is the fastest way to teach other people’s spam filters to distrust your domain, which is why SPF, DKIM and DMARC matter before you send anything at volume

For operational mail, a hundred renewal reminders or appointment letters, merging to email is fine and often the right tool. For anything resembling marketing, use something built for it, and be aware that consent and unsubscribe obligations apply to the message regardless of which tool sent it. That is a question worth asking before the send, not after.

When to stop merging and automate it

There is a threshold, and it is easy to spot once you are looking for it.

If you are running the same merge every month from the same export, re-applying the same fixes, and the only thing that changes is the data, the merge has stopped being a document task and become a process. At that point Power Automate can generate the documents from the source directly, with no spreadsheet tidy-up and nobody remembering which switches to add.

The honest test is whether a person has to remember something for it to work. If it does, it will eventually not work, usually while that person is on leave. That is the conversation our Consultancy team has under business process automation, and it is worth having at the point where the merge is monthly rather than the point where it is annoying.

Most merges never reach that threshold, and a well-set-up spreadsheet plus two field switches is the right answer for them.

Is one person in your team the only one who can run the merge?

The bottom line on mail merge in Word

The clicks are the easy part, and any walkthrough will get you through them. What separates a merge that works from one that produces four hundred letters with a decimal problem is the half-hour spent on the spreadsheet first, and knowing that Excel’s formatting does not survive the trip.

So the short version: headers in row one, one record per row, nothing merged, numbers as numbers. Then in Word, Alt and F9, \# for money and \@ for dates, watching the capital letters. Preview a record from the middle rather than the top, and merge to a document rather than to the printer.

If the same merge keeps coming round every month, the skill to learn next is cleaning the source once rather than fixing it repeatedly, which is what Power Query is for. And if your spreadsheets are the reason this is hard, our Excel training deals with the structural habits that make everything downstream easier, mail merge included.

Frequently asked questions

Why does my mail merge show numbers with lots of decimal places?

Because Word receives the stored value rather than the formatted one. Excel showing £1,234.56 does not mean Word gets £1,234.56. Fix it in Word with a numeric picture switch: press Alt and F9 to show the field codes, then add # "£#,##0.00" inside the field before the closing brace.

Why are my dates in American order?

Same cause, same kind of fix. Add a date picture switch: @ "dd/MM/yyyy" for a short British date, or @ "d MMMM yyyy" for 3 April 2026. The switches are case-sensitive, so use capital MM for months. Lower-case mm means minutes, which is the usual reason a date comes out looking strange.

Why are some of my records merging as blanks?

Usually merged cells in the source. A merged cell holds its value in the top-left cell only, and the cells beneath it are genuinely empty, so those rows merge with nothing in them. Blank rows in the middle of the data do the same thing. Unmerge everything in the source and remove empty rows.

Can I mail merge from Excel without opening Word?

No. Excel holds the data and Word does the merging, so the document side has to happen in Word. The merge is started from the Mailings tab in Word and points back at the workbook, which should be closed while the merge runs.

Can I use mail merge to send a newsletter?

You can, and it is usually a bad idea. Mail merge sends through your own Outlook account with no unsubscribe link, no tracking and no throttling, and sending marketing mail that way creates both a deliverability problem and a compliance one. Use it for operational mail such as reminders and statements, and use a proper email platform for anything promotional.

How do I send different wording to different recipients?

Use Rules on the Mailings tab. An If...Then...Else rule inserts one piece of text or another depending on a field value, so a single merge can handle recipients who need different paragraphs without splitting it into several merges.

Why will Word not connect to my spreadsheet?

Most often because the workbook is open in Excel. Close it and try again. Beyond that, check that the data is on a single sheet with headers in row one, and that you are pointing Word at the right sheet when it asks.

Shopping Basket