No Mail Merge Wizard In Word For Mac

The Mail Merge wizard guides you through the process of merging a main document and a data source.

1. Create a document containing the text of the letter.
2. On the Mailings tab, in the Start Mail Merge group, click the Start Mail Merge button, and then click Step by Step Mail Merge Wizard.
3. In the Mail Merge task pane, with the Letters option selected, at the bottom of the pane, click Next: Starting document.
4. With the Use the current document option selected, click Next: Select recipients.
5. With the Using an existing list option selected, click Browse. Then in the Select Data Source dialog box, identify the data source, and click Open.
6. If necessary, in the Select Table dialog box, click the table you want to use as your data source, and then click OK.
7. In the Mail Merge Recipients dialog box, sort or filter the records as necessary, and then click OK.
8. At the bottom of the Mail Merge task pane, click Next: Write your letter, andthen insert the required merge fields in the main document, either by clickingitems in the task pane or by clicking buttons in the Write & Insert Fields group on the Mailings tab.

Tip Clicking Address Block or Greeting Line opens a dialog box in which you canrefine the fields' settings, whereas clicking individual fields from the Insert Merge Field list inserts them with their default settings.

1. Create a document containing the text of the email message.
2. On the Mailings tab, in the Start Mail Merge group, click the Start Mail Merge button, and then click Step by Step Mail Merge Wizard.
3. In the Mail Merge task pane, click E-mail messages, and then click Next: Starting document.
4. With the Use the current document option selected, click Next: Select recipients.
5. Click Select from Outlook contacts, and then click Choose Contacts Folder. If the Choose Profile dialog box opens, select the Outlook profile from which you wantto choose your recipients, and then click OK. Then in the Select Contacts dialog box, identify the data source, and click OK.
6. In the Mail Merge Recipients dialog box, sort or filter the records as necessary, and then click OK.
7. In the Mail Merge task pane, click Next: Write your e-mail message, and insert the necessary merge fields.
8. Preview the merged email messages, and then click Next: Complete the merge.
9. Click Electronic Mail, and in the Merge to E-mail dialog box, do the following:
   • Verify that Email Address is selected in the To box.
   • Enter a message subject in the Subject line box.
   • Select the message format you want in the Mail format box.
10. With the All option selected in the Send records area, click OK.

1. Open a new blank document, and display paragraph marks.
2. On the Mailings tab, in the Start Mail Merge group, click the Start Mail Merge button, and then click Step by Step Mail Merge Wizard.
3. On the Select document type page of the Mail Merge task pane, click Envelopes, and then click Next: Starting document.
4. With Change document layout selected on the Starting document page, click Envelope options.
5. In the Envelope Options dialog box, do the following, and then click OK:
   • On the Envelope Options page, choose the envelope size, and specify the font and location for the delivery address and the return address.
   • On the Printing Options page, verify that the default printer shown is the one you want to use, specify the way you will insert the envelopes into the printer,and choose the feed location. (For envelopes, this is usually manual feed.)
6. At the bottom of the Mail Merge task pane, click Next: Select recipients.
7. On the Select recipients page, click the type of data source you will use. Then select or create the data source, and refine the recipient list as appropriate.
8. At the bottom of the Mail Merge task pane, click Next: Arrange your envelope.
9. In the document formatted by Word to match your selections, position the cursor at the upper-left paragraph mark, and then enter the return address as you want it to appear on all envelopes.
10. Position the cursor at the centered paragraph mark. On the Arrange your envelope page of the Mail Merge task pane, click Address Block.
11. In the Insert Address Block dialog box, specify the address elements you want to include, and preview the results. Then click OK.

1. Open a new blank document.
2. On the Mailings tab, in the Start Mail Merge group, click the Start Mail Merge button, and then click Step by Step Mail Merge Wizard.
3. On the Select document type page of the Mail Merge task pane, click Labels, and then click Next: Starting document.
4. With Change document layout selected on the Starting document page, click Label options.
5. In the Label Options dialog box, do the following, and then click OK:
   • In the Printer information area, click the type of printer you intend to use. If you choose Page printers, select the printer tray.
   • In the Label information area, click the label brand in the Label vendors list, and then click the product number in the Product number list.
     Or
     To set up the mail merge for custom labels, click New Label. In the Label Details dialog box, enter a name, margin dimensions, page size, and thenumber of labels across and down each sheet. Then click OK.
