One issue when performing a standard mail merge with Excel Data is that it only brings over the content of Excel cells. Additional formatting like the $ for currency, the % for percentages, the hyphens and parentheses in the special phone number format, and characters from custom number formats are not retained. Generally, what you see you in the formula bar in Excel is what you see brought into Word.
To get the additional formatting in Word, you need to perform a Dynamic Data Exchange (DDE) as follows:
Step 1: Change Word settings so that you are able to ask for DDE during the Mail Merge. (You only need to do this step one time; then you are good for all future Mail Merges requiring DDE.)
- Open Word, choose File > Options > Advanced.
- Under General, select the Confirm file format conversion on open check box.
Step 2: Use the Step by Step Mail Merge Wizard as follows:
- On the Mailings tab, choose Start Mail Merge > Step by Step Mail Merge Wizard.
- In the Mail Merge pane, under Select recipients, choose Use an existing list.
- Under Use an existing list, choose Browse, and then open your formatted spreadsheet.
- In the Confirm Data Source dialog box, select the Show all check box
- Choose MS Excel Worksheets via DDE (*.xls) > OK.
- In the Microsoft Excel dialog box, choose Entire Spreadsheet > OK.
- When the Mail Merge Recipients dialog box opens, choose OK.