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
BEFORE CONNECTING TO THE DATA SOURCE,
DO THE FOLLOWING IN WORD:
- Display Word Options (Click FILE ⇒ Options)
- Click Advanced and scroll down to GENERAL.
- Select the Confirm File Format Conversion on Open check box. Click OK.
 Connect to the data source
MAILINGS Tab ⇒ Start Mail Merge Group ⇒ Select 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.
 Finish the merge
MAILINGS Tab ⇒ Finish Group ⇒ Finish & Merge ⇒ appropriate option
Contact us if you have questions!