EXCEL AND LISTS
Mail merge from Excel: letters without lost zeros, wrong greetings or blank lines
Mid November, and the customer list arrives as an Excel export from the ERP system, 320 rows. Your managing director wants the holiday letter with a personal greeting on every customer's desk by early December. The first test print shows a greeting that reads "Dear Hoffmann" with no Mr or Ms, a Leipzig address with the postcode 4109, the voucher value without decimals and a date in a format nobody ever typed into Excel.

A wrong letter to a customer is quick to print and hard to take back. If you only run a mail merge once a year, it can easily cost a whole day of searching, fixing and reprinting, and you still end up with the uneasy feeling that one mistake slipped through.
This guide explains why a mail merge from Excel goes wrong so often and how to prepare the list and Word so every letter is right on the first print run. With formulas, a checklist and a worked example.
Quick answer: turning an Excel list into an error free mail merge
A mail merge from Excel happens in Word: you connect a letter to your Excel list, and Word fills in name, address and other fields for each row. Most errors do not start in Word but in the list. Fix the list first and there is very little left to do in Word.
- Prepare the list
All data on the first worksheet, one row per recipient, clear column headings. Find empty fields and inconsistent salutations before you start.
- Build the tricky columns as text
Prepare postcode, date and amount in helper columns as text, exactly as they should appear in the letter, plus a ready made greeting.
- Save, then connect
Save the list and only then connect it in Word under Mailings > Start Mail Merge > Letters.
- Check the hard cases
In the preview, look at the difficult records: an address without a contact name, a customer abroad, a postcode starting with zero.
- Merge first, print second
Use Finish & Merge > Edit Individual Documents to create one document with all letters, read through it, then print.
Why a mail merge from Excel goes wrong so often
The main reason is in Microsoft's documentation: Word takes dates, currency and numbers as they are stored in Excel, not as Excel displays them. A cell that shows €50.00 actually holds the number 50. The cell format stays behind in Excel.
On top of that come errors already sitting in the list. If the ERP system exports a postcode as a number, Excel turns 01067 into 1067. The salutation column says Mr in one row, Mr. in the next and nothing in a third. Some customers have no contact name, others no company. And if you keep editing the list after Word is connected, you get letters with outdated data.
- Postcode without its zero: Microsoft states that leading zeros are dropped during a mail merge if the column is not formatted as text.
- Date and amount in the wrong format: Word sees the stored value, not the cell formatting.
- Wrong or doubled greeting: abbreviations, empty fields and company addresses without a person do not fit into one greeting line.
- Blank lines in the address: an empty field leaves a gap when each address line is its own field.
- Wrong worksheet or outdated data: Microsoft recommends keeping all data on the first sheet and finishing changes before connecting the list to Word.
Step 1: prepare the Excel list for the mail merge
Work in a copy of the export and add a helper column for every tricky value. The original values stay in place, and Word only gets the prepared columns. In the examples, the salutation is in column A, the surname in B, the postcode in E, the country in F, the date in G and the amount in H, each starting in row 2.
Postcode: bring back the leading zero
If the postcodes are still intact, select the column and choose Home > Number > Number Format > Text, as Microsoft recommends for mail merges. If Excel has already removed the zero, use a helper column: =IF(F2="DE",TEXT(E2,"00000"),E2). TEXT pads German postcodes to five digits, so 1067 becomes 01067. The condition matters: Austrian and Swiss postcodes have four digits and must not get a zero, or Vienna's 1010 turns into a wrong 01010.
Greeting: a ready made greeting instead of rules in Word
First make the salutation column consistent. Filter it with Data > Filter and look at the values it contains. Replace variants such as Mr. with Mr and Ms. with Ms using Home > Find & Select > Replace. Then one formula builds the full greeting: =IF(A2="Ms","Dear Ms "&B2,IF(A2="Mr","Dear Mr "&B2,"Dear Sir or Madam")). Where no contact name is on file, the neutral greeting appears automatically.
Date and amount: as text, exactly as they appear in the letter
For a date, use =TEXT(G2,"dd/mm/yyyy"), for an amount =TEXT(H2,"#,##0.00"). Type the currency symbol in Word right before or after the field, which is also what Microsoft recommends. Because TEXT turns numbers into text, keep the original columns for calculations. Format percentages as text too: according to Microsoft, the percentage format otherwise multiplies the value by 100.
Find empty fields before Word does
Filter the company, surname, street and town columns one after the other for (Blanks). You see which addresses are incomplete and can decide beforehand whether to complete them or leave them out of this mailing. At the end, all data sits on the first worksheet, the file is saved and stored locally on your computer.
Step 2: create and check the mail merge in Word
- Set up the document
Open your draft letter and choose Mailings > Start Mail Merge > Letters.
- Connect the list
Under Select Recipients > Use an Existing List, open the Excel file and choose the worksheet with the data.
- Choose recipients
In Edit Recipient List, clear the check box for everyone who should not get this letter.
- Insert fields
Use Insert Merge Field to add the helper columns: the greeting instead of salutation and surname, the prepared postcode instead of the original column.
- Check the preview
Click through the letters with Preview Results and jump to the difficult records on purpose.
- Merge
Finish & Merge > Edit Individual Documents creates one document containing all letters. Read through it, then print.
The last step does the most to prevent embarrassing mistakes. Do not print straight from the preview; check the merged document. Look closely at four letters at least: an address without a contact name, one abroad, a postcode starting with zero and the letter with the longest company name. If these four are right, the rest usually are too.

