scorecard
  1. Home
  2. tech
  3. how-to
  4. How to do a mail merge using Microsoft Word and Excel to quickly create personalized templates for imported data

How to do a mail merge using Microsoft Word and Excel to quickly create personalized templates for imported data

Dave Johnson   

How to do a mail merge using Microsoft Word and Excel to quickly create personalized templates for imported data
  • You can do a mail merge in Microsoft Word and Excel to create personalized documents for many recipients at once.
  • You can import an Excel data table into Word to customize your template with names and addresses.
  • Mail merge helps you quickly create auto-personalized letters, envelopes, labels, and more.

It's surprisingly easy to set up a mail merge, which lets you create seemingly personalized communication for a large number of recipients without creating each message by hand.

To mail merge a document - like a letter, envelope, printing label, or email - all you need is Microsoft Word and Excel, plus all the names and addresses you want to merge.

What is a mail merge?

The mail merge is made of two parts. First, you'll need the data file in Excel. This is simply a table that includes the information you want to merge - usually names and addresses, though you can merge data about anything.

In addition, you'll create a mail merge template in Word - this is a document that includes the boilerplate text you want to be the same for every recipient as well as the placeholder for the data that Word will insert from the Excel data file.

How to do a mail merge in Word and Excel

1. Start Excel and open a blank workbook.

2. You need to import or enter the data you plan to use, and how you do this depends on how the data currently exists. For example, if you have a large number of addresses stored in a CSV file, import it into this Excel file. Click the "Data" tab in the ribbon and choose "Get Data," then "From File," and finally "From Text/CSV." The data might also already be in an Excel spreadsheet, in an Access database, or you might need to type it into Excel from scratch. Whatever method you need to use, get it into the spreadsheet.

3. If the data doesn't already have a header row, add one now (you can right-click Row 1 at the far left and choose "Insert" from the menu). Label the header so you know what each column contains. Word will also use this row to import your data correctly.

4. You might need to change the formatting of some columns. For example, if the Zip Code column is formatted for numbers, zips with leading zeros will appear without the zero as a four-digit number. To fix that, click the column header to select the entire column. Then click the "Home" tab and click "General" in the Number section of the ribbon. Select "Text" from the menu.

5. Save the spreadsheet. You can save it anywhere, but you'll be able to find it more easily in Word if you save it in the "DocumentsMy Data Sources" folder.

6. Open Microsoft Word. Open a new, blank document and then create the boilerplate, common text you want to include in every mail merged document.

7. Position the cursor at the top left of the page, where you want the recipient's name and address to appear.

8. Click the "Mailings" tab in the ribbon and then click "Start Mail Merge." In the drop-down menu, choose "Letters" or whatever other template you want to use. For this example, we'll make a letter, but you can use this process for any kind of document. You shouldn't see any change in the document.

Quick tip: In the "Start Mail Merge" drop-down, you can also select "Step-by-Step Mail Merge Wizard" at the bottom of the list for a more guided run-through of the mail merge process.

9. Click "Select Recipients," and in the drop-down, choose "Use an Existing List…"

10. Find the Excel spreadsheet you created and select it. If you saved it in the "My Data Sources" folder, it should be in the default location for mail merges.

11. In the "Select Table" dialog, choose the sheet that contains your data table. If you created a new spreadsheet for this purpose, there will only be one. Make sure to check the box for "First row of data contains column headers." Then, click "OK."

12. In the "Write & Insert Fields" section of the ribbon, click "Address Block."

13. In the "Insert Address Block" dialog box, choose the style you want to use to insert the data - you should see the first entry in the data table as an example.

14. If you don't see all the fields you expected, you need to match fields from the spreadsheet with the mail merge feature in Word. Click "Match Fields…" and then choose the field names from the spreadsheet to fill in the blanks. Word usually does a good job of guessing, but it's not unusual to have to edit your mail merge fields. When you're done, click "OK" and then "OK" again.

15. You should now see a mail merge placeholder in the document.

16. To preview your document, click "Preview Results" in the Preview Results section of the ribbon. Use the forward and back arrows to see how each data entry looks in your document. You can use this to make sure there are no embarrassing data entry or conversion errors.

17. When you're ready, click "Finish & Merge," and choose how you want to complete the document, such as via printing or email.

18. You can save this Word document to reuse it again in the future.

How to edit a drop-down list in Excel, depending on how you created itHow to export your iPhone contacts to an Excel spreadsheet using iCloudHow to use the find and replace feature in Microsoft Word on your Mac in 2 different waysHow to recover an unsaved Microsoft Word document, and enable AutoSave so you never lose one again

READ MORE ARTICLES ON



Popular Right Now



Advertisement