Word Mail Merge From Excel: Fix Number Formatting

In Word for Microsoft 365 on Windows, format a merge field with a numeric picture switch—for example, { MERGEFIELD Amount \# "$#,##0.00" } displays the value 1234.5 as $1,234.50. Excel’s cell formatting is not reliably carried into Word, so set the display format in the Word field or prepare the value as text in Excel.

Why Excel number formats can change in Word

An Excel cell has both a stored value and a display format. A cell might store the number 0.125 while showing it as 12.5%, or store 73 while displaying it as 00073. When Word connects to the workbook for a mail merge, it may read the underlying value rather than the exact appearance shown in the worksheet. The result can be a decimal instead of a percentage, a serial number instead of a date, or an identifier without its leading zeros.

This is usually a difference in how the programs interpret the data, not evidence that the number in Excel is wrong. The workbook can still be correct, and the merge can still use the intended recipient or amount. The fix is to decide where the display rule belongs: in the Word merge field, in an Excel helper column, or—in some older workflows—in the way Word connects to the workbook.

Apply a number format to a Word merge field

A field switch tells Word how to display a value when it evaluates the merge field. The \# switch is for numbers. You can add it to a field already inserted through the Mailings tab; you do not need to replace the Excel value or change its stored precision.

In Word, open the main mail-merge document and insert the field using Mailings > Insert Merge Field if it is not already present.
Show the field code. On Windows, press Alt+F9 to show or hide field codes throughout the document, or right-click the individual field and choose Toggle Field Codes.
Add the number switch and a format picture. For a currency amount, the code can read { MERGEFIELD Amount \# "$#,##0.00" }. Keep the actual merge-field name used in your document.
Toggle back to the field result, then use Mailings > Preview Results to check several records, including a large value and a value with cents.

The curly braces around a field are special Word field characters. Do not type ordinary braces if you are creating a field from scratch; insert a merge field through the Mailings tab, then edit its code. On Word for Mac, use the field’s context menu to toggle its code if the Windows shortcut is unavailable or differs on your keyboard.

Useful number, currency, and percentage examples

In a picture, the characters indicate how Word should present the value. A comma can mark thousands, zeroes require a digit position, and a decimal point sets the displayed decimal places. The quoted dollar sign in the currency example is a literal symbol. Adjust the symbol and picture to match the document’s audience and the numeric values in the source.

{ MERGEFIELD Quantity \# "#,##0" } displays 12500 as 12,500.
{ MERGEFIELD UnitPrice \# "$#,##0.00" } displays 1234.5 as $1,234.50.
{ MERGEFIELD Rate \# "0.0%" } displays a stored value of 0.125 as 12.5%.
{ MERGEFIELD Code \# "00000" } can display a numeric value of 73 as 00073.

Check percentage data especially carefully. Excel typically stores 12.5% as 0.125. A percent picture multiplies the displayed value by 100, so using it on a source value that is already 12.5 rather than 0.125 would display 1250.0%. The picture controls presentation; it does not correct a value stored at the wrong scale.

Format dates with the date picture switch

Dates use the \@ switch rather than the number switch. For example, { MERGEFIELD StartDate \@ "MMMM d, yyyy" } can display a valid date as November 2, 2026. In that picture, MMMM spells out the month, d gives the day without a leading zero, and yyyy shows a four-digit year. Other useful choices include MM/dd/yyyy for 11/02/2026 or d MMM yyyy for 2 Nov 2026.

If the field displays a number such as 46328 instead of a date, Word may be receiving the date’s spreadsheet serial value without its display format. Add the date switch and preview the result. If it still does not behave as a date, check that the Excel cells contain real date values rather than text that merely looks like a date. Regional settings can also affect how ambiguous text dates are interpreted, so an explicit month name or unambiguous date format can reduce confusion.

Choose between a Word switch and an Excel helper column

Use a Word field switch when the value should remain numeric in the workbook and only needs a different appearance in the letter, label, or envelope. This keeps the source data available for calculations and lets one document choose its own presentation. For example, the same amount could appear with two decimal places in an invoice letter and no decimals in a summary.

Use an Excel helper column when the value is really a text label, or when Word does not interpret the source consistently. This is especially useful for postal codes, membership numbers, product codes, and other identifiers where leading zeros are part of the identity rather than a mathematical value.

With the identifier in A2 and a five-digit display required, enter =TEXT(A2,"00000") in a helper column. If A2 contains 73, the formula returns the text value 00073. Include that helper column in the merge and insert it as the field. The original numeric column remains available, but remember that the helper result is text and is not suitable for arithmetic. For long identifiers, store the original as text in Excel: numeric cells retain only 15 significant digits, so converting a long number after entry cannot restore digits Excel has already changed.

What the Excel connection method changes

Word can obtain workbook data through different connection methods, and the method can affect whether Excel’s displayed formatting appears in a merge. The usual modern connection does not guarantee that Excel number formats will be honored, which is why a Word field switch or helper column is generally more dependable than relying on the worksheet’s appearance.

Some Windows versions and configurations offer the older DDE connection as a compatibility option. In Word, File > Options > Advanced > General includes the setting Confirm file format conversion on open. When enabled, selecting an Excel workbook as a recipient source may present a connection choice such as Microsoft Excel Worksheets via DDE. Availability and wording can vary, and DDE is a legacy route; it is not a substitute for checking the output. If you use it, preview several records and confirm that the merge still connects to the intended worksheet or named range.

On Mac and Word for the web, the available connection choices and menus can differ from Word for Windows. Do not assume the Windows DDE instructions apply. A field switch in the document or a text helper column in the workbook is usually the more portable approach.

Common formatting problems and practical checks

The format switch appears as text. The field may still be showing its code, or the braces may be ordinary typed characters rather than Word field characters. Toggle field codes and confirm that the merge field remains a real field.
Only some records look wrong. Inspect the source column for mixed types, such as numbers in some rows and text in others, or blanks and error values. Consistent source data makes the merge more predictable.
Currency has the wrong symbol or decimal separator. Set the symbol and decimal places to suit the document, and check Word, Excel, and Windows regional conventions. Do not assume a comma or period has the same regional meaning for every recipient.
Leading zeros disappear. A numeric format switch can add zeros for a fixed-width numeric code, but a helper column is safer when the code is an identifier or contains letters and punctuation.
The preview looks right but the final document does not. Preview Results is useful, but complete a small test merge with Mailings > Finish & Merge before producing all letters or labels. Check the merged document, not only the template.

A reliable workflow for a clean merge

Prepare the workbook before connecting it. Put clear column headers in the first row, keep one recipient or record per row, avoid merged cells in the data area, and save the file. Close it before selecting it in Word if the connection or file lock causes trouble. Then connect the workbook using Mailings > Select Recipients > Use an Existing List, choose the correct sheet or table, and insert the merge fields.

Format fields in the Word template, preview both ordinary and edge-case records, and use Finish & Merge to create a small sample output. Check blank values, unusually large amounts, negative values if relevant, dates near month or year boundaries, and identifiers with zeros. Once the sample is correct, complete the full merge. If you change a format switch after a merge, make the change in the main template and merge again; editing only one already-created output document does not update the template or other records.

Frequently Asked Questions

Why does Word mail merge show a date as a number?
Word may be receiving the spreadsheet’s date serial value rather than its displayed date format. Add a date picture using the \@ switch, and verify that the Excel source contains real dates rather than text strings.

How do I keep leading zeros in a mail merge?
For a fixed-width numeric value, a Word picture such as \# "00000" can add leading zeros. For postal codes or other identifiers, a text-formatted Excel source or helper column is often safer because the value is not truly a quantity.

Can Word use Excel’s cell formatting automatically?
Sometimes the connection method affects whether formatting is carried across, but Excel’s displayed format is not reliable enough to depend on for every merge. Apply a switch in Word or prepare a text helper column and verify the merged output.

How do I show two decimal places in a mail merge?
Add a numeric picture such as \# "0.00" to the merge field. For currency with thousands separators, use a suitable picture such as \# "$#,##0.00", then preview records with different values.

Will changing the Word field format change the Excel value?
No. A Word field switch changes how Word displays the merged value; it does not rewrite the workbook’s value or number format. Change the source data separately only when the stored value itself needs correction.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.