How to Fix Word Mail Merge Skipping the First Excel Data Row
Question details
Users experience an issue during an envelope mail merge where Word skips the first actual data row in the connected Excel workbook.
- Product
- Microsoft Word / Excel
- Device & OS
- not provided
- Scenario
- Setting up an envelope mail merge in Word using an Excel workbook as the data source.
- Observed behavior
- Word incorrectly treats the first address record as a header and skips it, despite the workbook already containing proper column headings in the first row.
Ensure your Excel spreadsheet is fully saved and closed before attempting to connect it to Word, and verify that the very first row distinctly contains your column headers without any blank rows above it.
Connect the Excel Workbook Using the OLE DB Method
Change the default data source connection method in Word to OLE DB Database Files to ensure headers and data rows are parsed correctly.
When Word imports data from Excel, it sometimes misinterprets the data structure depending on the connection protocol. Forcing Word to use the OLE DB connection gives you more explicit control over how the worksheet is read, specifically confirming the presence of headers.
Open Word and go to File > Options > Advanced. Scroll down to the 'General' section and check the box for 'Confirm file format conversion on open', then click OK.
Navigate to the 'Mailings' tab, click 'Select Recipients', and choose 'Use an Existing List'. Locate and select your Excel workbook.
A 'Confirm Data Source' dialog box will appear. Select 'OLE DB Database Files' from the list and click OK.
Select the specific worksheet that contains your data (it will typically display the worksheet name with a dollar sign appended, like 'Sheet1$'). Ensure the checkbox 'First row of data contains column headers' is checked, and click OK.
Perform Mail Merge Seamlessly with WPS Writer
Avoid complex OLE DB connection errors and missing data rows. WPS Writer integrates flawlessly with WPS Spreadsheet, making the mail merge process intuitive, fast, and completely free.
- 1. Open Mail Merge: Launch WPS Writer, open your main document, navigate to the 'References' tab, and click on 'Mail Merge'.
- 2. Import Data Source: Click 'Open Data Source' and select your Excel (.xlsx) or WPS Spreadsheet file. The software will automatically detect your headers.
- 3. Insert Merge Fields: Place your cursor in the document where you want the data to appear and click 'Insert Merge Field' to add your spreadsheet columns.
- 4. Complete the Merge: Click 'Merge to New Document' to generate the final pages, ensuring no data rows from your spreadsheet are skipped.
Frequently Asked Questions
Why does Word skip the first row of my Excel data?
Word often assumes the first row of an Excel spreadsheet contains column headers. If your data lacks headers, or if the connection method misinterprets the table structure, Word will absorb your first actual data row as the header.
How do I show the 'Confirm file format conversion on open' prompt in Word?
Go to File > Options > Advanced. Scroll down to the General section and check the box for 'Confirm file format conversion on open'. This will allow you to choose your connection method (like OLE DB or DDE) when linking an Excel file.
Can I use a CSV file instead of an Excel workbook to fix skipped rows?
Yes. Saving your Excel data as a CSV (Comma Separated Values) file and using that CSV as your data source strips away complex formatting and can bypass OLE DB connection errors, helping Word read the headers and rows correctly.
