Hey, all right welcome back to Mr. Excel net cast. I’m Bill Jelen. Well we’re getting close to the holiday season. You might want to be sending out a holiday card and if you’re already have your customer list in Excel we can take that data and create labels over on word. This is always a hard problem. People say why don’t you read this in your books and I’ll say it’s really simple. It’s not an Excel problem it’s a word problem. Go find MrWord.com and see if he puts it in his books but let’s talk about it since the day starts out an Excel and I’m getting questions about it.
The first thing that data in Excel has to have the headings in row one and in this case it doesn’t. Now we could delete that nice title up there or instead just create a name range. I’m going to select all of that data and create a name range here. Click on the A box type data and now the first row of the named range has the headings. So I’ll save that and close it. We can actually close out Excel and go over to Microsoft Word. Now Microsoft Word, I’m going to start with that blank document. We’re going to the tools. Letters and mailings and say that we want to do a mail merge and it brings up a six step process. In step one we choose labels and click next. In step two, you want to say you want to change the document layout and click label options and figure out what kind of labels you have.
Now usually I have 5160 those are 30 labels to a sheet but if you have you know company name and sweet numbers you might need a taller label like the 5162. Click Ok and you see it create some blank labels there. Now we click next and this is where we get to browse for Excel file. So it starts out in some weird folder or go back to the folder where I save to that file. Specify and make sure it choose the range name. The data range name and first row of data contains column headers. Click Ok all right so it shows me all the data and were good here. Next arrange your labels.
Well they’re trying to give you an address block that never works for me. I always choose more items and basically I’m going to add this items company first. See I’m double clicking last title, street, address, city, state and zip and then click close and we get a horrible looking label over here. Now I’m going to go through and actually can’t format that so I’m going to click right before the city and here return between the city and state put a comma space, between the state and zip put a space. Right before address to I’m going to hit it return, right before street. It’s interesting if there is no address too. Theirs is no zip number wont put that line in. You know I put title in and I really don’t want. I’m going to get rid the title first between first and last. I’m going to put a space and then before first put return.
So we get everything nicely arrange and once we have that first label we then click this button over here the right hand side update all labels. All right basically just copy that layout down to all the other labels on one sheet. Those we still have one a sheet document. We’ll click next preview your labels and they show us what the first sheets going to look like. Click next again and it’s interesting we have to say edit individual labels. Edit individual labels will allow to actually create a brand new document with all of those labels and here we have four pages of labels ready to send down for all of your holiday cards.
So there you have it how to do a mail merge to take data from Excel and create labels in Microsoft Word. I want to thank you for stopping by. We’ll see you next time for another net cast from Mr. Excel.
Transcription by:
Scribe4you Transcription Services