Change How Word Display Numbers in Mail Merge With Excel

Can you round numbers in Word?

When using Excel spreadsheets in a mail merge process, many users frequently run into difficulty formatting the fields that contain decimals or other numerical values. To ensure the data contained in the fields are inserted correctly, one must format the field, not the data in the source file.

Unfortunately, Word does not provide a way for you to change how many decimal places are displayed when working with numbers. While there are ways to work around this limitation, the best solution is to include a switch in the merge field.

Instructions in this article apply to Word for Office 365, Word 2019, Word 2016, Word 2013, and Word 2010.

What Is a Switch?

Field code switches allow you to modify the resulting data that you merge into a document. There are two types of switches in Word:

  • General switches help you format the data. For instance, you could use a general switch to select uppercase letters.
  • Field switches are field-specific, changing the behavior of the fields to which they are applied.

All switches begin with a backslash, followed by a character (or characters) that describe its function. A Numeric Picture Field Switch (\#) determines the display of a numeric result.

How to Perform The Numerical Switch Function

Learn how to use the Numeric Picture Field Switch (\#) to specify how many decimal places to display in your Word mail merge.

If the result of a field is not a number, this switch has no effect.

  1. With the mail merge main document open, press Alt + F9 to view the field codes.

    The field code will look something like {MERGEFIELD “fieldname” }.

    Screenshot of fields in mail merge document
  2. Directly after the end quote around the field name type \# followed by:

    • 0 for rounded whole numbers
    • 0.00 if you want to round the number to two decimal places (or 0.000 if you want to round the number to three decimal places and so forth)
    • ,0 for rounded whole numbers with a thousands separator
    Screenshot of Numeric Picture Field Switch
  3. Once you have added your field switch, press Alt + F9 to display the fields instead of the field codes.

Your number will appear rounded to the decimal place you specify. If it does not display immediately, refresh the document by minimizing it to the toolbar and reopening it. If the field value still does not display correctly, you may need to refresh the document again or close and re-open your document.