Mail Merge formatting issues in Word: causes and fixes

How to use Dynamic Data Exchange to connect to Excel sheet

Before starting a mail merge, perform the following steps in Microsoft Word.

1. Go to File > Options > Advanced.
2. Scroll down to the General section, select the check box Confirm file format conversion on open and click OK.
3. Start your Mail Merge as usual (the detailed steps are here). When it comes to selecting recipients, choose Use an existing list.
4. Browse to your spreadsheet, select it, and click Open (or double-click the file).
5. In the Confirm Data Source dialog box that opens, check the Show all box in the lower left corner, then choose MS Excel Worksheets via DDE (*.xls), and click OK.
6. Click Entire Spreadsheet, and OK.

It may take Word quite a while to connect to your Excel data source via DDE, so please be patient - this will save you even more time in the long run :)

Now, all numeric values in the Word mail merge document retain their original formats.

Mail merge: number format

To get numbers to appear in the right format (the same or different than in your Excel file), use the following numeric switch codes.

For example, to format a whole number with a thousand separator, use the numeric switch \# ,0 so the Number field changes to:

{ MERGEFIELD Number\# ,0 }

Mail merge: currency format

To format currency in mail merge, these are the codes to use:

For example, to apply a dollar format in mail merge, add the numeric switch \# $,0 to the Currency field:

{ MERGEFIELD Currency\# $,0 }

As a result, the number 3000 gets formatted as $3,000 with no decimal places.

Mail merge: percentage format

The way you format percentages in a Word mail merge document depends on how the source values are formatted in your Excel sheet.

If the General or Number format is used in Excel, a corresponding number will appear in Word. To format that number as a percent, add one of the following numeric switches.

For instance, to format a number as a whole percent, edit the Percent field as follows:

{ MERGEFIELD Percent\# 0% }

As a result, the number 50 is formatted as 50%.

If the Percent format is applied to Excel cells, Word will display an actual value behind the percentage formatting, which is a decimal number. For example, an Excel value of 50% will appear as 0.5 in Word. To convert it to a percent, you need to first multiply a decimal by 100, and then use an appropriate numeric switch. Here are the detailed steps:

1. Select the merge field that you want to format as a percent, e.g. «Percent». Note, the quotation marks should be included in the selection.
2. Press Ctrl + F9 to wrap the selected field in another one like this: { «Percent» }
3. Edit the field so that you get one of the following:
   • Rounded whole percent: {=«Percent»*100 \# 0%}
   • Percent with two decimal places: {=«Percent»*100 \# 0.00%}
4. Place the cursor anywhere in the field and press F9 to update it.

   Note. A merge field containing a formula will display a value, not the field name, even when not in the preview mode. Don't worry, it's a normal behavior. To make sure the value is not static, click the arrows in the Preview Results group to switch between the recipients. To view the field code, select the value and press the Shift + F9 keys together. If all done correctly, you will see something like this:
   { ={MERGEFIELD Percent }*100 \# 0% }

Mail merge: date and time format

As with numbers and currency, you can change the date format in mail merge by using a numeric switch. The table below lists codes for a few commonly used date/time formats.

For example, to apply a full date format, change the Date merge field format in this way:

{ MERGEFIELD Date\@ "dddd, MMMM dd, yyyy" }

How to change format of the current date and time in Word mail merge

To insert today's date and current time in a mail merge document, you can use the following shortcuts:

• Alt + Shift + D - insert the DATE field that displays the current date.
• Alt + Shift + T - insert the TIME field that displays the current time.

This will add the date and time in the default format. To change it, you can use a numeric switch as described above. Or you can apply the desired date/time formatting in a visual way.

1. Select the Date or Time field the format of which you want to change.
2. Press Shift + F9 to display the field coding, which may look something like { DATE \@ "M/d/yyyy" }
3. Right-click the selected field and choose Edit Field… from the context menu.
4. In the Filed dialog box, select the desired format for the Date field, and click OK.

   Tip. If you want to preserve formatting during updates, select the corresponding check box in the lower right corner.