How to do a mail merge: letters, envelopes and labels from a spreadsheet

The parts of every merge

Every program that does a mail merge, from Microsoft Word to LibreOffice Writer, works with the same parts, and most of the confusion goes away once they have names.

• The main document. The letter, envelope, label sheet or email, with the words that are the same for everyone and a placeholder wherever something changes.
• The data source. A table with one row per recipient and a first row that names the columns: a spreadsheet, a .csv file, an address book or a database.
• Merge fields. The placeholders. Each names a column, and Word shows it between chevrons — «First_Name» — with a MERGEFIELD field code underneath. Word will not put a space in a field name, so a column called First Name becomes First_Name.
• The preview. The main document with one record’s values in place of the fields, stepped through record by record before anything is printed or sent.
• The output. A new document with a copy per record, the same copies sent straight to the printer, or one email per record.

Get the list right first

Most merge problems are list problems that only show up at the end. Ten minutes on the spreadsheet saves a reprint.

• One header row, in row 1. A short, distinct name for each column, with no title above the headings and no merged cells. A title in row 1 becomes the name of a field, and the real headings become a recipient.
• One recipient per row. No blank rows in the middle and no totals row underneath. A merge reads every row that has anything in it, so a blank row becomes an empty letter and a totals row becomes a recipient.
• A column for each part. Title, first name, last name, each address line, town, postcode and country in columns of their own, so the greeting can say “Dear Ms Khan” and the address can leave out what is missing.
• Postcodes and phone numbers as text. Stored as a number, a US ZIP code of 02134 loses its leading zero and becomes 2134. Format the column as Text before typing into it.
• A .csv saved as UTF-8. If accented names come out garbled — “Müller” as “Müller” — the file’s encoding and the program’s guess disagree. Excel’s “CSV UTF-8” save type avoids it.

The steps, in Word

Microsoft Word’s Mailings tab is laid out in the order you work, left to right. LibreOffice Writer walks through the same stages in Tools → Mail Merge Wizard.

• Start Mail Merge says what the main document is: Letters, E-mail Messages, Envelopes, Labels or Directory.
• Select Recipients attaches the list: Type a New List, Use an Existing List, or Choose from Outlook Contacts. A workbook opens the Select Table dialog, which lists its sheets with a $ after each name (Sheet1$) and any named ranges without one. Leave “First row of data contains column headers” ticked.
• Edit Recipient List ticks people in and out, sorts, filters and finds duplicates.
• Address Block and Greeting Line are ready-made fields for the address and the “Dear …” line. Insert Merge Field puts any single column at the caret, and Rules adds conditions: If…Then…Else, Skip Record If and the rest. Match Fields tells Address Block which of your columns is the surname or the postcode when your headings are not the names it expects.
• Preview Results shows each record in place, with Find Recipient and Check for Errors beside it.
• Finish & Merge produces the result: Edit Individual Documents, Print Documents or Send Email Messages.

Choose Edit Individual Documents even when the letters are going straight to the printer. It gives you one document with every letter in it, which you can read through, correct and then print. A print run cannot be read first, and an email merge cannot be taken back.

Envelopes and labels

Envelopes. Mailings → Envelopes in Word prints one envelope for an address you type or select; for a merge, Start Mail Merge → Envelopes makes the main document an envelope and the list fills it. Choose the size that fits what goes inside: a C5 takes A4 folded once, a DL takes A4 folded in three, a C4 takes A4 flat, and a US No. 10 takes US Letter folded in three. How an envelope feeds — face up or down, which edge first — depends on the printer, and Word’s Envelope Options show what it expects. Print one on plain paper first and hold it against a real envelope.

Labels. Choose the label by the product number printed on the box: Avery L7160 is 21 labels on an A4 sheet, Avery 5160 is 30 on US Letter. The number carries the label’s size, the gaps between labels and the sheet’s margins, which is why a “close enough” layout has drifted off the labels by the bottom of the page. In a labels merge every label after the first starts with a «Next Record» field, so the sheet takes a new recipient per label rather than the same one 21 times; lay out the first label and Update Labels copies it to the rest.

Print labels at actual size — 100%, with any “fit to page” option off — because scaling moves each row a little further from its label. Test on plain paper first, held up to the light against a sheet of labels.

Where merges go wrong, and the fix for each

Dates and amounts in the wrong format. Word’s merge from an Excel workbook reads the value stored in a cell, not the format it is shown in. A date shown as 14 March 2026 can arrive as 3/14/2026, £1,250.00 as 1250, 25% as 0.25, and a calculated figure with a long tail of decimals. There are two fixes. Give the field a format of its own: press Alt+F9 to show the field codes, change { MERGEFIELD Due_Date } to { MERGEFIELD Due_Date \@ "d MMMM yyyy" }, or an amount to { MERGEFIELD Amount \# "£#,##0.00" }, and press Alt+F9 again. Or add a column to the spreadsheet that turns the value into text, such as =TEXT(C2,"d mmmm yyyy"), and merge that column instead.

Blank lines in the address. An address laid out by hand leaves a gap wherever a record has nothing for a line; the second address line is the usual culprit. Word leaves out a line that holds nothing but an empty field, but not one that also holds a space, a comma or a label such as “Tel:”, and other programs vary. Use Address Block, which leaves empty lines out, or put the optional line inside an If…Then…Else rule so the line and its break appear only when the field has something in it. And look for cells that seem empty but hold a space.

The wrong sheet. A workbook with several sheets makes the merge ask which one, and taking the first by habit merges whatever is on it: last year’s list, a summary, a pivot table. Give the sheet that holds the list a name that says so, and pick it by that name.

Rows you filtered out still get letters. A filter set in the spreadsheet hides rows on screen; the merge still reads them. Filter in Edit Recipient List instead, or copy the rows you want to a sheet of their own.

Fields with no column. A main document reused with a new list keeps fields named after the old list’s columns. Check for Errors finds them, and Match Fields or renaming the columns puts them right.

The list has moved. The main document remembers where its list was. Move or rename the workbook and the merge asks for it again the next time the document opens.