- Learn how to combine data from Excel to Word
- Preparing data to merge
- Link data source in Confluence
- Insert merge fields in your document
- View merge documents
- Preview of merged documents
- Completing the mail merge document
Learn how to combine data from Excel to Word
The Mail Merge feature in Microsoft Word and Excel simplifies the process of sending the same document, but with personalized changes, to multiple recipients. The term merge comes from the fact that one document (such as a letter) is merged with a data source document such as a spreadsheet.
Remark † The information in this article applies to Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2007. The Mail Merge feature is compatible with Word 2019, Word 2016, Word 2013, and Word 2010.
Preparing data to merge
The Word Merge Mail feature works seamlessly with data from Excel. Although you can also create a data source in Word, the use of this data is limited. If you already have mailing list data in your spreadsheet, there’s no point retyping all the information in your Word data source.
Theoretically, any Excel sheet can be in Word merge function without any special training. However, you should spend some time preparing your worksheet to optimize the merge process.
Spreadsheet data organization
Excel mailing list data should be neatly organized in rows and columns. Think of each row as one record and each column as the field you will insert into the document. If you need a refresher, check out our guide to data entry in Excel.
Create title bar
Create a title bar for the sheet you will use for merging. headline is a string of labels that identify the data in the cells below. Excel can sometimes be finicky about distinguishing between data and labels, so make it clear by using bold text, cell borders, and cell shading that are unique to the header row. This makes Excel stand out from the rest of your data.
Later, when you merge the mailing list data into the main document, the tags appear as merge field names. This eliminates confusion about what data is inserted into your document.
It is recommended to label columns in an Excel sheet. This helps prevent mistakes in the future.
Put all data on one sheet
The mailing list data that you will use for the merge must be on the same sheet. If it is spread across multiple sheets, merge the sheets or do multiple merges. Also make sure that the sheets have clear titles, as you have to select the sheet you are going to use without viewing it.
Link data source in Confluence
To link your prepared Excel sheet with the mailing list to a Word document:
In Word, select the tab mail list †
Select Start Merge and select the document type from the drop-down list.
In this tutorial, sheet data is added to an email. Select letters subscribe.
On the Mailings tab, select Select recipients and select the mailing list type from the drop-down list.
In this tutorial, the mailing list data is in a saved Excel sheet. Select Use existing list subscribe.
In the Select Data Source dialog box, locate and select the Excel file containing the mailing list data and select Open †
In the Select Table dialog box, select the worksheet that contains the mailing list data.
If your Excel has column headings, make sure the option is The first row of data contains the column headings †
Select Okay †
After the data source is linked to the main document, it’s time to enter text or edit the Word document. However, you cannot make changes to your data source in Excel. If you need to make changes to the data, close the document in Word before opening the data source in Excel.
Insert merge fields in your document
Select in Word mail list † Insert merge field to open the Insert Merge Field dialog box.
Select the field you want to add to the document and select Insert †
View merge documents
When you insert merge fields into a document, Word does not inherit the formatting of the data from the data source. If you want to apply formatting such as italics, bold, or underline, you need to do it in Word.
When viewing a document with fields, select the double arrows on either side of the field to which you want to apply formatting. When viewing merged data in a document, select the text you want to change.
All formatting changes apply to all merged documents, not just individual documents.
Preview of merged documents
To view merged documents, select Results example on the Mailings tab. This button acts as a toggle switch, so if you want to go back to viewing just the fields and not the data they contain, select it again.
Navigate merged documents using the navigation buttons on the ribbon of the Mailings tab. From left to right: First shot † previous post † Go to message † Next post and last record †
Before merging documents, review them all, or as many as you can, to make sure everything is merged correctly. Pay special attention to punctuation and spacing around the combined data.
Completing the mail merge document
When you are ready to merge documents, you have two options.
- Print documents † First you need to combine them with the printer. If you select this option, the documents are sent to the printer unchanged. You can pair with the printer by selecting: mail list † Finish and Merge † Print documents †
- Edit individual documents † If you need to personalize some or all of your documents (although it would be wise to add a notes field to the personalized notes data source) or make other changes before printing them. edit each individual document. To do this, select mail list † Finish and Merge † Edit individual documents †
Whichever method you choose, you’ll be presented with a dialog box where you can tell Word to merge all records, the current record, or a range of records. Select the records you want to print and click Okay †
To merge a range, enter the start and end numbers for the records you want to include in the merge and select Okay †
If you decide to print documents, after the dialog box appears, you will see the Print dialog box. You can interact with it just like you would any other document.