The Mail Merge feature of Microsoft Word is a great tool, saving hours of work on things like letters, labels, name tags etc. etc., but it has one really annoying habit that I get asked to solve on a regular basis. When things like dates and numbers are stored in an Excel spreadsheet to be used by Word in a mail merge, they very annoyingly lose their formatting during the transfer from the one application to the other.

The answer is all down to how Word and Excel exchange the data and what method they use.

Dynamic Data Exchange (DDE) is one method available in the Microsoft Windows® operating system for transferring data between applications. It uses shared memory to exchange the data and keeps the formatting you’ve used in Excel.

To choose the DDE data transfer method when you’re connecting to an Excel worksheet during a mail merge, all you have to do is select one check box in Word:

1. On the FILE ribbon, click Options, and then click the Advanced tab.
2. In the General section, click Confirm file format conversion on open.

This is an Office 2013 screenshot, but it is the same in both the 2007 and 2010 versions (use the Office Button in 2007 instead of the FILE ribbon)

At the step in the mail-merge process when you connect to your data file, after you locate the Excel worksheet you want to connect to, the Confirm Data Source dialog box opens. Click MS Excel Worksheets via DDE (*.xls), and then click OK. The numbers and dates from the Excel spreadsheet will now look exactly the same in your merged documents as they look in the worksheet cells.