Word Mail Merge: Keep Excel Formatting for Dates, Numbers

Word mail merge does not reliably carry Excel cell formatting into the finished document. To control the result, format merge fields in Word with a number or date picture switch, or create a text helper column in Excel when a value needs precise display, such as a percentage or an ID with leading zeros.

Excel can show a value as a date, currency amount, or percentage without changing the value stored in the cell. Word may read that underlying value rather than Excel’s display, so the merged result can look different. The right fix depends on whether you are formatting a number, preserving text, or trying to reproduce a particular Excel display.

Why Excel formatting can disappear in a mail merge

An Excel cell has both a value and a display format. A cell might store the number 1250 while its number format displays it as $1,250.00. A date cell stores a serial number that Excel displays as a calendar date. When Word connects to the workbook, it reads the data for the merge; it does not treat every Excel cell style as a guaranteed instruction for how to print that value.

This is why a Word letter might show a date as 45292, a currency amount without a currency symbol, or a percentage as a decimal. The workbook can look correct on screen while the Word preview looks wrong. It is not necessarily a problem with the data: it is often a difference between the stored value and the formatting applied by Excel.

Excel’s font, fill color, borders, alignment, and column width are also not transferred as formatting for the merged Word document. Set the appearance of the final letter, labels, or table in Word. The formatting issue most people mean by “keep Excel formatting” is the display of values such as dates, amounts, and codes.

Format merge fields with Word field switches

For ordinary numeric and date values, Word field switches are usually the most direct solution. A switch tells Word how to display the result of a merge field. The \# switch formats numbers, while the \@ switch formats dates and times.

