Mail merge in Word with data from an Excel sheet does not work anymore

Question

I have a word file with merge fields from an Excel file which has worked for years. Now it plays havoc with the merge. It does not respect date formats, changing my ISO format to the illogical US format, it rearranges the order of the merged fields etc. I have spent some time with Copilot and managed to fix it by creating a new Excel file with the source data copied as values only, but with no computed columns, which sort of kills the fun of the merging. I am up to date on Win11 and Office 365.

Answer 1

Hello @Rolf Hegerberg,

Thank you for posting your question in the Microsoft Q&A Forum 

I’m glad to hear that the workaround suggested by Q&A Assist helped you get things working, even if it required extra steps. Your observation about how this functionality used to work seamlessly is very valid, and I completely agree that improving this experience would benefit many users. 

If you believe this change impacts usability, I encourage you to share your feedback through the Microsoft Feedback Portal .This is the official channel where feedback is reviewed by the product team and taken into account for future updates. 

Once you’ve posted your feedback, please feel free to share the link here in the comments. I’ll be happy to vote for it to help increase visibility and ensure it reaches the product team. Also, please consider marking this response as “Accepted.” Doing so will pin the post to the top, which can help highlight your feedback link and make it easier for other community members with the same concern to find it and vote for your idea. 

 

Your contribution and initiative can help improve product experience for many users in the future. Thank you again for your valuable time! 

Best regards, 

---

If you have extra questions about this answer, please click "Comment". 

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread. 

Answer 2

When performing a mail merge in Word using data from an Excel sheet, issues such as incorrect date formats and rearranged fields can occur. This is often due to how Word interprets the data from Excel, which may not retain the formatting applied in Excel. Here are some suggestions to address these issues:

1. Use Dynamic Data Exchange (DDE): This method allows Word to connect to the Excel worksheet and retain the formatting. You can enable DDE by following these steps:
   • Open a new blank document in Word.
   • Go to File > Options > Advanced and check the box for Confirm file format conversion on open.
   • Start the mail merge process and select your Excel file, ensuring to choose MS Excel Worksheets via DDE when prompted.
2. Format your Excel data: Ensure that the data types in Excel are formatted correctly. For example, if you are using date fields, make sure they are formatted as dates in Excel before performing the mail merge.
3. Check for computed columns: If your Excel file contains computed columns, consider copying the data as values only, as you have done, but be aware that this may limit functionality during the merge.
4. Save your Excel file in a compatible format: If you are using an .xlsx file, try saving it as an .xls or .csv file, as these formats can sometimes work better with Word's mail merge functionality.

These steps should help you troubleshoot the issues you're experiencing with mail merge in Word using Excel data.

---

References:

• Excel data doesn't retain its formatting in mail merge operations in Word
• Mail merge error when a data source is accessed by multiple users in Word

Answer 3

I have done all you suggests, and made it work in a fashion. The thing is that the exact duplicate of the Excel source file and the Word doc worked like a charm last time I used it a year ago. In the meantime, Office 365 must have been updated and downgraded to remove this simple functionality, such as correct date format and allowing computed fields.