How to Produce a Mail Merge in Microsoft Word Using Excel Data

Mail merge is an incredibly useful way of automating the personalisation of documents such as letters, emails & envelopes.

It allows you to mass-produce these items by using a standard template, with placeholders for specific details that are then populated automatically based on a list that you provide.

Whilst Microsoft word mail merge is a fantastic feature, many uses prefer to create and maintain the list data to be used in the merge within Excel. Excel is an easy and flexible way to maintain the list data and this article will examine how you can combine data stored within Excel to populate a mail merge within Word.

Starting Point – Your Excel Document

The first thing that you need to do is to ensure that your Excel document is set up in a structure and format that makes it easy for Word to work with for the mail merge.

Fortunately, this is easy to do and there are only a few key points that need to be considered.

Firstly, make sure that the data within Excel contains headers, such as below.   The exact name that you provide for these headers will be the names that will appear in your mail merge within Word, so make sure that they are descriptive and that they indicate exactly what that data field is.

Next, it’s important that the data is contained within one Excel sheet.   If you have data that, for some reason, is split over more than one sheet then it would be worth spending some time to restructure this so that everything you need is contained within one table of data on a single worksheet.

Formatting

Finally, it’s worth thinking around formatting. If you have numbers that start with 0, or plan to use dates within the mail merge, then you will need to ensure that these are formatted in exactly the same way that you want them to appear on the mail merge.

An example of how to do this would be that for numbers that start with 0, such as a telephone number, then Excel would not display these correctly. Using the below to illustrate, you can see that Excel will ignore the leading 0 and this is not what we would want to appear on the mail merge.

An easy solution to this is to change how Excel treats the handling of such numbers.   This is easy to do and can be done by highlighting the data, right-clicking and then selecting ‘Format Cells…’

Once you have done this then you can then choose ‘TEXT’ from the left-hand column of data types from the menu that appears.

By doing so Excel will now display numbers in the exact format they are entered and will not attempt to truncate the data by omitting the leading zero when displaying.

In a similar fashion, how dates are stored within Excel may not necessarily be how you want them to appear in your mail merge. There are lots of formats in which dates can be displayed and this will be dependant on your needs, but along with the standard date options in the Excel formatting menu, you also have the option of creating a custom date format.

So the key point here is again to ensure that the format you see in Excel is exactly the same as the format you want to see in your mail merge, so it’s worth spending a bit of time getting this structure set up correctly.

Finally, it’s worth thinking about what fields you want to use within your mail merge. For example, if you need to refer to the person by the first name then you would need to ensure that within Excel you have a field that contains only the person’s first name.

Likewise, let’s say for marketing purposes you kept a list of the last time you made a sale or had contact from a specific customer.   If you wanted to put something in a letter that mentioned the year in which that last action occurred then you would want to make sure that you have that field, displaying only the year, within your Excel document.

The Mail Merge

Now that the initial set up has been completed, let’s run through the actual process steps required to perform a mail merge within Word, using an Excel template for the list data.

Fortunately, this is a quick and easy process to undertake, so although it can initially appear daunting, it’s easy to use once you have your Excel document set up in the correct structure.

Within Word, click on ‘Mailings’ on the ribbon and then select ‘Start Mail Merge’

You will then have a list of options for which you want can create a mail merge. In this example, we’ll create a letter as an example of how the functionality works, so we’d choose that option.

Next, we need to click on the ‘Select Recipients’ option from the ribbon and then choose ‘Use an Existing list’

Locate and choose your Excel document from its location and click OK.   You will then see the following:

Select the relevant sheet and ensure that the ‘First row of data contains column headers’ box is ticked.

You can then review the imported recipient list by clicking on ‘Edit Recipient List’ on the ribbon

Once in this menu, you will see the list of recipients and you can deselect any that you did not want to include.

You also have several other useful features such as being able to sort the data, find and remove duplicates and there are also filter options, which, for example, would let you filter on recipients from certain towns or postcodes, etc.

Once at this stage, the next step is to write out the document that you wish to use in your mail merge. What you will do, however, is add placeholders into any areas where you want the mail merge to personalise.

To do this, we’ll add an Address block to the top right of our Word document. On the ribbon, under ‘Mailings’, choose ‘Address Block’

Here you will get a preview box of what that address will look like

You can choose to display the person’s name in various formats, or not at all by deselecting the check box at the top left.

Looking at the preview box on the right-hand side you can see that there is an issue. We can only see the first line of the address; due to way our Excel data is structured. However, this isn’t a major issue and we can easily resolve this by changing a couple of settings.

Towards the lower right, click on ‘Match Fields’

You will then be presented with the following options

What we then need to do is to instruct Word on where the additional address lines are stored within Excel by mapping them to the corresponding fields by using the dropdowns.

In the below example you can see that I’ve mapped City (which I’m using Town) and Postal Code.

Once this is done, click on OK and you can then see that the preview window on the right-hand side now looks much more sensible and we’re ready to use that within our mail merge.

Click on OK and you will then see the following appear on your Word document.

This indicates that a Placeholder has now been added into the word document and this is where the address block that we saw in the preview box in the last step will now be displayed.   You can move this placeholder around as needed, if it’s not in the right location, although this step can be skipped if you chose the correct location before adding the placeholder.

You can now add any other options you want to personalise in the document in the same manner.

In the below we’ve added a greeting placeholder:

You can also use other fields throughout the document to create a more personalised experience by using the ‘Insert Merge Field’ option below:

Once you’ve added in these fields, your mail merge is nearly complete.   You can choose the ‘Preview Results’ option to review your mail merge and to make sure everything appears as expected.

You have the option of cycling through the mail merge Excel list by using the arrow icons

Whilst doing this you should now see your document correctly displaying the user’s details within your document.

If you are happy with the merged data, then you can choose ‘Finish & Merge’ to finalise the mail merge.

Here you will have options such as printing the document out for all the recipients, or you also have the option of viewing/editing individual documents before finalising the mail merge. You can also choose to send email messages if you were doing an email format.

The final step is to save your mail merge Word document. There is no special requirement here and you simply save it as you would any other Word document.   The neat thing here is that Word will automatically stay connected to your Excel document.

This means that if you update the Excel document then Word will automatically update the mail merge recipients list the next time you open your Word mail merge document.

When you next open the Word document you will get the below prompt. Simply click ‘Yes’ and your latest Excel data will be pulled into your Word mail merge document.

This completes the process on how to use Microsoft Excel data to perform a Microsoft Word mail merge.

Author Bio:

“Chris Rowley, ACMA, CGMA, is a director of Insightful Dynamics and a member of the Chartered Institute of Management Accountants.

Insightful Dynamics is a consultancy specialising in Microsoft Excel, VBA & data- specialising in modelling, automation analytics and BI solutions.”

Featured Image Source: Pixabay

Also Read:-

Advantages of blogging

How to make money from different niches of blogging

How to write a digital marketing plan (SOSTAC Model)