6. At the bottom of the Mail Merge task pane, click Next: Select recipients.
7. On the Select recipients page, click the type of data source you will use. Then select or create the data source, and refine the recipient list as appropriate.
8. At the bottom of the Mail Merge task pane, click Next: Arrange your labels, and then ensure that you can see the left edge of the main document.
9. With the cursor positioned in the first cell, click Address block on the Arrange your labels page.
10. In the Insert Address Block dialog box, click OK to accept the default settings.
11. In the Mail Merge task pane, click Update all labels.

Note: be sure you save the Word file as a document, not a template format. Open MS Word and choose a new document or an existing document that you would like to use as a Mail Merge template. Click the 'Insert' tab from the Menu bar and choose 'Field' from the 'Quick Parts' list. Choose 'Mail Merge' from the 'Categories' list. Now you are ready to merge. STEP 5 – Perform the Merge. Select Tools – Mail Merge Wizard. When the wizard window pops up, select 'Use the current document' Then on the left side of the box, click on 'Save, print or send' (highlighted below). You will then get a window that states 'creating documents'. First Open Microsoft word on your computer or laptop. Once the application starts head over to the mailing tab in a new document. Under the mailing tab, you will see the drop-down option that says Start Mail Merge. Once you click on the drop-down you will see a.

For more info, see Prepare your data source in Excel for a mail merge in Word for Mac. Outlook Contact List contains data in a format that can be read by Word. See Use Outlook contacts as a data source for a mail merge. Word data file is a data source you can create on the fly, within Word. For more info, see Set up a mail merge list with Word.

1. Open a new blank document, and display paragraph marks.
2. On the Mailings tab, in the Start Mail Merge group, click the Start Mail Merge button, and then click Step by Step Mail Merge Wizard.
3. In the Mail Merge task pane, click Directory, and then click Next: Starting document.
4. In the Mail Merge task pane, with the Directory option selected, at the bottom of the pane, click Next: Starting document.
5. With the Use the current document option selected, click Next: Select recipients.
6. In the Mail Merge task pane, click the type of data source you will use. Then select or create the data source, and refine the recipient list as appropriate.
7. At the bottom of the Mail Merge task pane, click Next: Arrange your directory, and insert the necessary merge fields.

Checking for Errors

You can validate that a main document and data source will merge successfully by usingthe Auto Check feature. When running an automatic check, you can simulate or completethe merge. You can display errors on screen as the merge occurs, or write errors to a separate document.

1. Prepare the main document and data source, and set up the mail merge for the type of output you want.
2. On the Mailings tab, in the Preview Results group, click the Auto Check for Errors button.
3. In the Checking and Reporting Errors dialog box, click Simulate the merge and report errors in a new document. Then click OK.
4. For each error that the Auto Check feature reports, in the Invalid Merge Field dialog box, click Remove Field or choose the matching field from the Fields in data source list.

1. Prepare the main document and data source, and set up the mail merge for the type of output you want.
2. On the Mailings tab, in the Preview Results group, click the Auto Check for Errors button.
3. In the Checking and Reporting Errors dialog box, do one of the following, and then click OK:
   • Click Complete the merge, pausing to report each error as it occurs, and then click OK.
   • Click Complete the merge without pausing. Report errors in a new document, and then click OK.
4. For each error that the Auto Check feature reports, in the Invalid Merge Fielddialog box, click Remove Field or choose the matching field from the Fields in data source list.

In this tutorial:

[Originally posted June 2012. Updated August 2018.]

The Mail Merge feature of Microsoft Word is one of my favorite parts of the program. It is extremely powerful for creating labels and customized letters, emails, or reports. Once you learn how to use it, you can save countless hours of work. Unfortunately, the task of learning to master all of its idiosyncrasies can give you countless headaches.

