How to Fix Excel Decimal Values Changing in Word Mail Merge
Question details
During a Word mail merge with an Excel data source, decimal values display with extra decimal digits instead of the correct format (e.g., 79,732.05 appears as 79,732.05000000000003).
- Product
- Microsoft Word and Excel
- Device & OS
- not provided
- Scenario
- Performing a mail merge in Word using an Excel spreadsheet as the data source.
- Observed behavior
- Numbers with decimals show floating-point approximations with extended decimal places in Word, ignoring the simplified formatting set in Excel.
Ensure your Excel data source document is saved and fully closed before you attempt to modify the MERGEFIELD codes in your Word document.
Apply a Numeric Format Switch to the MERGEFIELD in Word
The most reliable method is to format the field directly in Word using a numeric picture switch, which forces Word to display the specified number of decimal places.
Excel uses 64-bit binary floating-point to store decimal values, which often results in underlying approximations. Word pulls this raw underlying data rather than the displayed formatting from your spreadsheet.
Press Alt + F9 (or Option + F9 on Mac) in your Word document to reveal the field codes. The merged field will look something like { MERGEFIELD Amount }.
Click inside the field code brackets, just after the field name. Type the numeric switch \# "0.00" for two decimal places. The code should now look like { MERGEFIELD Amount \# "0.00" }.
Press Alt + F9 again to hide the field codes, then right-click the field and select 'Update Field' to apply the new numeric formatting.
Format the Excel Data as Text Before Merging
If you prefer not to edit field codes in Word, you can force Excel to send the exact text string by using the TEXT function.
Perform Flawless Mail Merges with WPS Office
WPS Office provides a highly compatible and intuitive Mail Merge feature in WPS Writer, seamlessly pulling formatted data from WPS Spreadsheet to quickly generate professional documents without complex formatting errors.
- 1. Open the Mailings tab: Open your template document in WPS Writer and navigate to the 'Mailings' tab on the top ribbon.
- 2. Select the data source: Click 'Open Data Source' and select the WPS Spreadsheet file containing your data.
- 3. Insert merge fields: Click 'Insert Merge Field' to place your numeric data fields exactly where you want them in the document.
- 4. Complete the merge: Click 'Merge to New Document' to automatically generate your final formatted files flawlessly.
Frequently Asked Questions
Why does Word change my Excel numbers during a mail merge?
Excel stores decimal numbers as binary floating-point approximations. While Excel rounds them for display purposes, a Word mail merge pulls the raw, underlying unrounded approximation (like 79,732.05000000000003 instead of 79,732.05).
Can I stop Excel from using floating-point approximations?
No, 64-bit binary floating-point calculations are an industry standard for spreadsheet software. You must resolve these display issues by applying formatting rules either in Excel via the TEXT function or directly in Word using field switches.
What are other useful numeric switches for Word mail merge?
You can use \# "$,0.00" to display currency with a thousands separator, or \# "0%" to display decimal values as whole percentages. Simply add these switches to the end of your MERGEFIELD code.
