Why do my cell formats (i.e., $, commas, etc.) not populate correctly in a mail merge?

Dear @ginadean,

Thank you for reaching out to Microsoft Q&A forum.

I understand you're encountering an issue with cell formatting in your mail merge. From my research, Word is pulling the "raw" numerical data from Excel rather than the "visual" formatting you see in the cells. By default, Word uses OLE DB to connect to Excel, which does not retain formatting like currency symbols or comma separators.

To fix this issue, you need to add a Numeric Picture Switch to your field codes in Word. This switch will tell Word exactly how to display the number, regardless of how it was formatted in Excel.

1.Open your Word mail merge document.

2.Find the merge field that’s missing its formatting (e.g., «Amount»).

3.Right-click the field and select Toggle Field Codes or press Alt + F9 to reveal all field codes in the document.

• The field will now appear like this: { MERGEFIELD Amount }.

4.Place your cursor inside the curly brackets, after the field name.

5.Type a space and then add the appropriate formatting code:

• For Currency: { MERGEFIELD Amount # "$#,##0.00" }
• For Percentages: { = { MERGEFIELD Percent } * 100 # "0%" }

Your field should now look like this:

{ MERGEFIELD Amount # $#,##0.00 }

Breakdown of the code:

• # indicates the start of a numeric switch.
• $ adds the dollar sign.
• #,##0 adds the thousands separator (comma).
• .00 forces two decimal places.

6.Right-click the field and select Update Field, or press F9.

7.Press Alt + F9 again to return to the normal view.

Finally, click Preview Results in the Mailings tab to see the formatted currency.

Additional notes:

• If you want to apply a different format (like date or custom number formats), you can adjust the numeric picture switch accordingly.
• To ensure consistency, double-check your Excel data to confirm it’s in the correct numerical format (e.g., currency, percentage).

I hope this information is helpful! Please follow these steps and let me know if the formatting works for you. If not, we can work together to resolve this. Thank you for your patience and understanding. If you have any questions or need further assistance, please feel free to share them in the comments so I can continue to support you. I'm looking forward to your reply.

If the answer is helpful, please click "Accept Answer" and kindly upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread. 

When performing a mail merge in Microsoft Word using an Excel worksheet as the data source, you may encounter issues where the formatting of numeric data, such as currency, does not carry over correctly. This happens because Word merges the data in its native format from Excel, which does not include the formatting applied in the Excel worksheet. As a result, currency values may appear without the dollar sign or commas.

To correct this issue, you can use one of the following methods:

1. Use Dynamic Data Exchange (DDE): This method allows you to connect to the Excel worksheet in a way that retains the formatting. You can enable DDE in Word by going to File > Options > Advanced, and checking the Confirm file format conversion on open option. Then, when you select your Excel data source during the mail merge process, choose the option for MS Excel Worksheets via DDE (*.xls).
2. Format the Excel field as text: If you specifically need to retain postal codes or similar data, you can format the relevant column in Excel as text. This can help retain the exact formatting when merged into Word.

By following these methods, you should be able to retain the desired formatting during your mail merge operation.

References:

• Excel data doesn't retain its formatting in mail merge operations in Word
• Mail merge error when a data source is accessed by multiple users in Word
• You receive a "Too many different cell formats" error message in Excel
• You receive a "Too many different cell formats" error message in Excel
• Date, Phone Number, and Currency fields are merged incorrectly when you use an Access or Excel data source in Word