Solving Design Problems Using Spreadsheets & Variable Data

I was tasked with converting an (overwhelmingly cluttered) list of over 170 cocktail drink recipes & instructions into a 5.5×8.5 laminated booklet for a catering bartender.  The goal was to produce a booklet that one could reference quickly when making a drink. I cleaned up the information by using a clean typography style, colored type for instructions/garnish, and creating simple icon illustrations for the serving glasses. I then faced the problem of populating all the information in that style throughout the booklet in a organized and efficient manner. I researched how to import excel/spreadsheet files and create variable data to populate in Indesign. This same technique works great for multi-employee business cards, mailing lists,  recipe books, numbering for tickets/invoices, etc. This was the perfect solution to my problem and I have used the technique again and again for various projects at work.  After typing up all the information in Excel, I could then import the data into one Indesign layout and the information populated within a matter of minutes. I should mention that some of these projects, (such as complex numbering) require knowledge of Excel functions. Good thing we have the Googles!

I’ll use the example of custom mailing lists.

Step one: In Indesign, create the artwork you wish to use on the mailer. (If you are using the backside for address, create that side as well.)

Step two: Hopefully someone will provide you with a pre-made spreadsheet list of addresses, but if they do not, you’ll have to do it yourself. If you do not have Excel you can always use Google Spreadsheet. On row one of each column (A,B,C,D…etc.) you will name each piece of information.

For example: Row one, column A, you would type NAME. Row one, column B, you would type ADDRESS. Row one, column C, type CITY/STATE. And finally, Row one, Column D, type ZIPCODE.  Starting with Row two, each row will belong to a specific persons name and full address. NOTE: If you have a two page Indesign document (for a front and back mailer), make sure you account for the side you do NOT want an address to be applied to by inserting a blank row for every other address.

After you’ve finished typing up all the addresses you will need to save the file as a .CSV File. (Common Delimitated Values)

NOTE! Make sure you close Excel before trying to import the list into Indesign. This does not work if the file is still open, even if it is saved correctly.

Step three: In the Indesign file you’ve created, create a text box where you would like the data to populate. Make sure the text box is large enough to contain all the information you wish to add in there. Go to Window > Utilities > Data Merge.

In the Data Merge panel, it gives you directions on how to import files. Here they are again.

1. Choose Select Data Source from the panel menu. (This is the .csv spreadsheet you created, saved, and CLOSED)

2. Drag data fields from the panel to frames on the page (or, with an insertion point selected, click the data fields you want to insert).

(I’m adding my own step here!) Once your text box contains those items, (It will look like <<NAME>><<ADDRESS>><<CITY/STATE>><<ZIPCODE>>you can control their appearance by font, color and any other text panel option.

3. Choose Create Merged Document from the panel menu.

This may take a few minutes to complete! After it is done merging, Indesign will let you know if there was any overset text.

That’s it! Hope you find this method as useful as I have!

Leave a Reply