One of the perennial annoyances of Mail Merge is its inability to format numbers from an Excel spreadsheet correctly. For example, a sales result in Excel appears as 100 but in Word it suddenly becomes 99.99999999996!

Fortunately, there is a way to fix this. In fact, there are three ways. We can modify the spreadsheet, modify the Mail-Merge document, or simply modify the way the two files connect to each other. Although this last approach is little known, it might be best approach—except for one giant detail: It no longer works for Word 2016.

[Note for users of various versions of Microsoft Office: The steps presented here are for Office 2016 as of August 2018, but the steps needed for older and newer versions of Word and Excel are very similar if not identical.]

The first way is to avoid the problem: In the Excel worksheet, insert a column with a formula that converts the Excel numbers or dates into a text format that is exactly the same as what you would want to appear in your Mail Merge document.

To do this, you need to use Excel's TEXT function, which enables you to convert a number or date into its equivalent text formatted exactly the way you desire. The downside is that you need to know certain formatting codes. Although these codes are identical to those used in the Custom Number formatting feature of Excel, they are rarely seen by most Excel users.

For example, the formulas TEXT(B3, '$#,##0.00') and TEXT(C5, 'M/dd/yy') convert number and date data into textual data (in this case, '$12,345.67' and '12/01/10').

(Tip: It is a good idea to format the cells containing these formulas in a different style, say, italic, or some unusual color to remind you that these numbers are simply text. In older versions of Excel, these 'numbers' could not be used in calculations. In Excel 2016, they can be used in all formulas except aggregate functions, such as, SUM and AVERAGE.)

The advantage of formatting numbers and dates as text is that text is transported from Excel into a Word Mail Merge document unmolested. Well, almost unmolested. Formatting options such as font, size, and color do not make the trip.

Format Codes for Excel's TEXT Function

A collection of the most commonly used codes is presented at the right. A key thing to remember is that both '0' and '#' act as placeholders for digits, but '0' will force leading or trailing zeros to appear. The '#' placeholder will be replaced by a digit only if it is not a leading or trailing zero. Thus, the code '00000' will ensure that the leading zero is not truncated from New Jersey postal zip codes.

These codes for numbers, dates, and times can be used in the TEXT function or in Excel's Custom number formats. As an extra bonus, they can also be used in the 'Numeric Switches' in Word Mail-Merge Fields described in the next section.

Microsoft Word Mail Merge

Unfortunately, there is a slight difference between the way these codes work in Excel and Word in Microsoft Office 2013. In Excel, you can use either 'M' or 'm' for months or minutes. In almost all cases, Excel is smart enough to figure out which units you are talking about. But Word is not so smart, and you must use the capitalized letter to refer to months. Also, Excel has an additional code, 'MMMMM', which returns a single letter abbreviation for the month (e.g., 'J, F, M, A, …').

Supplementing a spreadsheet table with a few columns featuring TEXT functions is a simple and direct approach. Just remember that these cells may look like they contain numbers, but they cannot be used in SUM or AVERAGE functions. If you need to convert them into numbers again, just use the VALUE function.

Handle with Care: The ROUND Function

Instead of the TEXT() function, some users prefer to use the ROUND() function to trim off extra decimal places. As its name implies, Excel's ROUND() function will permanently round a number up or down to the number of decimal places you specify. The advantage is that the rounded number is not text and can still be used for further calculations. In many cases, the ROUND() function will work well with Mail Merge, but you may want to steer clear of it because of the following reasons:

1. The ROUND function will not preserve the dollar sign or the thousands separator (comma)
2. The ROUND function will truncate trailing zeros
3. The ROUND function sometimes causes Mail Merge to display the wrong number of decimal places. For example, a Mail Merge document occasionally shows 4 decimal places when the ROUND function had specified 2 or 3.

In rare cases, the ROUND function causes Mail Merge to show a slightly different number. For example, instead of displaying 1.0014, Mail Merge showed 1.0013.

Okay. But let's say that you can't or don't want to change your Excel spreadsheet. Well, there is something we can do in Word:

