In mid July 2024 I had the pleasure of presenting the “Things you didn’t know data merge could do” session to the Los Angeles InDesign User Group (LAIDUG). Many techniques were demonstrated during this session that I would encourage you to watch (as well as many other brilliant sessions from the group).

During the session, I demonstrated two techniques that can be used to assist with Data Merge:

• A copyfitting technique that uses GREP styles to shrink larger blocks of text based upon the amount of characters that are in the line – the Square Peg in a Round Hole technique; and
• A variable bar graph technique that uses an Excel formula to turn a whole number into an equal string of characters that become substituted for a character style that has no fill or stroke, but has an underline made thick to represent a bar graph.

While preparing this demonstration, I thought “wouldn’t it be fun to combine the two?” so came up with a demonstration similar to the image below.

As the records change and values change, the length of the line AND the color of the line also change.

This is what the data looks like in the Excel file, note the formula used to make the string of characters to represent the bar graph.

It is saved out as a text file as – annoyingly – InDesign’s Data Merge can’t import Excel files for Data Merge – only CSV or TXT. But with your help, maybe this can change, but I digress…

To get the bar to change color, I’ve adapted the Square Peg Round Hole technique to behave differently – instead of shrinking the text as the amount of characters increases, it changes the color of the underline. There are five character styles – one for each color change, and the only thing each character style does is change the color of the underline.

This is what allows the bar graph to change to one of five colors depending on the value from the data merge.

A twist on this technique

While this technique works, the actual graphic only tells the reader that they are either early or late with their payments – it doesn’t convey to a late reader that there are stages of lateness. If the bar could keep the colors in increments of ten while still maintaining the correct value, that would be better.

Luckily, we can do this. A behaviour of GREP styles is that their position in the list of the GREP style portion of the Paragraph Style Options dialog determines when the style will be applied in relation to the other styles. The lower in the list the GREP style appears, the more recently the style is applied over the GREP styles earlier in the list.

To demonstrate this, I’ll duplicate the paragraph style to create the bar graph but take the previous GREP styles and swap them around so that the order goes from

reasonable – firstNotice – secondNotice – Late – Superlate

to become

Superlate – Late – secondNotice – firstNotice – reasonable

The following image has the initial demonstration with a delinquent payee.

But the following image is the same record but applying the duplicate style with the swapped GREP styles.

Now the reader can see that there were four previous stages prior to becoming the red color as indicated at the end of this new bar graph.

Give it a go!

Both of these techniques have several real-world uses, such as:

• Body Mass Index charts
• Daily temperature charts
• Rating charts (e.g. food safety, review score)
• Countdown to deadline (as per the example)