*** From the Archives ***

This article is from September 8, 2011, and is no longer current.

InDesign How-To: Designing with Data

Excerpted from InDesign Magazine, June/July 2011 (issue 42). Subscribe now!
In addition to the HTML version of the excerpt below, you can also download the excerpt as a PDF that retains the full design of the magazine. This PDF is best viewed in Adobe Acrobat or the free Adobe Reader.


Data and design might seem like strange bedfellows, but with a little InDesign know-how, you can combine the two to achieve layouts that are effective and informative… and do so surprisingly fast. For example, I recently used InDesign’s built-in Data Merge to bring a modest spreadsheet to visual life (and get around the limitations of Table and Cell Styles), saving a tremendous amount of time in the process.
This layout, which I created for Inbound Logistics magazine, originated as a spreadsheet of data compiled about the strengths and weaknesses of locations around the world for companies to consider setting up logistics operations. The spreadsheet (Figure 1) contained valuable, well-researched information that warranted a presentation more interesting than a mere table. Instead, I chose to produce individual tables for each country, including an image of its flag, and distribute those tables across several spreads against a map backdrop (Figure 2).
Figure 1: The Excel spreadsheet from which this project started has valuable information but no visual appeal.

Figure 2: With the help of Data Merge, I generated 24 of these complex tables from the spreadsheet data, then used them throughout this multi-page magazine layout. Click the image below to see a larger version.

Although the end results were impressive, the Data Merge part didn’t vary at all from any textbook Data Merge. Let’s take a high-level look at that process.
Step 1. Understand the Components
There are two things you need for an InDesign data merge: an InDesign document, and a data source file. The InDesign document is where you design static page elements and establish data placeholders in preparation for the merge. The data source is a tab- or comma-delimited file (most commonly saved from a spreadsheet application like Microsoft Excel or from a database). From these two, a new “merged” InDesign document is produced that incorporates the information from your data source into your design (Figure 3).
Figure 3: The Data Merge workflow requires a data source linked to an “interim” InDesign document with data placeholders, from which another merged document is generated.

Step 2. Plan, Design, and Decide
This isn’t, technically, an InDesign step. It’s a combination of sketching, trial-and-error, problem-solving, and decision-making that gives you a concrete goal to shoot for. What matters most is that you have a final design planned out and a data merge source that is ready to generate multiple iterations exactly the way you want. After you’ve run the data merge, any adjustments you make will need to be done object-by-object. You want to avoid that additional work, or keep it to a minimum. So it’s worth taking a step back to be sure everything’s thought through before you start.
In my layout, each country’s data was presented in a complex table that included other tables anchored within it as anchored objects, as well as anchored text frames and images, and multiple paragraph styles. The tables were so precisely formatted that they were beyond the practical capabilities of InDesign’s Table and Cell Styles feature, so I opted to create one “perfect” table as a basis for the others (Figure 4).
Figure 4: Using text for one country’s data copied and pasted from the original spreadsheet, all of the design choices and practical considerations—available space, arrangement of individual pieces of data, etc.—were first rendered in black-and-white to speed up the process, then I added color. With the aesthetic choices decided and applied up front, the rest was in the hands of Data Merge.

