How to mail merge in Word when the spreadsheet fights back

A mail merge in Word is really two jobs wearing one name. The Word half is easy and takes about four minutes once you have done it twice. The other half is a spreadsheet, and that is where nearly every merge comes apart.
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
Word does not read your spreadsheet the way you see it. It reads the stored value underneath, which is why currency and dates are the two things that break most often and why the fix lives in Word rather than in Excel.
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:
- Merged cells. The value sits in the top-left cell and the rest are genuinely empty, so those records merge blank.
- Headers not in row one, or a title row sitting above them.
- Blank rows in the middle of the data, which become blank letters.
- Numbers or dates stored as text. If a column of figures is left-aligned by default, it is text.
- 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?
Most misdirected email is a habit problem rather than a software problem, and habits respond well to a couple of hours of structured time with someone who knows the tools. Our Microsoft 365 training covers Outlook alongside the rest of the suite, tailored to how your team actually works. If a session does not land the way you expected, we will tell you so before you book the next one.
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.
Related reading
STRAIGHT ANSWERS
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.
