Formatting Issues

How to Preserve Excel Decimal Places in a Word Mail Merge

Nimra Malik Sep 27, 2026 870 views

Question details

The user needs to keep exact decimal formatting from Excel when importing numerical data into a Word mail merge document.

How to Preserve Excel Decimal Places in a Word Mail Merge

Product: Word, Excel
Device & OS: not provided
Scenario: Performing a mail merge in Word using an Excel spreadsheet as the data source.
Observed behavior: Numbers that are formatted with two decimal places in Excel lose their formatting and display with multiple unexpected decimal places in the Word document.

Before you start

Ensure your Excel data source is saved and closed before editing mail merge fields in Word to prevent file-locking conflicts and ensure the latest data is retrieved.

Solution 1Recommended

Format the Merge Field Code with a Numeric Switch

The most reliable way to enforce two decimal places in a mail merge is by applying a numeric format switch directly within Word's field codes.

Word does not automatically inherit number formatting from Excel during a mail merge because it connects to the raw data rather than the display text. To fix this, you must instruct Word on how to display the number by adding a specific formatting switch to the merge field.

Toggle the Field Codes

In your Word document, right-click the problematic merge field and select 'Toggle Field Codes', or click the field and press 'Shift+F9' on your keyboard.

Add the Numeric Switch

Locate the exposed field code, which should look like { MERGEFIELD MyField }. Click inside the bracket at the end of the text and add the numeric switch \# 0.00 so the code becomes { MERGEFIELD MyField \# 0.00 }.

Update the Field

Press 'F9' to update the field and hide the code. Click 'Preview Results' on the Mailings tab to verify that the numbers are now restricted to two decimal places.

Format the Merge Field Code with a Numeric Switch

Formatting Currency: If you want the numbers to display as currency with a dollar sign and comma separators, use the switch \# $#,##0.00 instead.

Easy Mail Merge in WPS Office

Create Professional Mail Merges with WPS Office

WPS Office provides a highly compatible and user-friendly Mail Merge feature, seamlessly connecting WPS Spreadsheets data to WPS Writer documents while preserving precise formatting.

1. Launch Mail Merge: Open WPS Writer, navigate to the 'References' tab, and click the 'Mail Merge' button to reveal the mailings ribbon.
2. Connect Data Source: Click 'Open Data Source' and select your WPS Spreadsheets (.xlsx) file containing the numeric data.
3. Insert and Format Fields: Click 'Insert Merge Field' to place your data into the document. If you need to adjust decimal formatting, press Shift+F9 to toggle field codes and add the \# 0.00 switch.
4. Preview and Complete: Select 'View Merged Data' to preview the correctly formatted numbers, then click 'Merge to Printer' or 'Merge to New Document' to finish.

Fully compatible with Microsoft Office document formats (.docx, .xlsx).Intuitive Mail Merge wizard makes data linking and field insertion effortless.Supports advanced field code editing for precise numeric and date formatting.Lightweight, fast, and free to use for everyday office tasks.

microsoft office alternative - wps office

Frequently Asked Questions

Why does Word change my Excel numbers during a mail merge?

By default, Word connects to Excel using OLE DB, which imports the raw, underlying data from the spreadsheet rather than the formatted display text. Because raw data can have many trailing decimals, Word displays them exactly as they are stored.

Can I format the numbers in Excel instead of using Word field codes?

Yes. While using field codes in Word is standard, you can bypass this by converting your Excel numbers to text before merging. In Excel, create a new column and use the formula =TEXT(A2, "0.00"). Use this new text column as your mail merge source.

What should I do if pressing F9 doesn't update the merge field?

If pressing F9 doesn't work (which can happen depending on your keyboard's function lock settings), you can right-click the field code and select 'Update Field' from the context menu. You may also need to toggle 'Preview Results' off and back on to see the change.

How do I format percentages in a Word mail merge?

To format a decimal as a percentage (e.g., 0.25 as 25%), use the switch \# 0%. If you need decimal places within the percentage, such as 25.50%, use the switch \# 0.00%.

Document & File Issues