Rounding in a Mail Merge

Marylea uses mail merge to place information from an Excel worksheet into the merged documents. In Excel, she can format a cell to round to the nearest two decimal places. She wonders how she can have the rounded value merged into her Word documents rather than the underlying data that has many more decimal places.

This happens because of how Excel works with numbers. What you see in an Excel worksheet is, typically, a formatted number. Let's say that you see a number such as 1234.56. That doesn't mean that is the number in the actual cell; chances are good that it is a much more precise number, such as 1234.5563289 or 1234.562567. Either could be rounded to what you see.

However, when you use mail merge in Word and pull information from the worksheet, it doesn't pull the number that you see in the cell (the formatted number). It pulls, instead, the underlying, more precise (unformatted) number. Thus, you get the result that Marylea is experiencing.

To get around this issue there are two possible solutions. You can either change what is being merged from Excel or you can change how the value is formatted in Word. If you go the former route, you'll need to go into your Excel worksheet and modify the actual underlying, formatted number so it contains values no more precise than two decimal places. This, however, could lead to a problem of a different nature in your mail merge—not enough digits. For instance, if an unformatted value in Excel is 987.6, that is what gets passed to Word—a value without the second digit to the right of the decimal point.

This brings us back to the second possible solution, which is to change how the value is formatted in Word. This approach will typically give the best results. In order to implement this approach, you'll need to remember that mail merges are made possible through the use of fields—that is how you indicate in a merge document what should be merged and where it should appear.

So your first task is to locate, in your merge document, the field that is responsible for actually merging the value from Excel. Once you locate it, press Alt+F9. The field code (all the field codes in the document) should expand so that you can see it all, and it will look similar to this:

{ MERGEFIELD MyValue }

This assumes the value being merged from Excel is called "MyValue". Since it probably isn't, you'll see some different field name instead of "MyValue." Further, if there is anything else in the field code besides "MERGEFIELD" and "MyValue", you'll want to delete that other info so that your field code contains just these two elements. (Note that if the field name has quote marks around it, you should leave those quote marks in place.)

Now you want to add the formatting switch to your field code. This is done by using a backslash (\) followed by a hash mark (#), then the pattern you want to use. For instance, let's say you want a very simple pattern that rounds to two decimal points. In that case, you could modify your field code to look like this:

{ MERGEFIELD MyValue \#0.00}

If you are dealing with larger values and you want to include thousands separators, then your pattern can look like this:

{ MERGEFIELD MyValue \#,0.00}

If you want to have the value include a dollar sign, then include that, as well:

{ MERGEFIELD MyValue \#$,0.00}

When done modifying the field code, press Alt+F9 again to hide the underlying field codes and, instead, display results. At this point you should be able to perform your mail merge, as you normally would, and your numbers pulled from Excel should be rounded and formatted as desired.

WordTips is your source for cost-effective Microsoft Word training. (Microsoft Word is the most popular word processing software in the world.) This tip (11313) applies to Microsoft Word 2007, 2010, 2013, 2016, 2019, and 2021.