Subscribe or Login to watch video

Login or Subscribe to watch video

71 - How do I combine Microsoft Excel spreadsheet in InDesign?

Video: 71 of 77


Questions & Comments

David Cross - 9 months ago

Hello, Q: Can an indesign data merge handle having several spreadsheets on different pages of the same document? I have several pages that contain charts with exactly the same listing headings eg. Turnover (£000s) | Actual (£000s) | Budget (£000s) | Variance (£000s).

Daniel Kyriacou - 6 months ago

That was a bit of fun.

It looks like the logo is too high up and the text is in too far but it's not showing the bleeds or crop marks.

Daniel Kyriacou - 6 months ago

this is on the wrong question. I'll post on the correct one also

ALL ACCESS: $12 per Month + Cancel anytime   

Video Transcription

In this video we're going to take this out of Excel file, with all this data in it, and then make it work in InDesign by creating these place holders. First Name, Last Name, Company. And then generating a file that has loads of people's names in it. You can see here, many hundreds. So let's go and do that now in InDesign.

The first thing we need to do is, we're going to create our Swing Tag. So we'll go to 'File', 'New', 'Document'. Now best thing to do for size is to go down to your local stationery store and just figure out what the plastic sleeve they go into, the size of text. I'm pretty sure they all use inches, even if you're not in America. 4" x 3", but go and check, just to make sure. Sometimes the business card sizes, just to take business cards. But we're going to use the 4" x 3", and in terms of this one, we're going to set the Margins to '0' just because I actually don't need them for this case. And in terms of Bleed, we'll use an eighth of an inch, so '0.125', or 3mm all around. I'm going to grab my Rectangle tool. I'm going to draw a rectangle that covers this bottom bit. That's where the company name's going to go. And I'm going to fill up with Red. It's all part of that, because it's a new document, so I'm going to have to click over here. I'll make it a little bit smaller.

The next thing I want to do actually is bring in my Logo. 'File', 'Place', in your '05 Data Merge' there's one called 'Green Logo'. Bring him in. And get him about there. 'W'. So now we need to connect the Excel file. The trouble is, Excel won't actually connect directly. So weirdly you'll have to open your Excel file in Excel, which is not weird, I guess. We're going to start with this one here, 'Garden Awards Attendees'. And that's what this file looks like. You can see, there's lots and lots of data in this one, but the xls or the sls won't work. You just need to open it up in Excel, or Google docs, whatever you can to open it up, and go to 'File', and just do a 'Save As'. So instead of 'xlss', we're going to drop that down and go to this one that says 'csv'. That's the one that InDesign can accept. Don't need to change anything else, hit 'Save'. I already done it for us in our examples.

So if I connect it now, it's going to connect to the csv but not the Excel file. Let's open up 'Data Merge' panel. Let's go to 'Window', 'Utilities', 'Data Merge'. And it kind of tells you what to do here, but the first one is the Nav sandwich here. And we're going to click on 'Select Data Source'. Remember, you can see these ones are grayed out, it won't work for these, I'm in '05 Data Merge', and we're going to work with the 'Garden Awards Attendees'. And you'll see that, if I jump back into Excel you can see it's brought through the Heading names, or the Column names. So if you don't have these, go into your files and just add them to it. It's going to make life easier working in InDesign.

You might have to insert a row, just type him in. You'll have to use all of these. I want First and Last Name, I don't want the Entry Fee, I don't want their email address, or the Entry Date. I want the Company name. So you can decide on what you want to have. I'm going to move that off to the side, I'm going to grab the Type Tool. And I'm going to draw a nice big Type Box. And all I need to do is, with my cursor flashing, I'm going to click on 'First Name'. Put a 'return' in, and put in 'Last Name'. I'm going to style these now to look like it did in the earlier part of this video. I'm going to move them, so they're kind of centered. I'm going to highlight these guys, and make them centered. They're both going to be 'Roboto'. So let's go 'Roboto'. It's going to be 'Bold'. I'm going to increase—

