Rounding in a Mail Merge

by Allen Wyatt
(last updated October 16, 2021)

1

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 Word in Microsoft 365.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Checking for Proper Entry of Array Formulas

Excel allows you to enter two different types of formulas in a cell: A regular formula or an array formula. If you need ...

Discover More

How to Stop a Table Row from Splitting Over Two Pages

Do you want your table rows to be split between pages? Word allows you to format the table so that rows stay together and ...

Discover More

Using Chart Titles

Titles can be a great addition to any chart. They help provide explanatory information about the information in the ...

Discover More

Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!

More WordTips (ribbon)

Maintaining Leading Zeroes

When merging ZIP Codes from a data source such as Excel, you might find that Word ends up dropping out leading zeroes in ...

Discover More

Beginning a Mail Merge

Performing a mail merge can be intimidating to some people. It needn't be; Word provides a handy step-by-step wizard that ...

Discover More

Merging Formatted Data

Using the mail merge feature of Word, you can make data from Access databases accessible for your documents. How ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in WordTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is seven less than 7?

2021-10-18 08:24:31

Beth

Thank you so much for this tip. I have wondered about this for a long time!


This Site

Got a version of Word that uses the ribbon interface (Word 2007 or later)? This site is for you! If you use an earlier version of Word, visit our WordTips site focusing on the menu interface.

Videos
Subscribe

FREE SERVICE: Get tips like this every week in WordTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.