The second way to cajole Mail Merge into displaying numbers from Excel correctly is to modify the Word document. Specifically, this means applying the desired number format code to the Merge Fields in the Word document. (The merge fields basically tell the Word document which column in the Excel table has the desired data.) To apply a format to a field, you must include a numeric switch (formerly called a picture switch) in the field's field code.

The first thing you have to do is to see the actual field code. Open the Mail Merge document and click the Mailings tab at the top of the window. Be sure the Preview Results button is toggled off so that you can see the Mail-Merge fields. Then right-click a Mail-Merge field (such as «Donation») and choose the Toggle Field Code option. You should now see the actual field code for that field, which is designated by curly braces as in { MERGEFIELD Donation }. Now edit the field code by simply inserting a numeric switch code to the end of the field code, as in

There are many picture codes available. Here are four examples with their respective results:

As you can see, the numeric switch codes are identical to the Excel formatting codes except that they are preceded by '#' for numbers and '@' for dates. (Note the use of quotation marks in the date code but not in the number codes.) To see more of the codes available, refer to the table below, or see the online help for 'numeric switch' in Microsoft Word.

Here are some examples of how the numeric field codes work with data in a column labeled 'Sales' in an Excel spreadsheet.

No Mail Merge Wizard In Word For Mac Osx

In light of the fact that Word and Excel use the same formatting codes, we could not help wondering why they don't use the same function format. For example, why not have the MergeField function look something like:

Good question. You'll have to ask Microsoft.

Note: Before you start cursing me out, try to remember that when you add or change a numeric switch, the effect may not be shown immediately. You either have to update the field (right-click it and choose Update Field), or click the button Mailings > Preview Results. On one occasion I had to do this 2 or 3 times. If you are using the Mail-Merge Wizard, you may have to go back a step and return to see the effect of your changes. (Why? Again, you will have to ask Microsoft.)

The numeric switches in merge fields work well, but I find the process very difficult to remember. ('Is it a forward slash or a back slash?') Also, it is very easy to make a mistake. Fortunately, for some users there is another way:

The above approaches are relatively simple, but if you have more than a few fields that require formatting, they can drive you into early retirement. At the very least, they require you to remember format codes that, while similar, are used in very different ways.

A much more elegant and simple solution is to have Word link to the Excel workbook via a DDE (Dynamic Data Exchange) link rather than the usual, presumably non-dynamic, linking process. That sounds a little daunting, but if you are smart enough to do Mail Merge, then DDE can a piece of cake. It is a simple two-step process, and the first step — enabling Word to open a file via DDE — has to be done only once.

[Does DDE work in Office 2016?Dynamic Data Exchange is an old technology, and it looks like Microsoft is in the process of pulling the plug on it. Sometimes it works for me in Office 2016, and sometimes it doesn't.

The trick appears to be that the Excel data source must be open before you access it from Word. As usual for Excel data sources, the table must begin on the first row of the first worksheet in the workbook file. If you do this, and have some patience, you should be able to get it to work.]

To set up Word 2013 for DDE links, do the following:

• Click: File > Options.
• Click the Advanced tab on the left and scroll down to the section General.
• Check the box labeled Confirm file format conversion on open.

That's all for the first step, and you never have to do it again. From now on, your copy of Word can open up many different types of files, and can open these by different avenues, including DDE. The only side effect of the above is that every time you open a non-Word file with Word, the program will give you a chance to change your mind. No problem.

1. Create a document containing the text of the letter.
2. On the Mailings tab, in the Start Mail Merge group, click the Start Mail Merge button, and then click Step by Step Mail Merge Wizard.
3. In the Mail Merge task pane, with the Letters option selected, at the bottom of the pane, click Next: Starting document.
4. With the Use the current document option selected, click Next: Select recipients.
5. With the Using an existing list option selected, click Browse. Then in the Select Data Source dialog box, identify the data source, and click Open.
6. If necessary, in the Select Table dialog box, click the table you want to use as your data source, and then click OK.
7. In the Mail Merge Recipients dialog box, sort or filter the records as necessary, and then click OK.
8. At the bottom of the Mail Merge task pane, click Next: Write your letter, andthen insert the required merge fields in the main document, either by clickingitems in the task pane or by clicking buttons in the Write & Insert Fields group on the Mailings tab.

