Using Mail Merge Word with Excel data is a practical and smart way of working. Mail Merge allows you to automatically fill documents with specific data from Excel. This not only saves time but also ensures accuracy and consistency across documents.
Mail Merge is a feature in Microsoft Word that allows you to produce multiple copies of a document with unique information for each recipient. No need for copy-paste or boring manual adjustments.
For example, you want to make an invitation letter or pay slip. The letter is written in the same format for many recipients. If without using Mail Merge, You'll probably copy the formatting to the next few pages.
This guide will show you the steps to create a Mail Merge in Word using data from Excel. You can practice this guide in Office 2013 and newer versions. For Office 2010, There are special tricks to overcome problems that may occur.
List of contents:
1. Preparation
You need to prepare two documents to create Mail Merge Word with Excel data. Required documents:
- Form created using Word. Example: Income Certificate
- Tables that contain data use Excel as the data source. learn how to create a table in Excel
Notes: The table used must be in the first cell in the Sheet. Look example of Mail Merge Word file with Excel data
2. Connecting Mail Merge Word with Excel Data
The first step that must be taken is to connect Mail Merge to the data source, namely an Excel document. To connect mail merge with excel, follow the steps below
2.1. Select Excel Document
- Open the Word document you have prepared, then click tab Mailings, then click Select Recipients then select Use Existing List. File Explorer window Select Data Source will open
- In the File Explorer window Select Data Source, select the Excel document you created previously, example here is Employee Salaries.xlsx
2.2. Select the Sheet containing the Table
After selecting the Excel document, a dialog box will appear Select Table, then take your pick Sheet where the table you created is located then click OK
3. Inserting Mail Merge Fields into a Word Document
Once you select the Excel document, it's time to insert the Mail Merge field into the Word document. This field acts as a placeholder for the data that will be populated from the Excel table you created.
- On the tab Mailings, part Write & Insert Fields -> Insert Merge Field, You can see the fields according to the column headings in the Excel table.
- Insert each field into its respective position in the Word document. Click the row or paragraph for the field and then select the appropriate field, field NO in paragraph No and so on.
- Once all fields are complete, insert them into their respective positions, the results will look like below:
4. Displaying Mail Merge Results Data in Word Documents
4.1. Preview Results
To display a preview of the Mail Merge results data, on the tab Mailings click Preview Results. You can see that the first data that appears is the first row of data in the Excel table.
4.2. Navigate Next and Previous Data
To move to the next or previous data, You can use the button Navigation (Red box). You can also jump to a specific row number by typing the sequence number according to the Excel table
4.3. Searching for Specific Data Rows
You can also search for specific data with the buttons Find Recipient then type in the keywords you want to find.
5. Print Mail Merge Data in Word
You can print Mail Merge data in Word in two ways. The first in the usual way through a shortcut Ctrl+P, This method is effective if you only want to print each data one by one.
The second, print via the print feature of Mail Merge. This method is highly recommended if you want to mass print the certificates.
To print from Mail Merge, on the tab 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.
Troubleshoot Common Mail Merge Problems
Although Mail Merge is a powerful tool for creating personalized communications, sometimes this can create challenges that require troubleshooting.
One of the common problems that may occur when creating a Mail Merge 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 to see if it is at the top of the sheet?
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 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.
As an example, The salary data displayed is Rp1000000, should be Rp1.000.000, and the date data displayed is 01/01/2022, should use the complete format ie 1 December 2022.
To format Mail Merge fields in Word, 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, as follows:
Repeat steps one and two above for each data that uses the same format.
- To format Indonesian dates, 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 happens because you are using an older version of Microsoft Word. To solve this problem, There are still special tricks so that Word displays Mail Merge data in the format it should be.
Special Mail Merge Tricks
This special Mail Merge trick is only suitable for Microsoft Word 2010 or older versions. With this trick, Mail Merge will display data according to the format applied in the Excel document without the need to change the format in the Word document.
Follow the steps below:
- Open Word Option which is in 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 that you have made Mail Merge previously.
- 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 should appear Confirm Data Source. In this section, You have to show all data source then check the option Show All. After all data sources appear, select 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, then 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.
Closing
That's the guide to making Mail Merge Word with Excel data, Hopefully this guide can be useful and make your work easier.
By understanding the concept of Mail Merge with Excel data from the examples provided, You can create Mail Merge on a larger scale, revolutionize the way you handle the document creation process.
You can also take advantage example of Mail Merge Word file with Excel data which has been provided free of charge.
To make it easier, You can watch a video on how to create a Mail Merge with Excel data
Thank you young man, really helped us
sama2 bang