This article contains a complete guide on how to create a Mail Merge Word using Excel data. You will know how to connect data, displays the data and how to print all the data. There are also solutions available to solve problems that may occur.
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, For example, you want to make a mass letter in the form of an invitation, certificate or pay slip. You will create these letters for multiple recipients using the same format, but the data is different. This is where Mail Merge in Word plays an important role.
If using Mail Merge without Excel data, You need to input each data into each Mail Merge field using a manual form. 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.
To save time and energy in creating mass letters, Microsoft Word provides a Mail Merge feature that can connect to data from Excel, but not many people have mastered how to do it. 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. This guide also provides solutions to various problems that may occur. Especially for Office users 2010, There are special tricks available that you can use.
List of contents:
Preparation
To create Mail Merge Word with Excel data. You need to prepare two required documents as follows:
- A Word document containing a template for the letter or form to be shared. Sample: Income Certificate
- Excel document containing a table with data on letter recipients. Learn cara membuat tabel di Excel.
The data in the Table in Excel that will be used must be in the first row in the Sheet.
Steps to Create Mail Merge Word 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, Select a tab Mailings, choose 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 as 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 will be able to see the names of the fields according to the column titles 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 you have completed all the fields, insert them into their respective positions, The result will look like the example 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.
Troubleshooting Mail Merge
One of the problems that may occur when creating Mail Merge Word with Excel data is that Word and Excel are not connected.
To solve this connection problem you can double check where your 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 whether the table you are using has been created correctly? is there any empty space above the table?
Another challenge in Mail Merge is the format difference, 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 according to the expected design. 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.
Conclusion
After following the steps to create Mail Merge Word with this Excel data, You can create a Mail Merge in Word using Excel data. Thus, You can increase the efficiency of time and energy in your work.
To make it easier for you to understand the concept of Mail Merge Word with Excel data, please download the document example of Mail Merge Word file with Excel data which is 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, Anda bisa simak video cara membuat Mail Merge dengan data Excel.
We realize, This guide is not perfect. There are still many shortcomings, good from a grammatical point of view, the steps are outlined as well as from the included images. But we have tried our best, to the extent of our abilities and knowledge.
We have spent a lot of time creating this article about Mail Merge Word with Excel data, we have updated and fixed any errors we are aware of. Therefore, if you would like to take the time to rate this article, will mean a lot to us.
Thank You, Hope it is useful.