Tip Clicking Address Block or Greeting Line opens a dialog box in which you canrefine the fields' settings, whereas clicking individual fields from the Insert Merge Field list inserts them with their default settings.

1. Create a document containing the text of the email message.
2. On the Mailings tab, in the Start Mail Merge group, click the Start Mail Merge button, and then click Step by Step Mail Merge Wizard.
3. In the Mail Merge task pane, click E-mail messages, and then click Next: Starting document.
4. With the Use the current document option selected, click Next: Select recipients.
5. Click Select from Outlook contacts, and then click Choose Contacts Folder. If the Choose Profile dialog box opens, select the Outlook profile from which you wantto choose your recipients, and then click OK. Then in the Select Contacts dialog box, identify the data source, and click OK.
6. In the Mail Merge Recipients dialog box, sort or filter the records as necessary, and then click OK.
7. In the Mail Merge task pane, click Next: Write your e-mail message, and insert the necessary merge fields.
8. Preview the merged email messages, and then click Next: Complete the merge.
9. Click Electronic Mail, and in the Merge to E-mail dialog box, do the following:
   • Verify that Email Address is selected in the To box.
   • Enter a message subject in the Subject line box.
   • Select the message format you want in the Mail format box.
10. With the All option selected in the Send records area, click OK.

1. Open a new blank document, and display paragraph marks.
2. On the Mailings tab, in the Start Mail Merge group, click the Start Mail Merge button, and then click Step by Step Mail Merge Wizard.
3. On the Select document type page of the Mail Merge task pane, click Envelopes, and then click Next: Starting document.
4. With Change document layout selected on the Starting document page, click Envelope options.
5. In the Envelope Options dialog box, do the following, and then click OK:
   • On the Envelope Options page, choose the envelope size, and specify the font and location for the delivery address and the return address.
   • On the Printing Options page, verify that the default printer shown is the one you want to use, specify the way you will insert the envelopes into the printer,and choose the feed location. (For envelopes, this is usually manual feed.)
6. At the bottom of the Mail Merge task pane, click Next: Select recipients.
7. On the Select recipients page, click the type of data source you will use. Then select or create the data source, and refine the recipient list as appropriate.
8. At the bottom of the Mail Merge task pane, click Next: Arrange your envelope.
9. In the document formatted by Word to match your selections, position the cursor at the upper-left paragraph mark, and then enter the return address as you want it to appear on all envelopes.
10. Position the cursor at the centered paragraph mark. On the Arrange your envelope page of the Mail Merge task pane, click Address Block.
11. In the Insert Address Block dialog box, specify the address elements you want to include, and preview the results. Then click OK.

1. Open a new blank document.
2. On the Mailings tab, in the Start Mail Merge group, click the Start Mail Merge button, and then click Step by Step Mail Merge Wizard.
3. On the Select document type page of the Mail Merge task pane, click Labels, and then click Next: Starting document.
4. With Change document layout selected on the Starting document page, click Label options.
5. In the Label Options dialog box, do the following, and then click OK:
   • In the Printer information area, click the type of printer you intend to use. If you choose Page printers, select the printer tray.
   • In the Label information area, click the label brand in the Label vendors list, and then click the product number in the Product number list.
     Or
     To set up the mail merge for custom labels, click New Label. In the Label Details dialog box, enter a name, margin dimensions, page size, and thenumber of labels across and down each sheet. Then click OK.
6. At the bottom of the Mail Merge task pane, click Next: Select recipients.
7. On the Select recipients page, click the type of data source you will use. Then select or create the data source, and refine the recipient list as appropriate.
8. At the bottom of the Mail Merge task pane, click Next: Arrange your labels, and then ensure that you can see the left edge of the main document.
9. With the cursor positioned in the first cell, click Address block on the Arrange your labels page.
10. In the Insert Address Block dialog box, click OK to accept the default settings.
11. In the Mail Merge task pane, click Update all labels.