Currency: { MERGEFIELD Amount \# "$#,##0.00" } can display 1250 as $1,250.00.
Date: { MERGEFIELD DueDate \@ "MMMM d, yyyy" } can display a date as September 5, 2026.
Whole number: { MERGEFIELD Quantity \# "0" } displays a numeric quantity without decimal places.

The field name must match the column header selected for the merge. The examples use field-code braces for explanation; when Word creates a merge field, it inserts the special field structure. Do not replace those field braces by typing ordinary curly-brace characters.

In Word for Microsoft 365 on Windows, select the merge field and press Alt+F9 to show field codes. Add the appropriate switch inside the field, then press Alt+F9 again to return to results. You can also right-click a field and choose Toggle Field Codes. Preview the merge and update the field with F9 if needed. On Word for Mac, Option+F9 toggles field codes on many keyboards; on some keyboards you may also need the Fn key. Menu names and keyboard behavior can vary slightly by Word version.

Apply the fix in a reliable merge workflow

Make the data source predictable before adjusting fields. In Excel, keep one record per row, use a clear header row, and avoid merged cells in the data range. Save the workbook, then connect it to Word using Mailings > Select Recipients > Use an Existing List. Select the workbook and, if prompted, choose the worksheet or named range containing the records.

In Word, start or open the document you want to merge and select the appropriate option under Mailings > Start Mail Merge, such as Letters or Labels.
Choose Mailings > Select Recipients > Use an Existing List, then locate the Excel workbook.
Insert fields using Mailings > Insert Merge Field. Choose the matching column name rather than typing a field name from memory.
Format fields that need a specific numeric or date display using the switches described above.
Choose Mailings > Preview Results and move through several records, including records with unusual dates, large amounts, or blank values.
Choose Mailings > Finish & Merge to create the final documents, print them, or send them according to the options shown.

Previewing more than the first record matters. A field can look fine for one row but expose an issue in another, such as a blank date, an unusually long name, or a value with more decimal places. If you edit the workbook after connecting it, save the changes and check that Word is using the expected data source and worksheet.

Choose formats for dates, money, percentages, and IDs

Pick a display format that suits the document’s audience, not merely the way the spreadsheet happens to look. A date such as 03/04/2026 is ambiguous across regions: it can mean March 4 or April 3. A switch such as \@ "d MMMM yyyy" produces a less ambiguous form, such as 4 March 2026, assuming the underlying date is interpreted correctly.

Currency fields can use a symbol and decimal pattern, for example \# "$#,##0.00". Check the intended currency and regional conventions: a dollar symbol alone does not identify a country, and the comma and decimal point conventions differ by locale. Also check whether a value should show cents. A whole-dollar format can make an amount easier to read, but it rounds the display and should not be used if the cents matter.

Percentages need special care because Excel often stores 12.5% as the number 0.125 and applies a percentage display format. A Word number switch may not reproduce the exact percentage presentation you expect across data connections and formats. Preview representative values; for a dependable, exact text display, use a helper column in Excel. For example, with a decimal rate in B2, where B2 contains 0.125, the formula =TEXT(B2,"0.0%") returns the text 12.5%. Use that helper column as the merge field. Because the result is text, it is intended for display, not further numeric calculations.

Identifiers such as postal codes, account references, and product codes are not always numbers in the practical sense. If leading zeros are significant, store the identifier as text in Excel or create a helper column that produces the desired text. A numeric Word format such as \# "00000" can display a five-digit number with leading zeros, but it cannot restore digits that were already lost when Excel converted an identifier to a number. For long IDs, text storage also avoids scientific notation and precision loss.

When an Excel helper column is the better choice

A helper column is useful when the required output is more specific than a standard Word field switch can comfortably express, or when a value must retain literal characters and leading zeros. It also makes the intended display visible in the workbook before the merge. Give the column a clear header such as MailingDateText or RateDisplay, then select that field in Word.

For example, a helper column can combine a formatted date with explanatory text, or turn a code into text with a required prefix. The trade-off is that the result is text: Word receives the finished display rather than a numeric value it can format. If the workbook will be reused for calculations, keep the original numeric or date column and create a separate display column rather than replacing the source data.

Excel’s TEXT function can create formatted text, but its format string needs to match the intended output. Test a few records, including blanks and edge cases. A helper formula may also need to be filled down for all records; an Excel table can help extend formulas when new rows are added. Save the workbook before previewing the merge so Word can read the latest results.

Using DDE as an alternative connection

On some Windows installations, connecting through Dynamic Data Exchange (DDE) can make Word use Excel’s displayed values rather than the default data interpretation. It is an older connection method and is not the best first fix: it can be unavailable, restricted by security settings, or behave differently from the standard connection. Field switches and helper columns are generally easier to review and maintain.

If you need to investigate DDE in Word for Microsoft 365 on Windows, the option may be available after enabling File > Options > Advanced > General > Confirm file format conversion on open. Reconnect the workbook and, if the format-conversion prompt offers it, select the Excel worksheet option that specifies DDE. Availability and wording can vary by installation. If the option is absent or blocked, use Word field switches or helper columns instead. Word for Mac does not provide the same Windows connection workflow.

Common problems and how to check them

The field shows a serial number. Confirm it is a real Excel date, then add a Word date switch or use a formatted-text helper column.
The switch appears as text. Toggle field codes to check that the switch is inside the merge field, not after it. Toggle back and preview the result.
Leading zeros are missing. Check whether Excel stored the source as a number and discarded the zeros. Use text data or a helper column if they are no longer present.
Currency or decimal punctuation looks wrong. Check the chosen picture format and the regional settings used to display the result. Test a few values in the finished merge.
Changes in Excel do not appear. Save the workbook, then check the data source and selected sheet in Word. Close and reopen the document or reconnect if Word is still showing stale data.
The field is blank for some recipients. Inspect those source rows for empty cells, inconsistent data, or a mismatch between the selected worksheet and the intended range.

Frequently Asked Questions

Does Word mail merge keep Excel cell formatting automatically?
No. Word may not preserve Excel’s display format when it reads the workbook. Set a format on the Word merge field or provide a preformatted text value in a helper column.

How do I stop a date from showing as a number in Word?
Show the field code and add a date picture switch, such as \@ "MMMM d, yyyy", then preview the merge. If the source is not a genuine Excel date, correct the data or prepare a text helper column.

Can I keep leading zeros in a mail merge?
Yes, if the zeros still exist in the source or you recreate them with a suitable format. Store identifiers as text when possible; if the value is numeric, a Word number switch such as \# "00000" can display five digits.

Why does my percentage merge as a decimal?
Excel commonly stores percentages as decimal values and applies a display format. Try a Word number format and preview it, or use an Excel helper column with TEXT to produce a display value such as 12.5% as text.

Do I need to format the Excel cells or the Word fields?
For a normal mail merge, format the merge field in Word when you need control over dates or numbers. Format the Word document’s fonts and layout in Word. Use Excel formatting as a guide, not as a guarantee that Word will copy it.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.