How to Fix Incorrect Numerical Sorting in an Excel-to-Word Mail Merge
Question details
Users experience incorrect numerical sorting (e.g., 100, 1000, 200) when performing a mail merge from Excel to Word.
- Product
- Excel, Word
- Device & OS
- not provided
- Scenario
- Creating a mail merge document in Word using data imported from an Excel spreadsheet.
- Observed behavior
- Numbers are sorted alphabetically as text rather than numerically, causing out-of-order sequences.
Ensure your Excel workbook is saved and closed in Microsoft Word before attempting to modify the source data formatting.
Convert Formula Results to True Numbers Using Paste Values
Use this method to strip formulas and text formatting, ensuring Word reads the data as pure numbers.
When Excel values are generated by formulas or imported as text, Word's mail merge feature defaults to reading them alphabetically. Converting these values to static numbers forces Word to sort them in standard numerical order.
Highlight the entire column in Excel that contains the formula-generated values or incorrectly formatted numbers.
Press Ctrl+C or right-click and select 'Copy' to copy the highlighted cells.
Right-click the destination column (or the same column) and choose 'Paste Special' > 'Values', or click the Paste Values icon (usually a clipboard with a '123' symbol).
Keep the new column selected, go to the Home tab, and change the Number Format dropdown from 'General' or 'Text' to 'Number'.
Save the Excel file, reopen your Word document, select the updated Excel file as your recipient list, and run the mail merge. The numerical sorting should now be correct.
Perform Flawless Mail Merges with WPS Office
WPS Office offers a built-in, highly compatible Mail Merge feature between WPS Spreadsheet and WPS Writer. Easily format your numbers, apply data sources, and generate documents without formatting glitches.
- 1. Format source data: Open your source data in WPS Spreadsheet, select the column, right-click, choose 'Format Cells', and set the category to 'Number'.
- 2. Access Mail Merge: Open your document in WPS Writer and navigate to the 'References' tab on the top ribbon.
- 3. Import data source: Click the 'Mail Merge' button, then select 'Open Data Source' to browse and import your saved WPS Spreadsheet file.
- 4. Insert fields: Click 'Insert Merge Field' to place your numerical data into the correct locations within the document.
- 5. Complete the merge: Click 'Merge to New Document' or 'Merge to Printer' to generate your final sorted files.
Frequently Asked Questions
Why does Word change my Excel number formatting during a mail merge?
By default, Word connects to Excel using OLE DB, which reads the raw data values rather than the displayed formatting. If the underlying data is stored as text or contains mixed data types, Word will sort it alphabetically (e.g., 10, 100, 20).
Can I force Word to read the Excel formatting directly?
Yes. You can enable DDE (Dynamic Data Exchange) in Word by going to File > Options > Advanced, checking 'Confirm file format conversion on open', and selecting 'MS Excel Worksheets via DDE' when connecting your data source. However, converting to true numbers in Excel is usually faster and less prone to errors.
How do I fix date formatting issues in a mail merge?
Similar to numerical issues, date formatting can be fixed by applying field codes directly in Word. Press Alt+F9 to reveal field codes, and add a switch like \@ "MMMM d, yyyy" to the end of your date merge field.
