Mail merge is an incredibly useful feature in Microsoft Word that allows users to combine a document with data from a source like Excel to create personalized letters, labels, or emails. However, one common challenge users face is formatting numbers, especially when decimals and commas are involved. If you’ve encountered scenarios where numbers appear unformatted in your mail merge, this guide will help you solve the problem quickly and efficiently.
For a detailed visual walkthrough, check out this YouTube video.
Understanding the Problem
When you perform a mail merge, the data in the Excel source file often doesn’t retain its formatting in the Word document. For instance, numbers formatted as currency in Excel might appear as plain numbers in Word without commas or decimals.
Example:
Excel Data:
| Name | Amount |
|---|---|
| John Smith | $1,234.56 |
| Jane Doe | $567.89 |
Word Mail Merge Output:
| Name | Amount |
| John Smith | 1234.56 |
| Jane Doe | 567.89 |
This lack of formatting can make your document look unprofessional. Fortunately, Word offers several solutions to format numbers properly.
Steps to Format Mail Merge Numbers
1. Prepare Your Excel File
Ensure your source Excel file is well-organized and formatted:
- Use appropriate number formatting in Excel (e.g., currency, percentage, or number with commas).
- Save the Excel file after making changes.
2. Set Up the Mail Merge in Word
- Open Microsoft Word and create a new document.
- Navigate to the Mailings tab and select Start Mail Merge.
- Choose your desired document type (e.g., Letters, Labels, or Directory).
- Click Select Recipients and choose Use an Existing List. Select your Excel file.
3. Insert Merge Fields
- Place your cursor where you want to insert the data.
- Click Insert Merge Field and choose the appropriate field (e.g., Amount).
4. Format Numbers Using Switch Codes
Switch codes are special instructions in Word that modify how data appears in a mail merge. To apply a switch code:
- Highlight the inserted merge field (e.g.,
«Amount»). - Press Alt + F9 to reveal the field code. It will look something like this:
{ MERGEFIELD Amount } - Modify the field code to include a formatting switch. For example, to format as currency:
{ MERGEFIELD Amount \# "$#,##0.00" } - Press Alt + F9 again to hide the field code.
- Update the field by pressing F9 or preview the results by clicking Preview Results in the Mailings tab.
Common Switch Codes:
| Format | Switch Code Example | Output Example |
| Currency | \# "$#,##0.00" | $1,234.56 |
| Number with commas | \# "#,##0" | 1,234 |
| Percentage | \# "0%" | 123% |
| Decimal without commas | \# "0.00" | 1234.56 |
| Custom text with numbers | \# "# 'items'" | 123 items |
5. Test the Formatting
After applying the switch codes, preview the results to ensure the formatting meets your expectations. Use the Next Record and Previous Record buttons in the Mailings tab to verify all entries.
6. Complete the Merge
Once satisfied with the formatting:
- Click Finish & Merge in the Mailings tab.
- Choose whether to print the documents directly or edit individual documents.
Tips for Advanced Formatting
- Use Conditional Formatting: You can apply conditional formatting to display specific text based on the value of a number. For example:
{ IF { MERGEFIELD Amount } >= 1000 "$#,##0.00" "Below $1,000" }This displays numbers above 1,000 in currency format and labels others as “Below $1,000.” - Combine with Text: To combine text with formatted numbers, use the following syntax:
{ MERGEFIELD Amount \# "$#,##0.00" } owed as of today. - Date Formatting: To format dates in a mail merge, add a date-specific switch code. For example:
{ MERGEFIELD Date \@ "MMMM d, yyyy" }Output: January 22, 2025
Troubleshooting Common Issues
- Switch Codes Not Applying: Ensure you press Alt + F9 to reveal the field codes and apply the switch code correctly. Don’t forget to press F9 after making changes.
- Excel Data Not Updating: If changes in Excel are not reflected in Word, save the Excel file, close it, and refresh the mail merge connection in Word.
- Incorrect Number Output: Double-check the field code for typos and ensure the switch code matches your desired format.
Why Formatting Matters
Properly formatted numbers not only enhance the visual appeal of your documents but also ensure clarity and professionalism. Whether you’re sending invoices, letters, or reports, small details like commas and decimals make a big difference.
For additional tips and tricks, watch the comprehensive tutorial on YouTube. This video offers a step-by-step demonstration to help you master mail merge formatting.
Stay tuned to Netstellars for more IT solutions and tutorials!
