If you want to create a mail merge from Excel to Word, there’s more involved than simply connecting a worksheet. Whether you set up the Excel sheet from scratch or import a text or CSV file, you have to make sure your data is structured correctly.
We’ll show you how to format your mailing list in a Microsoft Excel document so that when you connect it to Word for the mail merge, everything works properly.
Import a File to Excel
If you have a text or CSV file as the data source for the mailing list, you can import it into an Excel file. From there, you can arrange and format the data correctly before you connect it through the mail merge feature or Wizard in Word.
While the process is similar for versions of Excel, it is slightly different for Microsoft 365 versus Excel for Mac or other Excel versions.
Import a File Using Microsoft 365
If you’re using Excel with Microsoft 365 on Windows, follow these steps to import your data file.
- Go to the Data tab and select From Text/CSV in the Get & Transform Data section of the ribbon.
- Browse for and select the file. Then, choose Import.
- In the pop-up window, confirm or edit the three drop-down menus at the top for File Origin, Delimiter, and Data Type Detection.
- To format the fields upon import, select Transform Data. Note: If you plan to format them later, select Load to import the data directly.
- If you choose Transform Data, select the first column you want to format in the Power Query window that displays.
- Then, go to the Home tab and use the Data Type drop-down list in the Transform section to choose the format.
- Pick Replace current.
- Continue this process for each column in your dataset.
- When you finish, you’ll see the preview results and can select Close & Load on the Home tab.
You’ll receive your imported file with the number of rows loaded. You can then make additional adjustments as needed and save the file.
Import a File Using Other Excel Versions
If you’re using Excel on Mac or another version such as Excel 2013, follow these steps to import a text or CSV file.
- Go to the Data tab, open the Get Data menu, and pick From Text.
- Browse for and select the file. Then, choose Import or Get Data.
- In the Text Import Wizard window, pick Delimited and optionally set the Start Import at Row, File Origin, and whether your data contains column headers. Select Next.
- On the next screen, mark the check box for the type of delimiter you’re using such as tab, comma, or space, and select Next.
- On the final screen, you have the option to format the data. To do so, select each column and choose its data format at the top. Notice for dates, you can pick the structure; for numeric data, you can select Advanced to choose the Decimal and Thousands separators. Select Finish.
- In the Import Data dialog box, select a cell for an Existing sheet or choose New sheet for the data. Select Import.
You’ll then have your recipient list imported and ready for reviewing, editing, and saving.
Set Up the Mailing List in Excel
Whether you import a file as described above or create your mailing list in Excel from scratch, there are several things to keep in mind. These may require you to adjust your Excel data.
Make sure that:
- The data is formatted correctly for text, percentages, and numbers (described below)
- The column names in your sheet match the field names you plan to use in Word
- The data is in the first sheet of your workbook
- The workbook is saved locally (on your computer)
- The edits or changes are finalized before connecting the sheet in Word
Format the Mail List Excel
Along with managing the data for your mailing list in Excel, you must be sure that it is formatted correctly. This is imperative for percentages and zip or postal codes.
- Percentages: By default, percentages are multiplied by 100. Format percentages as Text if you want to avoid this calculation.
- Numbers: Format numbers to match their categories. For example, use Currency for dollar amounts.
- Zip or Postal Codes: Format zip or postal codes as Text. If you format them as Numbers, you will lose the leading zeros for codes such as 00321.
To format your data, select a column, go to the Home tab, and choose the format in the Number Format drop-down list.
Keep in mind that although you format the data specific to its category, it does not display corresponding symbols when you connect it to Word. For instance, when you map a percentage, you’ll only see the number display, not the percent sign. You should add the symbols you want adjacent to the mapped fields in the Word mail merge document.
Ready to Merge
Once you have your recipient list ready to go in Excel, it’s time to use the Word mail merge feature to finish the process. Whether personalized letters or email messages, look at our tutorial for creating a mail merge in Microsoft Word for complete details.
Additionally, you can check out how to create labels in a Word document from an Excel spreadsheet.