How to Format Excel Mail Merge Values to Two Decimal Places in Word
Question details
Users need to restrict the number of decimal places for Excel numeric data when it is imported into a Word document via the Mail Merge feature.
- Product
- Microsoft Word, Microsoft Excel
- Device & OS
- not provided
- Scenario
- Importing an Excel data source containing numeric values into a Word document for a mail merge.
- Observed behavior
- Word imports the raw underlying data from Excel, causing numbers to display with more decimal places than intended instead of retaining the formatted two decimal places.
Ensure your Excel data source is fully saved and closed before initiating the Mail Merge in Word to prevent file locking and read-only errors.
Apply a Numeric Picture Switch to the Word Merge Field
Modifying the field code directly in Word by adding a numeric picture switch is the most reliable way to enforce exactly two decimal places on imported Excel numbers.
By default, Word pulls the raw unformatted data from Excel. To control how this data appears in your final document, you must apply specific formatting rules known as 'switches' to the individual merge fields.
Open your Word document, navigate to the 'Mailings' tab, choose 'Start Mail Merge', and select your document type (e.g., Letters).
Click 'Select Recipients', choose 'Use an Existing List', and locate the saved Excel workbook containing your numeric data.
Place your cursor where the number should appear in the document. Click 'Insert Merge Field' and select the appropriate column header from your Excel list.
Press 'Alt + F9' simultaneously on your keyboard. This will change the display of the merge field to show its underlying code, looking similar to { MERGEFIELD Amount }.
Click inside the closing curly bracket of the field code, add a space, and type the numeric formatting switch exactly as follows: \# "#,##0.00".
Press 'F9' to update the field with your new formatting instructions.
Press 'Alt + F9' again to hide the field codes. Finally, click 'Preview Results' on the Mailings tab to verify that your numbers now display exactly two decimal places before selecting 'Finish & Merge'.
Effortlessly Handle Mail Merge and Data Formatting in WPS Office
WPS Office Writer provides powerful, easy-to-use Mail Merge capabilities that are seamlessly compatible with Excel spreadsheets. You can accurately format numbers, currencies, and dates using intuitive field code editing without encountering data import errors.
- 1. Launch WPS Writer: Open WPS Office, create or open your main document, and navigate to the 'Mailings' tab.
- 2. Connect Data Source: Click 'Open Data Source' and browse your computer to select the Excel spreadsheet containing your values.
- 3. Insert and Format Fields: Click 'Insert Merge Field' to place your data into the document. Right-click the inserted field, choose 'Toggle Field Codes', and easily append your formatting switch.
- 4. Generate Documents: After confirming your format, click 'Merge to New Document' or 'Merge to Printer' to process your fully formatted mail merge.
Frequently Asked Questions
Why does Word ignore my Excel decimal formatting during a mail merge?
Word's mail merge feature pulls the raw underlying data from the Excel spreadsheet rather than the visual cell formatting. If an Excel cell contains 14.567 but is formatted to visually display as 14.57, Word will still import the raw 14.567. You must use a numeric picture switch in Word to enforce the visual format.
Can I format the mail merge value as currency with a dollar sign?
Yes. To format the value as currency, you can include the currency symbol directly inside the numeric switch's quotation marks. For example, editing the field code to include \# "$#,##0.00" will format the imported value with a dollar sign, comma separators, and exactly two decimal places.
What should I do if the Alt+F9 shortcut doesn't work to show field codes?
If pressing Alt+F9 does nothing, your keyboard's function keys might be mapped to media controls. Try pressing the 'Fn' key alongside Alt+F9 (Fn + Alt + F9). If that still fails, you can simply right-click directly on the merge field text in your document and select 'Toggle Field Codes' from the context menu.
