Rounding in a Mail Merge

Written by Allen Wyatt (last updated October 16, 2021)
This tip applies to Word 2007, 2010, 2013, 2016, 2019, and Word in Microsoft 365


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:


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. ...


Using Subtotals and Totals

You can insert subtotals and totals in your worksheets by using either a formula or specialized tools. This tip explains ...

Discover More

Graphics Marked as Changed when Comparing Documents

The tools on the Review tab of the ribbon allow you to do all sorts of markup and comparisons of documents. When ...

Discover More

Writing a Macro from Scratch

Creating macros can help extend what you can do in Excel. If you work with macros, you know that creating macros from ...

Discover More

Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!

More WordTips (ribbon)

Checkboxes in a Merged Document

When creating a mail-merge document, you may want to include some special characters, such as check-marked boxes, in the ...

Discover More

Grouping Records in a Mail Merge

Need to group records in some manner when they are used in a mail merge? It can be frustrating when your records are not ...

Discover More

Getting Rid of Mail Merge Section Breaks

When you create a group of documents from a merge file, Word normally inserts section breaks between iterations of the ...

Discover More

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

View most recent newsletter.


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 1 - 0?

2024-06-01 14:30:47

Mohamed Salah

i would want to add that some laptops doesn't support the shift + F9 combinations due to the fn key so instead try alt+Fn+F9

2021-10-18 08:24:31


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.


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.