Microsoft Word - Mail Merge: How to import and format data from Excel

Use the links below to quickly jump to a section in this article:

• Open Microsoft Word
• Go to File > Options (bottom left) > Advanced
• Under General, check "Confirm file format conversion on open"
• Click OK, then close Word

To start, create an Excel file to hold your data.

• Place unique headers in the first line of your sheet - don't reuse any!
• Keep your data as clean as possible - do not put different types of data under the same heading!
• You can create data on multiple sheets in the same excel file, but you can only use one sheet at a time for a Mail Merge.

Here's an example Excel sheet with data and headers, as well as two columns with formulas:

Formatting the Excel Sheet

Return to top of page

To prepare and sanitize your Excel sheet:

• If any columns contain dates (like columns F and G above), highlight the column and change the format to Short Date.
• If any columns contain text and numbers in the same column (like column H above), highlight the column and change the format to Text.
• For any columns that contain monetary amounts (like column E above), you don't have to change the formatting to include a dollar sign or decimals.
• You don't need to worry about any formulas that produce long decimals (columns K and L above), as we will fix the length in Word.

Once your data is created, save and close your Excel file.

Next, open a Word file that will utilize this data. This can be an existing Word file or a new file. 

Here's an example Word document, with gaps in the text for data:

Mail Merge Wizard Settings

Return to top of page

Once this is create, select the Mailings tab along the top, then select Start Mail Merge. Choose Step-by-Step Mail Merge Wizard...

1. The Mail Merge pane will open on the right side. Choose Letter (unless your document is meant to be something different, like an envelope) and then choose Next
2. For Select Starting Document, choose Use this current document
3. On the next pane, leave Use an existing list select and select Browse - select the Excel file you saved previously
4. On the Confirm Data Source window, select OLE DB Database Files and click OK (should be the default selection)
5. Choose the sheet in your Excel file that contains the data (will be named Sheet1$ if you never changed this)
   • Make sure you leave the "First row of data contains column headers" box checked

You will see a Merge Mail Recipients box - if you click and drag the bottom right corner, you can see the Excel data as it will appear in the Word document:

In the Merge Mail Recipients box still, you can check or uncheck rows that you do or don't want to include. If you have hundreds of rows of data and only want your current document to impact 6 of them, you can uncheck everything (by clicking the check mark in the top row) and then check those specific rows. Once done with that, hit OK. Then, hit Next to continue on to step 4 in the Mail Merge pane.

To add fields to your document:

1. Place your cursor where you would like a data field to go
2. In the Mail Merge pane, select More Items to open an Insert Merge Field dialogue box
3. Make sure Database Fields is selected at the top of this dialogue box
4. Choose the field from the list of your Excel headers that you want to insert, then click Insert
5. Hit Close
6. Move your cursor to the next spot, then repeat steps 2-5 to add another field

Once you have everything added in, you can select Highlight Merge Fields in the toolbar along the top (in the Mailings tab) to see them all easily, like this:

Mail Merge Field Codes

Return to top of page

If you were to continue to the next step in the Mail Merge pane right now, you'd see the exact data that you saw in the Mail Merge Recipients box (lengthy decimals and all), so let's do the formatting first. To start, highlight everything (Control + A or Cmd + A), right-click on one of the data fields, then select Toggle Field Codes from the context menu. Your merge fields now look something like { MERGEFIELD "First_Name" }.

Here's a chart for how to format some different fields:

Here's what it looks like to add formatting to the example above:

Once you have updated the formatting, you can continue on to Step 4 in the Mail Merge pane on the right. You should now see a preview of how one of the documents will look, and you can cycle through these previews with the left and right arrows in the Mail Merge pane.

If any field is formatted incorrectly, or if you wish to make any changes, here's how you can adjust it:

1. Right-click on the field and select Toggle Field Codes
2. Make any changes to the formatting
3. Right-click on the field again and select Update Field, which will show the preview again with any formatting changes immediately applied
4. Repeat steps 1-3 for any additional changes

Here's a preview of the example above, with all of the formatting applied and with "Highlight Merge Fields" turned off:

Once you have previewed the letters and are happy with their appearance, you can continue through the rest of the Mail Merge wizard to save/print all the files together, or you can use the Merge & Split add-on to export the letters as individual files with custom filenames.