This one here, I'm going to max up the size. How big? You'll have to look at your names list and see how many characters that person has. If you got somebody with a really big name you might have to have slightly smaller font. We can test it, it's easy enough. Select it all, I'm going to make it green color. This guy here is going to be the 'Light' version. Now I'm just messing around, designing. Now it's hard to work with this First and Last Name. So what we can do is, see it says 'Preview' in the corner, it will pull through only the first entry from your database list. So Robyn Ward, not very long, so it's going to fit just fine. I'm going to select it all, and make it a bit bigger. You can cycle through, see this arrow. I'm going to go through, Robyn Ward, Mark Rose. Lyty, Sergio. We got lots of names, and none of them are very long. So this is going to work out just fine.

Now this is not actually done, this is just previewing. You can turn it on and off. But it's not meant to be the finished one. There's a last part to this to export it. This is just kind of previewing it through. There's one more thing I need to do, it's my Company Name. So I got nothing selected, grab the Type Tool, drag out a box, and I'm going to click in 'Company'. I'm going to do the same thing, I'm going to center it, grab it. The Company Names get quite long so I'm going to have it so it's all the way across. I'll use the Eyedropper tool, like we did earlier. I'm going to grab all this. Grab my Eyedropper Tool, steal that one. This time I got the 'Roboto Thin'. Make it a bit smaller, maybe make the font 'Paper'. Hit 'Preview'.

You can see, some of these business names are a lot longer. So I probably have to go smaller on the font. Select all this. Little smaller, so it all fits in. You might have to do a print as well because this is printing at '13 pts'. It's not very tiny, but it might be a little too thin to read. So I'm going to go up to 'Regular'. So let's have a look through a few of these. They look like they're all going to fit. So that is how you connect the two up.

The next thing I want to do is export this thing. First of all I'm going to save it. And let's call this one, in 'Desktop', 'InDesign Class Files', this one is going to be called 'Green at Heart Awards Night'. 'Tags'. I'm going to call this one 'V1'. To save it, I need to turn this off. This here is like the template. You're going to end up with two files. You got this one, plus the ones you generated with all the names in it. And you can click this little icon here, 'Create Merged Document'. Click on this. There's a few different options. We're just going to leave it all as standard because that's probably what you're going to need to do. Let's click 'Create'.

What happens is, we've got-- I've got two files open at the moment but it's generated a third. A nice little thing that appears here "No over-set text were generated." Over-set text is that little red cross in the corner that says the text couldn't fit. This can be good, say you're doing business cards, and it's for-- I've done jobs for 10,000 people at a big electrical company, and you hit that button, and you don't want to have to go and double check 2000 names, or 10,000 names to see if any of the names are too long and won't fit. This is just telling you, "Don't worry, all is cool."

The difference between this one, which is my template, which has got First and Last Name, like place holders, and the second one is the naming. Can you see here, it's called 'V1', it's got hyphen 1, it's not like super clear but if I save this one now-- instead of hyphen 1, I might give it a better name. I might call this one-- can't think of a better name. Don't call it Final. Remember, that will kill this job. I'm calling it Output because I can't think of a better name at the moment. Maybe it's easy just to call the first one Template, and the next one Not Template. So it's saved, and what we can do is look at the Pages panel. Mine has disappeared. 'Window', let's go to 'Pages'. And I drag it back in here. I know why, I dragged that out. I was using it for a different project while I was making these tutorials.

As you can see, I have a page 1, page 2, page 3, can you see all these names here? It's made a document with all of these. So if I'm printing in the office, I can have print. What is it? A hundred different names. Or I can send this to the printer now, to get printed. And hopefully get trimmed up, and then sent back to me, so I can put them into that little clear name sleeves at the welcome desk, when people arrive. I got my heart set on this Gala Night now even though I've totally just made this thing up. Anyway, I'm sure it would be a great night. That my friends, is Data Merge.

ALL ACCESS: $12 per Month + Cancel anytime