You’ve got a spreadsheet full of prices and other information, and you’re using Microsoft Word to merge all that data into a document. You’re frustrated, because even though your prices have dollar signs, commas and decimal points in Excel, none of that formatting has carried over to Word through the mail merge. Of Zen and Computing can help you make mail merge obey!
This is a particularly frustrating problem, because it doesn’t seem like you’re doing anything wrong. You know how to do a mail merge… you’ve done a hundred mail merges. What makes this one different?
The reality of the situation is that you’re not necessarily making a mistake. This is a communication problem, much like the sort of person-to-person communication problems that cause bigger issues throughout the workings of many businesses every day. The reason your prices are not formatting correctly is because of a few steps usually not performed over the course of the generic mail merge process. Due to these missing steps, Excel is sending your price numbers to Word, but failing to communicate any of the formatting.
Many others have had the same problem, and the Microsoft Office Answer Box has published the answer in “Answer Box: Numbers don’t merge right in Word“. The Answer Box presents three different solutions:
- Use Dynamic Data Exchange
- Muck around with field codes in Word
- Format cells in an Excel worksheet
Let’s discuss the last two options first.
Muck around with field codes in Word
“Muck around with field codes in Word” requires you to reveal some extra formatting codes by pressing ALT+F9, and then making some slight modifications. These modifications tell Word to format the plain numbers it’s receiving from Excel with a dollar sign, a comma and a decimal. This is not a particularly good solution for two reasons:
- It requires you to edit field codes, a task akin to programming. It’s entirely too confusing for the average person.
- It requires you to specify a hard number for the maximum amount of digits in your price. If you have many different prices with different numbers of digits, you must pick the largest number of digits. For any price with fewer digits, Word takes the difference and leaves that many blank spaces between the dollar sign and the first digit. The recipient of your document could easily forge in extra digits.
Format cells in an Excel worksheet
“Format cells in an Excel worksheet” is not preferable either, because it makes it difficult to perform calculations with your data. This step has you change the format of your price column to “text”, instead of “currency”. When it’s formatted as “currency”, your price column stores the unformatted numbers, and Excel adds the formatting on the screen for you. When you switch from “currency” to “text”, Excel actually writes the formatted number, including dollar sign, comma(s) and decimal point, into the column as text instead of a digit. Your mail merge will show the correctly formatted numbers, but as you can probably guess, you don’t want to be performing future integer calculations using text instead of integers.
Our Recommendation: Use Dynamic Data Exchange
Selecting Dynamic Data Exchange (DDE) slightly changes the underlying method that Excel uses to send your data to Word for the mail merge, but does not modify your original Excel data. Your mail merge displays the correctly formatted prices, and your Excel data remains intact for whatever you want to do with it in the future.
From the Answer Box article:
- On the Tools menu, click Options, and then click the General tab.
- Click Confirm conversion at Open.
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. In the Microsoft Excel dialog box, for Named or cell range, select the cell range or worksheet that contains the information you want to merge, and then click OK.
Visit “Numbers don’t merge right in Word at the Microsoft Office Answer Box for the full instructions on using DDE, as well as the other two methods that we’ve discussed in this article. This problem also applies to other types of numbers, such as dates, and the Answer Box article mentions those as well.