Step 3. Start with the Data
You can’t put data placeholders into your InDesign document unless you have a data source, so let’s start there. I’ll assume you received an Excel document as your data source. Excel is the ideal environment for sorting and organizing data until you’re ready to hand it off to InDesign. There are only two absolute rules you must follow to make a data source InDesign-ready:
• It must have a header row.
• All image files the data source refers to (see step 4) must be at their specified location.
Beyond that, InDesign is pretty flexible. For example, you’re not required to use all the data in the source file. You may link to data from a 20-column spreadsheet, but if there are columns of data you don’t need, just don’t create placeholders for that data in your layout. Also, your spreadsheet can contain blank cells (a.k.a. empty fields) if there’s no relevant data for any one item. InDesign even offers options for handling empty fields (see step 10).
Step 4. Identify your Image References
InDesign’s Data Merge isn’t just a mail merge with better typography. You can also use it to include any type of image you could manually place in a layout. All you need to do is identify the full path to the image file in your data source. On the Mac, this could be something like Macintosh HD:userfolder:Projects:Client Name:Images:imagename.psd. Windows users should replace the colons in that example with backslashes (i.e., C:userfolderProjectsClient NameImagesimagename.psd).
To tell InDesign that a column contains image references, add the @ symbol at the beginning of the column header (e.g., @photo). Unfortunately, if you type this in Excel, it will think you’re creating a formula (and complain that you’re doing it wrong), so you need to type an apostrophe (‘) before the @ symbol. When you hit Enter, or tab out of that cell, the apostrophe won’t be visible, but Excel will accept the @ in the header cell, and InDesign will have what it’s looking for as an identifier of image references (Figure 5).
Figure 5: References to images in an Excel spreadsheet require the full image file path and an @ symbol at the beginning of the column header name.

Step 5. Save as Tab- or Comma-Delimited
Before a spreadsheet can be used by InDesign, it needs to be saved as either a tab-delimited (.txt) or comma-delimited (.csv) file. You can save a tab-delimited version from Excel by choosing Save As… , then selecting Text (Tab delimited) from the Format pull-down menu in the Save As dialog, but you’ll be confronted by up to 8 export dialogs and cautionary alerts. I prefer a quicker method: Select all of the data from your Excel spreadsheet (including the column headers), copy it, paste it into any plain-text editor, and save it as a “.txt” file. If you need or prefer to go the CSV route (it makes no difference to InDesign), choose Save As… in Excel, then CSV (comma-delimited) from the Format pull-down.
Step 6. Link Your InDesign Document to Your Data
Once your data’s in one of those Data Merge-friendly formats, you need to connect it to your InDesign file. With your InDesign data template file open, go to Window > Utilities > Data Merge (CS5 and 5.5) or Window > Automation > Data Merge (CS4 and earlier) to access the Data Merge panel.
From the panel’s flyout menu, choose Select Data Source, then navigate to and select the tab- or comma-delimited file you created in the previous step. Once imported, your data source file’s column headers appear as a list in the Data Merge panel (Figure 6). Text-based data appears with a “T” icon next to the header name, and image references (any column beginning with the @ symbol) have a picture icon next to them.
Figure 6: The Data Merge panel reflecting the column headers of the data source file. Note the image icon next to the “Flag Art” Item.

Step 7. Add Data Placeholders
In your layout, replace any temporary text you may have used in your “template” with a placeholder from the Data Merge panel (Figure 7). To do this, either select a column header name from the Data Merge panel and drag it into a text or graphic frame, or put your cursor in the appropriate place in a frame, then click on the column header name in the Data Merge panel. Placeholders appear as the data source’s column header name, surrounded by pairs of angle brackets, in whatever style you’ve applied in your layout.
Figure 7: The table “shell” with data placeholders added. Red dots appear where the placeholder name is treated as overset text because its name is longer than the data that will ultimately appear in the cell. The labels for the data (GDP, Exports, etc.) are static elements that are common to all tables. Note the <> placeholder in the graphic frame where the flag will go.

