Prepare your Excel 2016 data source for a Word mail merge

You've built a list of contacts and other data that you want to use for a Word mail merge. If your data source is an existing Excel spreadsheet, then you just need to prepare the data for a mail merge. But if your data source is a tab delimited (.txt) or a comma-separated value (.csv) file, you first need to import the data into Excel, and then prepare it for a mail merge.

Note: In an earlier version of Excel, you might have used Dynamic Data Exchange (DDE) in combination with the Step-by-Step Mail Merge Wizard in Word. Excel no longer supports DDE. If you'd like us to consider this feature for the next version of Excel, drop us a comment in the Excel Suggestion Box.

Step 1: Set up your data source in Excel

If you're already using an Excel spreadsheet as your data source for a mail merge in Word, go to Step 2 in this topic. If the data source is a .txt or a .csv file that contains your Gmail contacts, for example, use the Text Import Wizard to set up your data in Excel.

  1. Open Excel.

  2. On the Data tab, choose Get External Data > From Text.

    The From Text option is highlighted on the Data tab.

  3. Choose the .txt or .csv file you want, and then choose Import.

  4. In the Text Import Wizard, in the Original data type pane, choose Delimited.

    Excel Get External Data from Text, Text Import Wizard, Step 1 of 3

  5. Select the My data has headers check box above the Preview pane if the file you're importing has headers, and then choose Next.

  6. In the Delimiters pane, select the check box that matches the delimiter (such as a Tab or Comma) your data uses, and then choose Next.

    The options for Delimiters are highlighted in the Text Import Wizard.

    Tip: The Data preview pane shows what the results will look like in a table format.

  7. Under Data preview, choose the column that contains ZIP Codes or postal codes, and under Column data format, choose Text.

    The Text option for Column data format is highlighted in the Text Import Wizard.

    Note: Each time you apply a data format—General, Text, or Date—to a column, the name of the format appears in the table header for that column.

  8. Repeat step 7 as necessary, choosing the column you want to change and the data format you want to apply.

  9. Choose Finish.

  10. In the Import Data dialog box, accept the default setting of Existing worksheet and the cell address, and then choose OK.

    In the Import Data dialog box, choose to put the data in an existing worksheet, the default setting, or in a new worksheet

    Caution: The cell address in the Import Data dialog box shows the currently selected cell. Data is imported beginning with the named cell address.

  11. Save your spreadsheet with a new file name.

After you successfully import a .txt or .csv file, go to Step 2.

Step 2: Prepare your data source

In your Excel data source that you'll use for a mailing list in a Word mail merge, make sure you format columns of numeric data correctly. Format a column with numbers, for example, to match a specific category such as currency.

If you choose percentage as a category, be aware that the percentage format will multiply the cell value by 100. Format a column of percentages as text if you want to avoid that multiplication factor. Also, a column that contains ZIP Codes or postal codes needs to be formatted as text to preserve the data during a mail merge. Leading zeros—for example, 00399—in codes are dropped during a mail merge if they're not formatted as text.

  1. Open your Excel data source and choose the column that contains the ZIP Codes or postal codes.

  2. On the Home tab, in the Number group, choose the Number Format (General) box down arrow, and then choose an option in the list.

    In Excel, on the Home tab, in the Number group, choose the down arrow in the General box to choose the number format to use.

Make sure you add the appropriate symbol before or after a merge field. For example, here's how the currency and percentage values look if you omit symbols.

Mail merge document that reads "your contribution of 50.00" and "offer you a 20 discount."

If you include the symbols, the numbers make more sense.

Mail merge results document reads "your contribution of $50.00" and "offer you a 20% discount."

In your mail merge document, you add the symbols before or after the merge fields like this:

Sample mail merge document where a field named Donation is preceded by a dollar sign and a field named Percent is followed by a percent sgn.

See also

Share Facebook Facebook Twitter Twitter Email Email

Was this information helpful?

Great! Any other feedback?

How can we improve it?

Thank you for your feedback!

×