Use the links below to quickly jump to a section in this article:
Preparation:
- Open Microsoft Word
- Go to File > Options (bottom left) > Advanced
- Under General, check "Confirm file format conversion on open"
- Click OK, then close Word
To start, create an Excel file to hold your data.
- Place unique headers in the first line of your sheet - don't reuse any!
- Keep your data as clean as possible - do not put different types of data under the same heading!
- You can create data on multiple sheets in the same excel file, but you can only use one sheet at a time for a Mail Merge.
Here's an example Excel sheet with data and headers, as well as two columns with formulas:
![](https://cedarville.teamdynamix.com/TDPortal/Images/Viewer?fileName=47818ca5-d54e-450b-b6ad-bc13924da7cd.png&beidInt=284)
Formatting the Excel Sheet
Return to top of page
To prepare and sanitize your Excel sheet:
- If any columns contain dates (like columns F and G above), highlight the column and change the format to Short Date.
- If any columns contain text and numbers in the same column (like column H above), highlight the column and change the format to Text.
- For any columns that contain monetary amounts (like column E above), you don't have to change the formatting to include a dollar sign or decimals.
- You don't need to worry about any formulas that produce long decimals (columns K and L above), as we will fix the length in Word.
Once your data is created, save and close your Excel file.
You won't be able to modify it once we access the data in Word. If you need to make any changes to the Excel file, save and close out of Word before reopening Excel, then save and close Excel before reopening Word again.
Next, open a Word file that will utilize this data. This can be an existing Word file or a new file.
Here's an example Word document, with gaps in the text for data:
![](https://cedarville.teamdynamix.com/TDPortal/Images/Viewer?fileName=6c1233c2-4cbe-411c-bd92-1c04a10f69bb.png&beidInt=284)
Mail Merge Wizard Settings
Return to top of page
Once this is create, select the Mailings tab along the top, then select Start Mail Merge. Choose Step-by-Step Mail Merge Wizard...
- The Mail Merge pane will open on the right side. Choose Letter (unless your document is meant to be something different, like an envelope) and then choose Next
- For Select Starting Document, choose Use this current document
- On the next pane, leave Use an existing list select and select Browse - select the Excel file you saved previously
- On the Confirm Data Source window, select OLE DB Database Files and click OK (should be the default selection)
- Choose the sheet in your Excel file that contains the data (will be named Sheet1$ if you never changed this)
- Make sure you leave the "First row of data contains column headers" box checked
You will see a Merge Mail Recipients box - if you click and drag the bottom right corner, you can see the Excel data as it will appear in the Word document:
![](https://cedarville.teamdynamix.com/TDPortal/Images/Viewer?fileName=00f78f36-2956-48c4-a8dc-a2d001043862.png&beidInt=284)
Notice how the date fields and the 'Citizen ID' fields show up exactly as we want, thanks to how we formatted them in Excel. Also notice that some of the numbers that repeat are different than the ones that show in Excel. For instance, one box shows 0.80000000000000004, which is not what was entered in that cell - this is a result of tiny mathematical rounding errors that occur in Excel and Word. We will fix it in a later step, but it's important to know that even if a formula should produce a nice, even result (0.8), you might end up with something like this anyway.
In the Merge Mail Recipients box still, you can check or uncheck rows that you do or don't want to include. If you have hundreds of rows of data and only want your current document to impact 6 of them, you can uncheck everything (by clicking the check mark in the top row) and then check those specific rows. Once done with that, hit OK. Then, hit Next to continue on to step 4 in the Mail Merge pane.
To add fields to your document:
- Place your cursor where you would like a data field to go
- In the Mail Merge pane, select More Items to open an Insert Merge Field dialogue box
- Make sure Database Fields is selected at the top of this dialogue box
- Choose the field from the list of your Excel headers that you want to insert, then click Insert
- Hit Close
- Move your cursor to the next spot, then repeat steps 2-5 to add another field
Once you have everything added in, you can select Highlight Merge Fields in the toolbar along the top (in the Mailings tab) to see them all easily, like this:
![](https://cedarville.teamdynamix.com/TDPortal/Images/Viewer?fileName=618d9b0f-128e-4658-a241-04f290ef0fb2.png&beidInt=284)
Mail Merge Field Codes
Return to top of page
If you were to continue to the next step in the Mail Merge pane right now, you'd see the exact data that you saw in the Mail Merge Recipients box (lengthy decimals and all), so let's do the formatting first. To start, highlight everything (Control + A or Cmd + A), right-click on one of the data fields, then select Toggle Field Codes from the context menu. Your merge fields now look something like { MERGEFIELD "First_Name" }.
Here's a chart for how to format some different fields:
Field Type |
Field Codes |
Text (capitalize all letters) |
{ MERGEFIELD "Fieldname" \* Upper } |
Long number (no decimals) |
{ MERGEFIELD "Fieldname" \# #,##0 } |
Long number (two decimals) |
{ MERGEFIELD "Fieldname" \# #,##0.00 } |
Short number (two decimals) |
{ MERGEFIELD "Fieldname" \# 0.00 } |
Money ($ sign, two decimals) |
{ MERGEFIELD "Fieldname" \# $#,##0.00 } |
Date (20-May-2023) |
{ MERGEFIELD "Fieldname" \@ dd-MMM-yyyy } |
Date (May 20, 2023) |
{ MERGEFIELD "Fieldname" \@ "MMM dd, yyyy" } |
Here's what it looks like to add formatting to the example above:
![](https://cedarville.teamdynamix.com/TDPortal/Images/Viewer?fileName=88cb3c2c-31aa-4d77-9812-4da6c27d1feb.png&beidInt=284)
Once you have updated the formatting, you can continue on to Step 4 in the Mail Merge pane on the right. You should now see a preview of how one of the documents will look, and you can cycle through these previews with the left and right arrows in the Mail Merge pane.
If any field is formatted incorrectly, or if you wish to make any changes, here's how you can adjust it:
- Right-click on the field and select Toggle Field Codes
- Make any changes to the formatting
- Right-click on the field again and select Update Field, which will show the preview again with any formatting changes immediately applied
- Repeat steps 1-3 for any additional changes
Here's a preview of the example above, with all of the formatting applied and with "Highlight Merge Fields" turned off:
![](https://cedarville.teamdynamix.com/TDPortal/Images/Viewer?fileName=1b06afa7-edbe-4269-a24c-1d44f5802bff.png&beidInt=284)
Once you have previewed the letters and are happy with their appearance, you can continue through the rest of the Mail Merge wizard to save/print all the files together, or you can use the Merge & Split add-on to export the letters as individual files with custom filenames.
If you are partway through a Mail Merge and want to start over (or if you want to close out of Word to make changes to the Excel file), make sure you are on step 4 or earlier in the Mail Merge pane (back up if you need to). Click on Start Mail Merge and select Normal Word Document, then save your document and exit out. This allows you to start the Mail Merge process over from scratch and will keep Word from prompting you for various SQL confirmations when you reopen Word.