Step 8. Clear the Decks
InDesign can do a single-record or multiple-record data merge. The former creates a new page for each record in the data source file; the latter fits as many records as it can on one page before creating another page.
To be able to produce a multiple-record layout, you must follow a few rules about how you set up your “interim” InDesign document for the merge:
• A multiple-record data merge can only be produced from a single-page InDesign document. Having two or more pages restricts data merge to single-record mode.
• Everything on the document page—even if it has no data placeholders in it—will be duplicated in the merge process. If the other objects on your page prevent more than one record from fitting (such as a big background image), Data Merge will behave as if it’s producing a single-record layout because the document can’t accommodate more than one record per page. So, If there are page elements you want on the page that don’t need to be part of the merge, move them to the master page.
Step 9. Preview Your Results
To test your success with the set-up process, check the Preview box at the bottom of the Data Merge panel (Figure 8). InDesign then temporarily replaces the placeholders you added with the information from the first record (row) in your data source. You can click the forward and back arrows at the bottom of the Data Merge panel to preview each record (Figure 9) and check for problems, copyfitting issues, etc. This is particularly important in a table-based layout like this because overset text in a table just disappears. Since data length can vary, you want to be sure that a table-based design will accommodate the longest possible instance of any data element.
Figure 8: The Data Merge panel’s Preview checkbox lets you swap out placeholders for actual data prior to creating the merged document. The numeric field allows you to jump to any record (row) in the data source file and preview its data in the layout.

Figure 9: A record previewed using the Data Merge panel’s Preview feature. All data fits in the allotted space even though the placeholders didn’t.

If you plan to create multiple records per page, as I did, you’ll need to go beyond the Preview checkbox and choose Create Merged Document from the panel menu. In the Records Tab of the Create Merged Document dialog (Figure 10), change the default from Single Record to Multiple Records, and select the Preview Multiple Record Layout checkbox.
Figure 10: Multiple records previewed from the Records tab of the Create Merged Document dialog. With the default settings, only six instances of the country data tables can fit on this page. The frame containing the blue background on the page is on the master page, so it’s not a factor in the merge process. Click the image below to see a larger version.

If the multiple record layout needs tweaking—to fit more records on a page, add or remove space between records, etc.—switch to the Multiple Record Layout tab. There, you can adjust margins, space between columns, space between rows, and indicate whether the layout builds Rows First (across, then down) or Columns First (down, then across). By adjusting my margins and column spacing (Figure 11), I was able to fit nine country tables on a page instead of six. I ultimately moved them around in a more free-form fashion in the final layout, but for something like a yearbook where the number and arrangement of records per page is essential, these controls make adjustments simple and immediate.
Figure 11: In the dialog’s Multiple Record Layout tab, I adjusted margin settings to fit three tables across the page, resulting in a grid of nine tables per page.

Step 10. Set Additional Options
The Options tab of the Create Merged Document dialog allows you to establish frame fitting and centering options for the images imported during the merge (Figure 12). You can also opt to have InDesign remove blank lines where there’s a placeholder, but no data (a blank cell) for that placeholder in a given record. There’s also an option for setting a cap on the number of records the merged document can contain. Once that limit’s reached, additional documents will be created containing the remaining records up to that per-document limit. So, if your data source contains 200 records, and you set a 50-record limit, four merged documents will be created.
Figure 12: Additional controls in the Options tab are frame fitting and a way to split the merged document into multiple documents by setting a maximum record amount per document. I opted to have my flag images fill their frames proportionally and be centered within those frames. This setting applies to all images that are part of the data merge.

Step 11. Create the Merged Document
Clicking OK in the Create Merged Document dialog both commits your settings and produces the final InDesign document (as “yourfilename-1.indd”). Unless you need to run the merge again, you’re finished working with the “interim” document where you did all of your set-up. (It’s a good idea to save that file, though, in case the data changes and you need to merge again.) In the new, merged document, there is no longer a link to the data source file. All of the data brought in during the merge process becomes static text within the InDesign file.
If your design calls for each record to appear on a defined grid, you’re done. However, for this project, Data Merge was the most efficient way to create 24 complex, identically structured and formatted tables, which I then manually moved around in a layout with map backgrounds. As a final step, I customized some table attributes individually to better connect them to the maps, and I added introductory text for each region (Figure 13). But that was all fine-tuning, and I could spend the time doing that because using Data Merge for the heavy lifting saved me hours of potentially painstaking work.
Figure 13: A single page and a spread from the final layout. I added the maps and introductory text to the merged document and arranged the tables accordingly. I also did a little color customization (not possible with Data Merge) to match the country colors on the map.