Note: be sure you save the Word file as a document, not a template format. Open MS Word and choose a new document or an existing document that you would like to use as a Mail Merge template. Click the 'Insert' tab from the Menu bar and choose 'Field' from the 'Quick Parts' list. Choose 'Mail Merge' from the 'Categories' list. Now you are ready to merge. STEP 5 – Perform the Merge. Select Tools – Mail Merge Wizard. When the wizard window pops up, select 'Use the current document' Then on the left side of the box, click on 'Save, print or send' (highlighted below). You will then get a window that states 'creating documents'. First Open Microsoft word on your computer or laptop. Once the application starts head over to the mailing tab in a new document. Under the mailing tab, you will see the drop-down option that says Start Mail Merge. Once you click on the drop-down you will see a.

For more info, see Prepare your data source in Excel for a mail merge in Word for Mac. Outlook Contact List contains data in a format that can be read by Word. See Use Outlook contacts as a data source for a mail merge. Word data file is a data source you can create on the fly, within Word. For more info, see Set up a mail merge list with Word.

1. Open a new blank document, and display paragraph marks.
2. On the Mailings tab, in the Start Mail Merge group, click the Start Mail Merge button, and then click Step by Step Mail Merge Wizard.
3. In the Mail Merge task pane, click Directory, and then click Next: Starting document.
4. In the Mail Merge task pane, with the Directory option selected, at the bottom of the pane, click Next: Starting document.
5. With the Use the current document option selected, click Next: Select recipients.
6. In the Mail Merge task pane, click the type of data source you will use. Then select or create the data source, and refine the recipient list as appropriate.
7. At the bottom of the Mail Merge task pane, click Next: Arrange your directory, and insert the necessary merge fields.

Checking for Errors

You can validate that a main document and data source will merge successfully by usingthe Auto Check feature. When running an automatic check, you can simulate or completethe merge. You can display errors on screen as the merge occurs, or write errors to a separate document.

1. Prepare the main document and data source, and set up the mail merge for the type of output you want.
2. On the Mailings tab, in the Preview Results group, click the Auto Check for Errors button.
3. In the Checking and Reporting Errors dialog box, click Simulate the merge and report errors in a new document. Then click OK.
4. For each error that the Auto Check feature reports, in the Invalid Merge Field dialog box, click Remove Field or choose the matching field from the Fields in data source list.

1. Prepare the main document and data source, and set up the mail merge for the type of output you want.
2. On the Mailings tab, in the Preview Results group, click the Auto Check for Errors button.
3. In the Checking and Reporting Errors dialog box, do one of the following, and then click OK:
   • Click Complete the merge, pausing to report each error as it occurs, and then click OK.
   • Click Complete the merge without pausing. Report errors in a new document, and then click OK.
4. For each error that the Auto Check feature reports, in the Invalid Merge Fielddialog box, click Remove Field or choose the matching field from the Fields in data source list.

In this tutorial:

[Originally posted June 2012. Updated August 2018.]

The Mail Merge feature of Microsoft Word is one of my favorite parts of the program. It is extremely powerful for creating labels and customized letters, emails, or reports. Once you learn how to use it, you can save countless hours of work. Unfortunately, the task of learning to master all of its idiosyncrasies can give you countless headaches.

One of the perennial annoyances of Mail Merge is its inability to format numbers from an Excel spreadsheet correctly. For example, a sales result in Excel appears as 100 but in Word it suddenly becomes 99.99999999996!

Fortunately, there is a way to fix this. In fact, there are three ways. We can modify the spreadsheet, modify the Mail-Merge document, or simply modify the way the two files connect to each other. Although this last approach is little known, it might be best approach—except for one giant detail: It no longer works for Word 2016.

[Note for users of various versions of Microsoft Office: The steps presented here are for Office 2016 as of August 2018, but the steps needed for older and newer versions of Word and Excel are very similar if not identical.]

