Percentages in Word Mail Merge

How can you put percentages into a Word mail merge.

Following our article on importing Excel data into a Word mail merge, here are the options for using percentages.

Excel might show a percentage but the number stored is a fraction. For example 25% is stored in Excel as 0.25 . Normally, a Word mail merge will import the raw number from Excel and let you format it in way we’ve described before.

As usual, there’s more than one solution to the problem:


Formatting in Word

You can change the decimal fraction (eg 0.738) to a percentage (73.8) in Word.  To do it embed the MERGEFIELD inside another Word field.  This lets you multiply the number coming from Excel by 100 before formatting.

The entire field code looks like this:

{={MERGEFIELD PercentagefromExcel}*100  # “## 0%” }

Remember to press Ctrl + F9 to make the field – not the curly brackets on the keyboard.


Massaging in Excel

The most direct option is to add some columns in Excel with versions of the percentage figure that you can use in Excel.

Here’s a sample worksheet that shows you two different ways to ‘massage’ a percentage into a Word friendly form (the data is fictional):

Excel - Percentages in different formats.jpg image from Percentages in Word Mail Merge at Office-Watch.com

Percentage: Column B is the fraction (eg 0.12) displayed as a percentage. This is the usual way percentages are handled in Excel.

Actual Value: exposes the value in column B in the way Excel stores it, in other words as a fraction. Just for demonstration purposes.

Percentagex100: is the fraction from column B multiplied by 100. Eg=B2*100.

PercentageasText: is the column B value converted to text (using the TEXT function) with percentage formatting applied. Eg =TEXT(B2,”##0%”) .

Either ‘Percentagex100’ or ‘PercentageasText’ will work in a Word mail merge.

Probably ‘Percentagex100’ is the better choice since it sends a number (not text) to Word that you can format in any way you wish. There may be cases where you use the same Excel data in different formats for the mail merge.

‘PercentageasText’ locks you into a single format to display in Word.


DDE option

Another choice is to change the type of communication between Excel and Word to the older ‘DDE’ system.

DDE imports the visible results of Excel cells, not the underlying value. In other words, what you see in Excel is what will appear in a Word mail merge. For details see the last section of this article.

Changing this setting will affect all data coming from Excel and may change the behavior of other data, not just percentages. Double-check your mail merge after switching to DDE.