Fix Word Mail Merge Skipping Empty Rows in the Excel Data Source

When you run a mail merge in Word, the merge may skip rows in your Excel data source that appear to be empty. This causes missing records in the final merged document, such as letters or labels that should have been created. The root cause is often that Excel treats blank cells differently from truly empty cells, and Word’s mail merge engine reads these cells as null values. This article explains why Word skips empty rows and provides step-by-step fixes to ensure all rows in your Excel data source are included.

Why Word Mail Merge Skips Rows With Empty Cells

Word’s mail merge feature connects to an Excel data source through a database connection called OLE DB or ODBC. When Word reads the Excel sheet, it treats each row as a record. If every field in a row is empty, Word may interpret that row as the end of the data range and stop reading further rows. This behavior is by design because Word assumes a fully blank row marks the end of the table. However, even rows with data in some columns but empty in others can be skipped if Word’s connection settings filter out null values.

Another common cause is Excel’s “last cell” definition. Excel remembers the last cell that contained data. If you deleted data from cells but did not delete the formatting or cell content, Excel still considers those cells as part of the used range. Word reads this range and may include blank rows that were previously used but are now empty, or it may stop early if it encounters a truly empty row.

The Role of the Excel Table Name

When you define a named range or convert your data to an Excel Table (Insert > Table), Word uses that named range as the data source. If the named range includes empty rows, Word will process them. But if the named range is defined as a dynamic range that excludes blanks, Word will skip those rows. Always ensure your named range includes all rows you want to merge, even if some columns are empty.

How Word Determines the Data Range

Word reads the Excel sheet from the first row down until it encounters a completely blank row. If your data has a blank row in the middle, Word stops there and ignores all rows below it. This is the most common reason for missing records in a mail merge. The solution is to remove blank rows from your Excel sheet or to use a named range that covers only the rows with data.

Steps to Prevent Word From Skipping Empty Rows

Apply the following methods to ensure Word reads every row in your Excel data source. Use these steps in the order shown for the best results.

Method 1: Add a Dummy Column With a Constant Value

This method forces Word to see every row as containing data, even if other columns are empty.

1. Open the Excel data source file
   Locate the Excel workbook that contains your mail merge data. Open it in Excel.
2. Insert a new column to the right of your data
   Click the column header to the right of your last data column. Right-click and choose Insert. This creates a new column.
3. Label the new column and fill it with a constant value
   Type “Dummy” in the header row. In the first data cell, type the number 1. Drag the fill handle down to the last row of your data to fill all cells with 1.
4. Save the Excel file
   Press Ctrl+S to save. Close Excel if you plan to update the mail merge connection in Word.
5. Reconnect the data source in Word
   Open your Word mail merge document. Go to Mailings > Select Recipients > Use an Existing List. Browse to the updated Excel file and select it. In the Select Table dialog, choose the sheet that contains your data. Make sure the “First row of data contains column headers” checkbox is checked. Click OK.
6. Run the mail merge
   Click Finish & Merge and choose your output option. Word now reads every row because the dummy column ensures no row is completely empty.

Method 2: Remove Blank Rows From the Excel Sheet

If blank rows exist in the middle of your data, delete them so Word does not stop prematurely.

1. Open the Excel data source
   Open the workbook in Excel.
2. Select the entire data range
   Click the top-left cell of your data, then press Ctrl+Shift+End to select all cells with content.
3. Go to Home > Find & Select > Go to Special
   In the Go to Special dialog, select Blanks and click OK. This selects all empty cells in the range.
4. Delete blank rows
   Right-click any selected blank cell and choose Delete > Entire Row. Click OK. Excel removes all rows that are completely blank.
5. Save and reconnect in Word
   Save the Excel file. In Word, repeat the steps to reconnect the data source (Mailings > Select Recipients > Use an Existing List).

Method 3: Use a Named Range That Excludes Blank Rows

Create a dynamic named range in Excel that automatically adjusts to include only rows with data.

1. Open the Excel data source
   Open the workbook in Excel.
2. Convert your data to an Excel Table
   Select any cell in your data range. Press Ctrl+T. In the Create Table dialog, make sure the range is correct and check “My table has headers.” Click OK. Excel names the table Table1 by default.
3. Rename the table
   Click anywhere in the table. Go to Table Design > Table Name. Type a name without spaces, such as “MailMergeData”. Press Enter.
4. Save the Excel file
   Press Ctrl+S and close Excel.
5. Connect Word to the table
   In Word, go to Mailings > Select Recipients > Use an Existing List. Select the Excel file. In the Select Table dialog, you will see the table name (MailMergeData) listed. Select it and click OK. Word uses the table’s defined range, which excludes completely blank rows but includes rows with data in any column.

If Word Still Has Issues After the Main Fix

Even after applying the methods above, you may encounter problems. The following sections cover additional issues and their solutions.

Word Still Skips Rows That Contain Only Spaces

Cells that appear empty but contain a space character are not truly empty. Word treats them as having data, but the merge field may show a blank. To fix this, use Excel’s TRIM function to remove extra spaces. Insert a new column with the formula =TRIM(A2) and copy it down. Then copy the values over the original data using Paste Special > Values.

Mail Merge Inserts Blank Lines Instead of Skipping

If you want blank fields to produce an empty result but not skip the entire row, use Word’s IF field code. In the mail merge document, press Alt+F9 to show field codes. Locate the merge field, such as { MERGEFIELD City }. Wrap it with an IF statement: { IF { MERGEFIELD City } <> “” “{ MERGEFIELD City }” “” }. This displays the city name if it exists, or nothing if the field is blank.

Word Cannot Find the Excel Data Source After Moving the File

If you moved the Excel file after setting up the mail merge, Word may lose the connection. Reconnect by going to Mailings > Select Recipients > Use an Existing List and browsing to the new location. To avoid this, store both files in the same folder before starting the merge.

Word Mail Merge vs Excel Data Source: Empty Row Handling Comparison

Item	Word Mail Merge (Default)	Word Mail Merge (With Fix)
Completely blank row	Stops reading data at that row	Included if dummy column added
Row with empty cells in some columns	Processed, but merge fields show blank	Same behavior, no change needed
Row with spaces in empty cells	Treated as non-blank, merge field may show blank	Use TRIM function to remove spaces
Hidden rows in Excel	Skipped entirely	Unhide rows before merging
Named range or Excel Table	Respects defined range, may exclude blanks	Use table with dummy column for full inclusion

Understanding how Word and Excel interact during a mail merge helps you prevent missing records. By adding a dummy column, removing blank rows, or using a named range, you can ensure that every row in your data source appears in the final output. For complex merges, test with a small sample before processing the full list. Use the IF field code trick to handle blank cells without skipping the entire record.