Easier, Better, Faster, Stronger
A sturdy guide to using Adobe InDesign’s Data Merge feature
As designers, the first step in taking our thoughts from a physical space to a digital one is often transcribing them from stickies and whiteboards into spreadsheets. This allows for greater collaboration with partners, advanced handling of data and information, and documentation. Transforming these spreadsheets into effective communication tools for presentations or workshops isn’t so easy. Wrangling the multitude of unique data bits and making sure the text is styled correctly, color coded, and easily updatable can be time consuming and frustrating at best, but transforming a sea of data into something visual is a powerful tool and will be well worth your efforts.
One tool we find useful for streamlining this process is InDesign’s Data Merge feature. Data Merge transforms large volumes of spreadsheet data into neat and orderly things like sheets of mailing labels, directories, or even infographics. Data Merge takes the information you have in your spreadsheet and automatically places it in repeating elements within an InDesign file. We find Data Merge particularly useful when creating communication tools specific to the service design field like service blueprints, concept cards, and others things that require a lot of complicated data to be presented in a consistent manner. Data Merge helps us to produce these tools with ease while avoiding tedious and error-prone tasks like text styling and copy-pasting of repeating elements. You can think of the Data Merge function like an automated copy-paste ability on steroids.
In this guide, you’ll get an overview of how Data Merge works, learn what common (but non-obvious) bugs to watch out for, and gain some pro tips and additional resources. Before you know it, you’ll be known as the Data Whisperer.
Table of Contents
1. Getting Started
2. Setting Up Your Spreadsheet
3. Creating Your Template
4. Doing the Data Merge Dance!
5. Common Problems to Watch Out For
6. Pro Tips
7. Additional Resources for Overachievers
1. Getting Started
To begin, you need to have a spreadsheet application that can export to a .csv file (Microsoft Excel, Apple Numbers, and Google Sheets all work) and Adobe InDesign.
If you’d like to follow along with our example files, you can download them here. The zip includes an example spreadsheet file and an InDesign template.
Next, collect your files for Data Merge. Include everything you are going to use for the data merge inside the same folder (so not sub folders), including your InDesign template, spreadsheet file, and visual assets.
To give you an example of how we use the Data Merge technique in our design work, we put together a service blueprint detailing an experience at a Bay Area-based coffee shop known for their individually handcrafted coffee drinks. A service blueprint, in short, is an operational tool that visualizes the components of a service experience in enough detail to analyze, implement, and maintain it. It helps us to communicate and see how the experience will be supported by staff, operations, and systems within the service. If you’re not going to make service blueprints, don’t fret! The steps outlined below can be useful in creating a myriad of file types: service blueprints are just our most common use case of Data Merge, whereas creating something like a set of project cards is more straightforward and would be a great starting place for beginners.
To begin the example service blueprint for this blog post, we “conducted research” (i.e., buying coffee and hanging out). We then organized our observations into a rough draft service blueprint.
To bring this blueprint draft from an analog matrix of stickies to a digital form we could more easily refine and collaborate with, we still need to transfer the information to a spreadsheet.
2. Setting Up Your Spreadsheet
Once you’ve captured, collaborated and refined your content in the Excel file, it’s time to ready the data to be merged via InDesign. But before importing your spreadsheet into InDesign, it’s important to organize the content of your spreadsheet into columns and not rows. For a blueprint, this means you need to flip it to read top-to-bottom, not the traditional left-to-right reading of a blueprint. Sadly, InDesign only reads data vertically, and in columns. It cannot and will not import data correctly if there are not unique headings for each column. To help minimize the mental overload, we recommend building your spreadsheet in whichever orientation makes sense to you and then transpose your data only when you’re ready to start the data-merge process. To perform the task of moving your data from rows to columns, use the “transpose” function in your spreadsheet software to quickly reformat for the data merge:
In the eyes of the Data Merge feature, each heading (1) in your spreadsheet becomes the “data field name,” and its column (2) becomes the “data field.” Each row (3) will become a “record:”
Data Merge only reads .csv (comma-separated values) and .txt (tab-delimited text) files; we’ve chosen to work with .csv. Exporting spreadsheets to these file types strips them of formatting, formulas, and special characters. Make sure to remove any line breaks within cells, including soft returns, as they won’t be imported properly.
Data Merge doesn’t just place text — you can add images to the data you import, which will be placed into image frames and linked just like any other graphics in InDesign.
To have Data Merge place an image file, create a new column (data field) that will tell InDesign to import your images. Begin the column heading (data field name) with an “@” symbol (this marks the data field as external files to be linked). In the cells below, enter a ‘/’ and corresponding file name, including the extension (.png, .jpg, .ai, etc.). Your file name should be formatted like this “/example.ai”. Make sure your image file names are exactly the same in both the .csv file and InDesign file.
Note, some spreadsheet applications won’t allow you to do this. If you’re working in one of these, enter an apostrophe before the “@” so it looks like this: “ ‘@ .”
Our example blueprint includes icons to visually denote which staff member is involved at each step. To get your Data Merge to include icons, create one column in the spreadsheet where you’ll specify the icon file you want, in our example, the staff member icons. We wanted to have two different icons for the front-stage staff boxes in our blueprint, “barista” and “cashier”.
If you want to place an icon in a new location, you’ll need to set up a separate column for each location you want an icon to appear. In our example we use this ability to have separate placement and icons for front-stage vs. back-stage staff. As a result, our example has two columns for icons “@frontstafficon” and “@backstafficon,”
If your final design has repeating elements (reference example below) be aware that you only have to set up a single example of the repeating element, as the Data Merge will auto-populate the rest of the final design (see Step 4 for more detail on how to properly set up your spreadsheet to create repeating elements).
3. Creating Your InDesign Template
A Data Merge template is a master InDesign file that uses placeholder elements to represent where the information and images will be merged into from your spreadsheet. When designing the template, include anything that will be a repeated element in your final design and contain the data from your spreadsheet. These repeating elements could be a number of elements together, like a project card and it’s different fields, or a single element, like the blueprint boxes in our example. In this example template, we’ve set up frames for each of the channels, icons, and stage labels; these will repeat in every column of the blueprint we’re creating in InDesign. Be aware that you only have to set up a single example of the repeating element, as Data Merge will auto-populate the rest of the final design (see Step 4 for more details on how to properly set up your spreadsheet to create repeating elements).
Designing as much as possible before merging the data, including colors and styles, will simplify later processes and will make re-merging much quicker if it becomes necessary. Any structural elements that are not repeated, like titles, labels, and keys, will be added after the data is merged.
To link the InDesign file with the .csv file, pull up the Data Merge panel (Window > Utilities > Data Merge) and click the fly-out menu icon (located in the upper right-hand corner of the Data Merge panel) to navigate to Select Data Source. From here, find your exported .csv file and click through to Import (InDesign should detect the proper settings).*
Each of your data fields should now appear in the Data Merge panel with an icon that denotes what type of data it holds (text or image). Drag and drop the data field names to their proper frames. Text frames can hold more than one text placeholder, and line breaks and other connecting symbols or strings can be added here. Placeholder text should appear as <<data field name>>* (placeholder text for images may not appear depending on the frame size). Continue to format the template as desired.
4. Doing the Data Merge Dance
When you’re happy with your InDesign template, click the fly-out menu icon (located in the upper right-hand corner of the Data Merge panel),here you can choose to preview the results of the merge, and when you’re happy with the results, click the “merge” option in the fly-out to create your merged document. When creating the final merged document, you’ll have some options on how that will take place. Choosing to put multiple records (rows in your spreadsheet) on one document page will create repeated iterations of your template with each of your records, while putting a single record on a page will create a new page for each record. For service blueprints, we choose to put multiple records on one document page to create repeated iterations of the template containing each of the records. The “Multiple Record Layout” tab—relevant only if you choose to put multiple records on a page—allows you to control the spacing, margins, and layout of new records. Lastly, the “Options” tab is important especially for placing images in frames of different dimensions within the template, since it includes an option for fitting preferences. Merging the data will create a new InDesign document, so save both the template and the merged document.
The merged document is complete!
After you’ve merged the data into the template, it’s likely that you’ll want to add some final touches or make adjustments before sharing it with others. You will also need to delete any of the blank elements that were created in the Data Merge process. On our service blueprint, we removed the empty squares, added flow lines, the line of visibility, experience stage labels, and or course, a title. And there you have it!
5. Common Problems to Watch Out For
Hopefully you’ve now got an awesome thing that you can’t wait to share with the world, but in our experience, little problems tend to sneak in for even the most savvy Data Merge masters. Here are a few common problems we’ve run into and likely solutions for each:
My spreadsheet looks clean but some of my data is in the wrong place or even missing!
Empty-looking cells can be riddled with hidden or invisible characters. Try deleting all of your empty cells and import again. If this doesn’t work, using a .tsv file instead of a .csv file, or copying the data into a new spreadsheet may help.
I can’t get InDesign to import my .csv file and I’m getting a weird error!
It’s likely that your .cvs file is still open. Close it and check again. Leaving it open prevents InDesign from importing it.
I’ve typed in “<<data field name>>” into my template but it’s not importing data!
You must either drag-and-drop or, having clicked into a text frame, click on the correct data field in the Data Merge panel.
Ugh. I’ve updated some information on my spreadsheet but I don’t want to do the whole thing over again!
After making changes to your data, you can re-save your .csv file and update the link to InDesign by selecting Update Data Source from the Data Merge panel’s drop down menu.
My design is shifting on the page after the merge!
This usually happens when the page size is adjusted from the original size when the page was created. The Data Merge function only references the original file measurements. Creating a new file at the final size you need should clear up the issue.
I’m trying to merge several sheets but InDesign isn’t importing all of the data!
InDesign has trouble handling multiple sheets into one template. If you have multiple sheets, you’ll either need to consolidate them into one or export multiple .csv files.
I’d like to merge something that requires a list format, with line breaks, but I though those don’t work?!
The easiest way to include line breaks is to separate the text into spreadsheet columns based on where you want the line breaks to occur. Then, when you’re setting up your template, separate their placeholders with line breaks.
6. Pro Tips
- Writing formulas once and dragging them into adjacent cells will save you from typing the same information into individual cells over and over.
- The concatenate function can read and combine characters from individual cells together to form a string. We use this feature, together with the “if” function, to toggle icons on and off. For example, the Google Sheets formula =IF(ISBLANK(G8)=FALSE,”/”&CONCATENATE(G8)&”.ai”,””) first checks if cell G8 is blank. If it is, nothing will appear in the new cell. If G8 contains information, Google Sheets will use the contents of cell G8 to insert “/(G8contents).ai” into the new cell.
- To color-code, create vector image files that are solid blocks of color and add them as you would any other image to your template. This is particularly helpful when creating sets of cards that should be easily categorized or sorted.
- Find the longest records in your spreadsheet and use them as placeholder text to design your template. You’ll have fewer fixes with overset text after you merge.
7. Additional Resources for Overachievers
Adobe’s Data Merge Instructions
Tutorial: Adding Images by Lynda.com
Troubleshooting Data Merge Errors by InDesignSecrets.com
A Guide to Service Blueprinting Written by Nick Remis and The Adaptive Path Team at Capital One
Created by: Allison Huang, Vince Losanes, Tim Gruneisen, Dianne Que, Iran Narges, and Nick Remis
