Continue to WhatsApp?

The chat opens in WhatsApp, in the app or in a new tab. You leave our website, and data such as your IP address is sent to WhatsApp (Meta).

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.

Kraft paper Christmas card on a light envelope against a dark background, a picture of the yearly holiday letter to customers
Photo: Kelly Sikkema on Unsplash

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.

  1. 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.

  2. 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.

  3. Save, then connect

    Save the list and only then connect it in Word under Mailings > Start Mail Merge > Letters.

  4. 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.

  5. 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

  1. Set up the document

    Open your draft letter and choose Mailings > Start Mail Merge > Letters.

  2. Connect the list

    Under Select Recipients > Use an Existing List, open the Excel file and choose the worksheet with the data.

  3. Choose recipients

    In Edit Recipient List, clear the check box for everyone who should not get this letter.

  4. 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.

  5. Check the preview

    Click through the letters with Preview Results and jump to the difficult records on purpose.

  6. 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.

Woman at a desk checking printed pages, the way you check a test print before the letters go out
Photo: Anastassia Anufrieva on Unsplash

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

Raw data and prepared helper columns
SalutationSurnamePostcode (export)CountryGreetingPostcode for the letter
MsSchmitz50667DEDear Ms Schmitz50667
MrWagner1067DEDear Mr Wagner01067
MsGruber1010ATDear Ms Gruber1010
4109DEDear Sir or Madam04109
Mr.Becker44135DEDear Sir or Madam44135

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

FAQ

Quick answers.

How do I do a mail merge from Excel to Word?

Prepare the Excel list and save it. In Word, choose Mailings > Start Mail Merge > Letters, then Select Recipients > Use an Existing List and pick your Excel file. Add fields with Insert Merge Field, check them with Preview Results and create the finished letters with Finish & Merge.

Why does the mail merge drop the leading zero in postcodes?

Because the postcode is stored as a number, and numbers have no leading zero. Microsoft recommends formatting the column as text before the merge. If the zero is already missing in Excel, =IF(F2="DE",TEXT(E2,"00000"),E2) restores it for German addresses without changing Austrian postcodes.

How do I keep the date format in a mail merge from Excel?

The easiest way is in Excel: a helper column with =TEXT(G2,"dd/mm/yyyy") returns the date as text, and Word uses it exactly as it is. Alternatively, format the field in Word with a date switch in the field code, which you reveal with Alt+F9. The Excel route is easier to check.

How do I avoid wrong greetings in a mail merge?

First make the salutation column consistent so it only contains Mr, Ms or nothing. Then an IF formula builds the full greeting, with "Dear Sir or Madam" for every address without a contact name. In Word, you insert only this one field.

Why are there blank lines in my mail merge addresses?

Usually because each address line is its own merge field and a field is empty for some customers, such as the company or the contact name. Filter the list for empty fields beforehand and check those records in the preview. For address labels, the free print labels tool leaves out empty fields automatically.

Can I print labels from the same Excel list?

Yes. In Word, use Mailings > Start Mail Merge > Labels. The free print labels tool is quicker: drag in the list, choose the label sheet, print the PDF. Print a test page on plain paper first and choose Actual size or 100% in the print dialog.

LET'S TALK ABOUT YOUR WORKFLOW

Write to us. We reply personally.

A question, an idea or a task that costs time every week? A few sentences are enough. You get an honest answer from the people who also build your office helper.

Ivan, Lukas & JanReply within 1–2 working days

  • Personal reply
  • No obligation
  • Based in Vienna, GDPR aware
Photo of Ivan
Rather talk directly?Ivan, your contact person+43 676 59 40 027office@pebblebyte.comMon to Fri 08:00–18:00
Have a specific task? Get your free workflow check

Your message

Please do not enter passwords or confidential customer data. We only use your details to handle your request. No newsletter, no sharing for advertising. Details in our privacy notice.