The first way is to avoid the problem: In the Excel worksheet, insert a column with a formula that converts the Excel numbers or dates into a text format that is exactly the same as what you would want to appear in your Mail Merge document.

To do this, you need to use Excel's TEXT function, which enables you to convert a number or date into its equivalent text formatted exactly the way you desire. The downside is that you need to know certain formatting codes. Although these codes are identical to those used in the Custom Number formatting feature of Excel, they are rarely seen by most Excel users.

For example, the formulas TEXT(B3, '$#,##0.00') and TEXT(C5, 'M/dd/yy') convert number and date data into textual data (in this case, '$12,345.67' and '12/01/10').

(Tip: It is a good idea to format the cells containing these formulas in a different style, say, italic, or some unusual color to remind you that these numbers are simply text. In older versions of Excel, these 'numbers' could not be used in calculations. In Excel 2016, they can be used in all formulas except aggregate functions, such as, SUM and AVERAGE.)

The advantage of formatting numbers and dates as text is that text is transported from Excel into a Word Mail Merge document unmolested. Well, almost unmolested. Formatting options such as font, size, and color do not make the trip.

Format Codes for Excel's TEXT Function

A collection of the most commonly used codes is presented at the right. A key thing to remember is that both '0' and '#' act as placeholders for digits, but '0' will force leading or trailing zeros to appear. The '#' placeholder will be replaced by a digit only if it is not a leading or trailing zero. Thus, the code '00000' will ensure that the leading zero is not truncated from New Jersey postal zip codes.

These codes for numbers, dates, and times can be used in the TEXT function or in Excel's Custom number formats. As an extra bonus, they can also be used in the 'Numeric Switches' in Word Mail-Merge Fields described in the next section.

Microsoft Word Mail Merge

Unfortunately, there is a slight difference between the way these codes work in Excel and Word in Microsoft Office 2013. In Excel, you can use either 'M' or 'm' for months or minutes. In almost all cases, Excel is smart enough to figure out which units you are talking about. But Word is not so smart, and you must use the capitalized letter to refer to months. Also, Excel has an additional code, 'MMMMM', which returns a single letter abbreviation for the month (e.g., 'J, F, M, A, …').

Supplementing a spreadsheet table with a few columns featuring TEXT functions is a simple and direct approach. Just remember that these cells may look like they contain numbers, but they cannot be used in SUM or AVERAGE functions. If you need to convert them into numbers again, just use the VALUE function.

Handle with Care: The ROUND Function

Instead of the TEXT() function, some users prefer to use the ROUND() function to trim off extra decimal places. As its name implies, Excel's ROUND() function will permanently round a number up or down to the number of decimal places you specify. The advantage is that the rounded number is not text and can still be used for further calculations. In many cases, the ROUND() function will work well with Mail Merge, but you may want to steer clear of it because of the following reasons:

1. The ROUND function will not preserve the dollar sign or the thousands separator (comma)
2. The ROUND function will truncate trailing zeros
3. The ROUND function sometimes causes Mail Merge to display the wrong number of decimal places. For example, a Mail Merge document occasionally shows 4 decimal places when the ROUND function had specified 2 or 3.

In rare cases, the ROUND function causes Mail Merge to show a slightly different number. For example, instead of displaying 1.0014, Mail Merge showed 1.0013.

Okay. But let's say that you can't or don't want to change your Excel spreadsheet. Well, there is something we can do in Word:

The second way to cajole Mail Merge into displaying numbers from Excel correctly is to modify the Word document. Specifically, this means applying the desired number format code to the Merge Fields in the Word document. (The merge fields basically tell the Word document which column in the Excel table has the desired data.) To apply a format to a field, you must include a numeric switch (formerly called a picture switch) in the field's field code.

The first thing you have to do is to see the actual field code. Open the Mail Merge document and click the Mailings tab at the top of the window. Be sure the Preview Results button is toggled off so that you can see the Mail-Merge fields. Then right-click a Mail-Merge field (such as «Donation») and choose the Toggle Field Code option. You should now see the actual field code for that field, which is designated by curly braces as in { MERGEFIELD Donation }. Now edit the field code by simply inserting a numeric switch code to the end of the field code, as in