Word can also format dates and numbers itself, using switches in the field codes, which you reveal with Alt+F9. Microsoft documents this approach. If you only run a mail merge once a year, text columns in Excel are usually faster and safer, because you can see there exactly what will appear in the letter.
Example: five rows from the customer list
| Salutation | Surname | Postcode (export) | Country | Greeting | Postcode for the letter |
|---|---|---|---|---|---|
| Ms | Schmitz | 50667 | DE | Dear Ms Schmitz | 50667 |
| Mr | Wagner | 1067 | DE | Dear Mr Wagner | 01067 |
| Ms | Gruber | 1010 | AT | Dear Ms Gruber | 1010 |
| 4109 | DE | Dear Sir or Madam | 04109 | ||
| Mr. | Becker | 44135 | DE | Dear Sir or Madam | 44135 |
Rows 2 and 4 show the lost zero that the formula restores. Row 3 keeps four digits because the address is in Austria. Row 4 is a company address without a contact name and gets the neutral greeting. Row 5 shows why you make the salutation column consistent first: to the formula, Mr. is neither Mr nor Ms, so Mr Becker would be addressed as "Dear Sir or Madam". After replacing Mr. with Mr, this letter is right too.
Envelopes and labels: the second stumbling block
Once the letters are printed, the envelopes follow. Window envelopes are the simplest: the address from the merged letter shows through the window, so you need no labels. For cards and envelopes without a window, Word also offers Labels under Mailings > Start Mail Merge. If you only do this once a year, though, you look up the steps from scratch each time, and the first print run is often a few millimetres off.
The free print labels tool is quicker. Drag in the address list from Excel, choose the label sheet, for example 70 × 37 mm like Avery Zweckform 3474, and download a ready PDF. The tool recognises company, name, street, postcode and town from the column headings, restores the lost zero in German postcodes and leaves out empty fields instead of printing blank lines. It lists incomplete addresses for you to check. On a partly used sheet, you enter the first free label. Your file stays in your browser.
Common mistakes in a mail merge from Excel
- Editing the list after connecting it: finish your changes in Excel and save before you connect the list in Word.
- Padding every postcode to five digits: without a condition, Austrian and Swiss postcodes get a wrong zero too.
- Taking the salutation column as it is: abbreviations, typos and empty fields lead to wrong greetings. Filter first, then make the values consistent.
- Trusting the Excel formatting: Word takes the stored value. Anything that must appear exactly as shown belongs in a helper column as text.
- Printing documents directly: without checking the merged document, you see mistakes only on paper, or your customer does.
- Data spread over several sheets: Microsoft recommends keeping all mail merge data on the first worksheet.
Limits, effort and the cost of a reprint
A single mail merge a year is very manageable with this guide. It gets tedious when mailings come up more often: price updates, circulars about holiday closures, annual letters, invitations. Each time the list comes fresh from the ERP system, and each time the helper columns are missing again.
A conservative calculation: four mailings a year at 6 hours each for the list, the merge, the check and the labels add up to 24 hours. At an assumed internal cost of €38 an hour, meaning wages plus overheads as preset in the cost calculator, that is €912 a year. Postage and paper for a reprint come on top. Run your own numbers in the office work cost calculator.
When mailings come up regularly: an office helper
An office helper is a small program we set up for exactly this task. It takes the export from your ERP system, prepares postcode, greeting, date and amounts by fixed rules, flags incomplete and duplicate addresses and produces letters, labels or mailing lists from them. You see the result before anything is printed and approve it. Word, Excel and your ERP system stay, and nobody has to learn new software. The page automate Excel work gives the overview.
If you send more than the holiday letter, such as price updates or circulars several times a year, it is worth looking at the whole workflow. In the free workflow check, describe where the addresses come from and which letters are made from them. You get a personal, free assessment within 1–2 working days. We deliver small projects in 3–7 days once the workflow is agreed, at a fixed price from €1,990 excl. VAT.
Sources and further reading
- Microsoft Support: Seriendruck mithilfe einer Excel-Tabelle
- Microsoft Support: Vorbereiten einer Excel-Datenquelle für einen Word-Seriendruck
- Microsoft Support: Für Seriendrucke verwendbare Datenquellen
- Microsoft Support: Einfügen von Seriendruckfeldern
- Microsoft Support: Erstellen eines Verzeichnisses mit Namen, Adressen und weiteren Informationen
