How to Use Mail Merge in Microsoft Word with an Excel Data Source

Introduction

When you need to send personalized letters, print custom envelopes, or generate hundreds of unique certificates, typing each one manually is highly inefficient. Microsoft Word includes a powerful Mail Merge feature that automates this process by connecting a single Word template document to a structured database—most commonly, a Microsoft Excel spreadsheet. This guide provides a step-by-step workflow for configuring and executing a Mail Merge.

Step 1: Prepare the Excel Data Source

The success of a Mail Merge entirely depends on a cleanly formatted Excel spreadsheet. Before opening Word, ensure your Excel data meets these requirements:

• Header Row: The very first row (Row 1) must contain clear, descriptive column headers (e.g., FirstName, LastName, Address, City, ZipCode). These headers become the “Merge Fields” in Word.
• No Blank Rows: Ensure there are no empty rows between data entries, as Word might interpret a blank row as the end of the database.
• Formatting Consistency: Ensure zip codes are formatted as text if they begin with a zero, and dates are formatted clearly.

Save the Excel file and close it. Word cannot connect to an Excel file that is actively open for editing.

Step 2: Set Up the Word Document Template

Open Microsoft Word and create a new blank document, or open an existing letter template.

1. Navigate to the Mailings tab on the ribbon.
2. Click Start Mail Merge and select the type of document you are creating (e.g., Letters, Envelopes, Labels).

Step 3: Connect the Excel Data Source

Now, you must link the Word document to your saved Excel file.

1. On the Mailings tab, click Select Recipients.
2. Choose Use an Existing List…
3. Navigate to the location where you saved your Excel file, select it, and click Open.
4. A prompt will ask you to select the specific Sheet within the Excel workbook (e.g., Sheet1$). Ensure the box for “First row of data contains column headers” is checked, and click OK.

Step 4: Insert Merge Fields

You can now insert placeholders that Word will replace with the actual data from Excel.

1. Place your cursor in the document where you want a piece of data to appear (e.g., after the word “Dear “).
2. On the Mailings tab, click Insert Merge Field. A dropdown menu will appear listing the column headers from your Excel file.
3. Select FirstName. A placeholder looking like «FirstName» will appear in the document.
4. Add spaces, punctuation, and other text normally. Insert the remaining fields (e.g., «Address», «City») to construct the letter block.

Step 5: Preview Results and Finish the Merge

Before printing or saving hundreds of documents, preview the output to ensure the formatting and spacing are correct.

1. On the Mailings tab, click Preview Results. The «FirstName» placeholder will change to the actual first name from row 2 of your Excel sheet.
2. Use the forward and backward arrow buttons next to “Preview Results” to cycle through several records and verify the data looks correct.
3. Once satisfied, click the Finish & Merge button.

You have three options to finalize the merge:

• Edit Individual Documents: Generates a massive new Word document containing every single letter concatenated together. This is useful if you need to add a custom postscript to just one specific letter before printing.
• Print Documents: Sends the merged letters directly to your default printer.
• Send Email Messages: If your Excel sheet contains an email column, Word can use Outlook to send the merged documents as the body of personalized emails.