You may be familiar with Conditional Formatting in Excel. Well, Conditional Formatting can also be applied to fields in Word.
Now, Word does not have a Conditional Formatting button like in Excel, but you can write an IF function in the field and format the true and false responses. This will create the perfect Conditional Formatting behaviour.
In this example, Mail Merge is being used to inform members of how many points they have earned this week. If the number of points is greater than 20, I want the number formatted in red, and if not formatted in black.
The name of the Mail Merge field we need to edit is number.
Inserting an IF Function to a Word Field
To write the IF function, we first need to see the field code. To do this, press Alt + F9 on the keyboard. This will toggle the field codes of your document.
The document will look like below with field codes shown instead of the field enclosed in chevrons such as <<number>>.
Click inside the field code to edit it.
The structure of the IF function is shown below. It is enclosed in curly braces and begins with the expression or condition, then the action to take if true and then the action to take if false.
{ IF Expression Truetext Falsetext }
To get the IF function to conditionally format the number in our Mail Merge we need to edit the field code to the below.
You do not type the curly braces. It will not work if they are typed. They must be entered by pressing Ctrl + F9.
{ IF { MERGEFIELD number } > "20" { MERGEFIELD number } { MERGEFIELD number } }
Conditional Formatting with Mail Merge
When entering the IF function, format the true text and false text as you want them to appear. This could be a different colour, bold, underlined, larger. Whatever you need.
The end result of our document is shown below with field codes visible. The true text is red, with the false text left with the automatic black.
Awesome Video! : )
Thanks Muhammad.
Hi,
Thank you for the tutorial, its really helpful.
Can we use similar If statement for progressive result?
For example, If < 20 Color Red, If <40 color Orange, If <80 color Green????
This can be done. I have looked for nested Ifs within Mail Merge before but I remember it being quite awkward. Not as simple as in Excel.
I would personally filter the results in the Edit Recipient List button and run 3 separate merges. It would be easier and not take long. Apply the font colour for each one manually.
I found this a big help!
Is it possible to format a field based on a different fields number?
EG: Format a customers name red if the value of points was <20
Hi Clayton,
Yes absolutely. Just change the fields to match what you need like the below.
{ IF { MERGEFIELD number } < "20" { MERGEFIELD name} { MERGEFIELD name } } Format the first MERGEFIELD name in red like shown in the tutorial. Alan
Hello Alan,
I really like your channel because I find your tutorials extremely easy to follow. I am really struggling to create Mail Merge that has a table of outstanding invoice statements to customers, the data in excel contains customers that have several invoices so the fields for name and account are duplicated and I cannot get them collectively to go on one letter in the directory style merge because I don’t know the codes to include the extra lines or not include extra lines if the fields are not from the same customer, I also have a row in excel for each customer that contains the account number and a total amount due. Can you help?
I found you a fantastic help with formatting the dates and amounts, so very much looking forward to watching a decent instruction on how to do this too please. Thank you
Hi Emma, thank you for your comments. Unfortunately I don’t think I can help with this. I have not done a mail merge before that places several lines that match criteria onto one letter. I would just merge the account number and total invoice amount.
The only other thing I could recommend (because I don’t know of a mail merge way) would be to use Excel to create a formula that consolidates all the invoice numbers into a cell for each customer. Once it is in 1 row we could include it in the mail merge easy.
Excellent!! Thank you so much, I finally managed to include colors in mail merge! Very helpful!
You’re welcome Sylvia.
Thanks so much it was great, is there a way to do this in regards to date. I am looking to highlight training expiration dates.
I am sure this would be possible, though not tried. The dates are in your mail merge source, I assume.
Hi Alan,
We are looking to directly merge data from Excel to Word, specifically generating donor acknowledgement letters in Word from Excel data. We currently use Blackbaud but want to streamline this process onto our own servers. Can Word manage to do a merge like this?
Sure. You can definitely merge from Excel to Word to create your donor acknowledge letters. You can have a template in Word and merge the workbook and Word doc each time to need to generate the letters.