Everywhere you look, there’s no escaping how data-driven our world is becoming. All that data, in theory, is making our lives easier, more informed, and everything more immediate. But data is only as good as how you present it. Using InDesign’s Data Merge, you can take advantage of all of the benefits of structured data and transform into great-looking layouts.
 

Adobe Digital Media Solutions Consultant, Designer, author, podcast host, speaker, instructor, tech nerd, husband, father.
  • Anonymous says:

    What a great solution to an otherwise time-consuming problem! I really like how the final infographic turned out.

    Can you please tell me what font you used for the layout? It’s familiar, but I just can’t identify it. Thanks!

  • Anonymous says:

    That’s pretty neat, but is the data linked or just imported? For example, if I modify the Excel sheet, will it update the data in the InDesign file?

  • Anonymous says:

    Glad you liked the article and the layout. The font is Interstate, used with various weights and widths throughout the project. https://www.fontbureau.com/fonts/Interstate/

  • Anonymous says:

    Data Merge is a one-time association. Once you create the merged document, it is no longer connected to your data (although the original “source” or template document is, with its placeholders). You’d need to produce a new merged document from up-to-date data, which is why I waited ’til late in the process to do the final data import. From that point, all changes were made in the InDesign layout only.

  • Anonymous says:

    my pea brain cannot handle the complexity of this article.

  • Anonymous says:

    Great article! Do you have a website where I can go and get more useful training like this? please. This is the kind of tutorials that should be out there ! well done and thanks for sharing .:)
    Daniel

  • Anonymous says:

    Hello Michael,

    This was very helpful, thank you. However, I am stuck & wondering if you could offer some inDesign wisdom. I am working on a fairly simple project of creating guest place cards for a dinner event. I set up a single document with 4 place cards on the page. I followed the instructions exactly for laying out Multiple Records however data merge created 4 identical place cards on each page instead of filling in the fields each with a different name. So I now have quadruple the place cards I need. Do you know what could be going on?? Any advice would be much appreciated. Thank you so much!
    Cristina

  • Arif_SML Bangladesh says:

    Hi

    I have learn how to marge easy and best way.Thanks to you.

  • MPatterson says:

    This was an easy to follow toot, a great solution to easily format quarterly reports that are full of numerical data.

  • Sarah says:

    I’m looking for guidance on how to efficiently get the filepath of the images into the excel spreadsheet. Is this a totally manual process? Thanks!

  • Barb says:

    I am also trying to get multiple records to flow in one large, multi page document. Some of my records are larger than others, what is the secret to get this into one multi-page doc with all text linked? I need to be able to ‘select all” text to make any page changes / adjustments? Many thanks in advance.

  • Bobbie-Jo says:

    Hi, I am doing a 4 up mail merge and need to add a back page to the postcard as well. I know you can add a back page with a single data merge but is there anyway to add the back of the postcard into the document so I don’t have to print one side then the other?

  • Sansapance Lacktrouser says:

    I’m trying to do a 2-up merge so I can print door signs for the whole company. I want to print 2 per page, but InDesign will not merge the data so that 2 different records appear on a page. It just repeats the same data till the next page.
    All graphics are on the master. The data source is a .CSV file, listing Dept, FirstName, LastName.
    The only objects on the page are fields referring to Dept, FirstName, LastName. There are 2 sets of these fields, because, naturally, I want 2 name signs for 2 different people. What I get, though, is the same name sign twice on each page.

  • Cristina says:

    Hi! I’m working on the automatization of the prices of my company’s catalogue. I want to use data merging to put the prices and other product information in the catalogue I already have.
    The thing is, that I want to merge specific data to each product. All the data (price, alphacode, unit and price) may vary each year, but the numeric code of each product always stays the same. So I thoght to use the numeric code as a reference to merge the data into the catalogue. Is it possible? Thnaks!

  • Shama says:

    Why can’t I find Utilities under Windows menu in InDesign CS4? Is it a plug in?

  • >