Enforce Excel Formatting in Mail Merge

Excel, Word | Comments Off on Enforce Excel Formatting in Mail Merge

Advanced Mail Merge Settings

Creating multiple documents with unique recipients, sales totals, or dates is accomplished with Mail Merge. Mail merge takes two files (the main document and the data source) and merges them into one.

Basic addition: 1 + 2 = 3

1 Main Document (Start Mail Merge)
+ 2 Data Source (Select Recipients)
3 Final Document (Finish & Merge)

It’s critical to realize when you’re using formatted numbers in Excel (such as zip codes, where Excel drops the leading zeros unless formatted as other than a number), those formats will not transfer into the merged document unless you’ve instructed Word to do so. The zip code for Hoboken, NJ for example, 07030, would merge as 7030. You can see the problems this would create on address labels.

Enforce Excel Formats in the Mail Merge

When merging numeric data that is formatted in Excel to display as currency, percentages, or text, you’ll want to be sure to preserve the numeric formatting by using Dynamic Data Exchange  before you connect to the Excel worksheet


  1. Display Word Options (Click FILE Options)
  2. Click Advanced and scroll down to GENERAL.
  3. Select the Confirm File Format Conversion on Open check box. Click OK.

After you’ve completed the above steps:
[1] Begin the mail merge
MAILINGS Tab Start Mail Merge GroupStart Mail Merge Tool type

[2] Connect to the data source
MAILINGS Tab Start Mail Merge GroupSelect Recipients Use an existing list.

Locate the Excel worksheet, click OK.

If you don’t see MS Excel Worksheets via DDE (*.xls) select the Show all check box then select MS Excel Worksheets via DDE (*.xls) OK.

In the Microsoft Office Excel dialog box, for Named or cell range, select the cell range or worksheet that contains the information that you want to merge, and then click OK.

From this point you will continue the mail merge as usual, adding fields and formatting as needed.

[3] Finish the merge
MAILINGS Tab Finish GroupFinish & Merge appropriate option

Contact us if you have questions!