There are many picture codes available. Here are four examples with their respective results:

As you can see, the numeric switch codes are identical to the Excel formatting codes except that they are preceded by '#' for numbers and '@' for dates. (Note the use of quotation marks in the date code but not in the number codes.) To see more of the codes available, refer to the table below, or see the online help for 'numeric switch' in Microsoft Word.

Here are some examples of how the numeric field codes work with data in a column labeled 'Sales' in an Excel spreadsheet.

No Mail Merge Wizard In Word For Mac Osx

In light of the fact that Word and Excel use the same formatting codes, we could not help wondering why they don't use the same function format. For example, why not have the MergeField function look something like:

Good question. You'll have to ask Microsoft.

Note: Before you start cursing me out, try to remember that when you add or change a numeric switch, the effect may not be shown immediately. You either have to update the field (right-click it and choose Update Field), or click the button Mailings > Preview Results. On one occasion I had to do this 2 or 3 times. If you are using the Mail-Merge Wizard, you may have to go back a step and return to see the effect of your changes. (Why? Again, you will have to ask Microsoft.)

The numeric switches in merge fields work well, but I find the process very difficult to remember. ('Is it a forward slash or a back slash?') Also, it is very easy to make a mistake. Fortunately, for some users there is another way:

The above approaches are relatively simple, but if you have more than a few fields that require formatting, they can drive you into early retirement. At the very least, they require you to remember format codes that, while similar, are used in very different ways.

A much more elegant and simple solution is to have Word link to the Excel workbook via a DDE (Dynamic Data Exchange) link rather than the usual, presumably non-dynamic, linking process. That sounds a little daunting, but if you are smart enough to do Mail Merge, then DDE can a piece of cake. It is a simple two-step process, and the first step — enabling Word to open a file via DDE — has to be done only once.

[Does DDE work in Office 2016?Dynamic Data Exchange is an old technology, and it looks like Microsoft is in the process of pulling the plug on it. Sometimes it works for me in Office 2016, and sometimes it doesn't.

The trick appears to be that the Excel data source must be open before you access it from Word. As usual for Excel data sources, the table must begin on the first row of the first worksheet in the workbook file. If you do this, and have some patience, you should be able to get it to work.]

To set up Word 2013 for DDE links, do the following:

• Click: File > Options.
• Click the Advanced tab on the left and scroll down to the section General.
• Check the box labeled Confirm file format conversion on open.

That's all for the first step, and you never have to do it again. From now on, your copy of Word can open up many different types of files, and can open these by different avenues, including DDE. The only side effect of the above is that every time you open a non-Word file with Word, the program will give you a chance to change your mind. No problem.

The second and last step has to be done each time you select a data source for your Mail Merge operation (either in Step 3 of Word's Mail Merge Wizard or after you press the Select Recipients button in the Mailings ribbon). Relax. It is just three additional mouse clicks:

• After you have chosen the data file you would like to use, a new 'Confirm Data Source' dialog box will appear.
• The default type of link is by OLE, but that is not what you want.
• In the Confirm Data Source dialog box, click the check box to Show all.
• In the expanded list of file types, choose MS Excel Worksheets via DDE (*.xls). (Choose this even if you are using the newer Excel file format: *.xlsx.)
• If asked, confirm that you are selecting the Entire Spreadsheet.

If you have already selected a spreadsheet for your Word document, you may have to select it again, this time via a DDE link. That's it! From now on, your Excel formatting will travel over to Word Mail Merge documents fairly intact. One huge caveat here: Make sure that the data you want to merge are in the first sheet of your Excel workbook. (It took us two hours to finally figure out that DDE does not see anything but the first Excel worksheet!)

It is a shame that Microsoft has not replaced DDE with a new technology that works in a similar manner. When DDE worked, it was marvelous. Let's hope an equally marvelous technology will appear in the near future.

Does DDE work for you in Word 2016? If you have a comment about that or any other issue related to Mail Merge number formatting, please let me know. Click here to post an anonymous comment.