This article will show you in full how to create a Mail Merge Word with Excel data. Using Mail Merge Word with Excel data is a practical and smart solution.
Mail Merge is a feature in Microsoft Word which allows you to generate multiple copies of a document with unique information for each recipient. No need for copy-paste or boring manual adjustments. Not only does it save time but also ensures accuracy and consistency across documents.
As an illustration, Suppose you want to make an invitation letter, certificate or pay slip. The letters are created in the same format for multiple recipients. If without using Mail Merge, You'll probably copy the formatting to the next few pages, Of course, this method is less effective and inefficient.
Therefore, Microsoft Word provides a Mail Merge feature that can pull data from Excel. This guide will explain step by step how to create a Mail Merge Word with Excel data. You can practice this guide on Office 2013 or a newer version. For Office users 2010, There are special tricks to overcome various problems that may occur.
List of contents:
Preparation
To create Mail Merge Word with Excel data. You need to prepare two required documents:
- A Word document containing a template for the letter or form to be shared. Sample: Income Certificate
- Excel document that contains a table with data on letter recipients. Learn how to create a table in Excel.
The data in the Table in Excel that will be used must be in the first row in the Sheet.
Mail Merge Word Steps with Excel Data
1. Connecting Mail Merge Word with Excel Data
Once the required Word and Excel documents are available, The first step that must be taken is to connect Mail Merge to the data source, namely a table in an Excel document. This guide uses example of Mail Merge Word file with Excel data
To connect Word mail merge with Excel, follow the steps below:
1.1. Select Excel Document
Open the Word document you have prepared, then click tab Mailings, then click Select Recipients then select Use Existing List. Window File Explorer Select Data Source will be open
On the window File Explorer Select Data Source, select the Excel document you created previously, examples in this guide are Employee Salaries.xlsx
1.2. Select Sheets
After selecting the Excel document, a dialog box will appear Select Table, choose Sheet which contains the table you created, then click OK
2. Inserting Mail Merge Fields into a Word Document
After selecting Sheet which contains a data table in an Excel document, Your Word document will display the table titles in the Mail Merge fields. This field acts as a placeholder to display data from the Excel table you previously selected.
On tabs Mailings, part Write & Insert Fields -> Insert Merge Field, You can see the field names according to the column headings in the Excel table.
The next step is to insert each Mail Merge field name into the Word document.
How to enter the Mail Merge field: Click the row or paragraph where you want to place the field and then select the appropriate field, for example in a paragraph No select a field NO and so on.
Once all fields are complete, insert them into their respective positions, the results will look like below:
3. Displaying Mail Merge Field Data in a Word Document
Once the Mail Merge field name is inserted into the Word document, You can display the field data in the following way:
3.1. Preview Results
To show a preview of Mail Merge field data, on tabs Mailings click Preview Results. Knob Preview Results will display one row of data per impression, the first order data in the display is the data in the first row in the Excel table. To display data in other rows, You can use navigation to display the following data and previous data.
3.2. Displays Next and Previous Data
To display the following or previous data, You can use buttons Navigation (red box). You can also select directly to a row number or specific data by typing a sequence number based on the order of the data in the Excel table.
3.3. Searching for Specific Data
Apart from displaying data based on sequential numbers, You can also display data based on certain keywords using the search feature. Press the button Find Recipient then type the keywords you want to find, for example a person's name, address or city.
4. Print Mail Merge Documents in Word
You can print Mail Merge documents in Word in two ways. The first in the usual way through a shortcut Ctrl+P, This method is effective when you only want to print one line of displayed data.
The second way to print is using the print feature from Mail Merge. Printing from Mail Merge is more efficient if you want to mass print several rows of data at once.
To print from Mail Merge, on tabs Mailings click Finish & Merge, then select Print Document. In the dialog box Merge to Printer, You can select the row number of data you want to print, All, Current Record or a specific row range.
Until here, the steps to create Mail Merge Word with Excel data have been completed. In this section we have shown how to connect Mail Merge with Excel data, how to enter the Mail Merge field, how to display and print Mail Merge data.
Even if you have followed the above guide carefully, Sometimes there are other things that may happen that are not as expected and require troubleshooting.
Troubleshoot Common Mail Merge Problems
One of the common problems that may occur when creating Mail Merge Word with Excel data is a disconnect between Word and Excel.
To solve this connection problem you can check again where the Excel data source is located, It's best to put the Excel document in the same folder as the Word document.
Another problem that may arise is incorrect data population, i.e. information from your Excel data is not correctly filled into your document. This is often caused by format differences or inconsistencies in data sources.
You need to make sure that you have selected the correct Sheet. Also check the table you created, is there any empty space above the table?
Another common challenge in Mail Merge is format differences, where the appearance of the resulting Mail Merge document may be different from the design you want. This issue can be resolved by reviewing the formatting settings in the Mail Merge field and making adjustments.
Formatting Mail Merge Field Data
You may need to format the Mail Merge field so that the data appears as expected. Mail Merge field formatting is usually done for some special data such as numbers and dates.
For example, The salary data displayed is Rp1000000, should be Rp1.000.000, and the date data displayed is 01/01/2022, it would be better to use the full format ie 1 December 2022.
To format Mail Merge fields in Word as above, follow the steps below:
- Right-click the field for which you want to change the format, then click Toggle Field Code
- To format numbers with thousands separators, add code \# #.##0 after the field name, like the example below:
Repeat steps one and two above for each data that uses the same format.
- To display the date in a format suitable for the Indonesian region, for example 12 April 2022, add \@”dd MMMM yyyy” after the field name, as follows:
After formatting all the required fields, the results will look like below:
Finished, Now all Mail Merge data appears in the proper format.
If you still experience failure in creating Mail Merge with Excel data, for example, the data that appears does not match the expected format, Maybe it could be because you are using an older version of Microsoft Word. To solve this problem, You can try the special trick below to get Word to display Mail Merge data in the format it should be.
Special Mail Merge Tricks
This special trick of Mail Merge Word with Excel data can only be applied to Office products 2010 or older versions. With this trick, Mail Merge will display data according to the format specified in the Excel document without the need to change the format of the mail merge field in the Word document.
On Office 2010, to display Mail Merge data to match Excel format, follow the following steps:
- In a Word document that you have connected to Excel, open Word options settings, open the tab File then select Options.
- At the window Word Options, click the menu Advanced then select (tick) option Confirm file format conversion on open
- Click OK, then save and close the Word document.
- Reopen the document Word which has been connected to Excel.
- When opening the document, A confirmation dialog box will appear as shown below, then click Yes
- Once the document is open, repeat connecting Mail Merge Word with Excel data. This step aims to activate a new connection to select the data source and connection type.
- When you select a document Excel, a dialog box will appear Confirm Data Source. In this section, You need to display all data source, then check the option Show All. After all data sources appear, choose MS Excel Worksheets via DDE (*.xls) then click OK.
- The system automatically opens the Excel document you have previously selected. When the confirmation dialog box appears, click OK
- If this trick is successful, you can run it, then the Word document will display the Mail Merge data according to the format used in the Excel document.
Cover
That's the complete guide, step by step to create Mail Merge Word with Excel data, Hopefully this guide can be useful and make your work easier.
Please download example of Mail Merge Word file with Excel data which has been provided free of charge.
By understanding the concept of Mail Merge Word with Excel data from the example that has been provided, You can create Mail Merge Word on a larger scale, revolutionize the way you handle the document creation process.
If you prefer to follow tutorials with videos, You can watch a video on how to create a Mail Merge with Excel data.
