Take mailing list data from Microsoft Excel and produce labels in Word. Episode 906 will show you how.
Watch a new two minute video every workday to learn one of the 377 tips from the book Learn Excel 97-2007 from MrExcel.
Watch a new two minute video every workday to learn one of the 377 tips from the book Learn Excel 97-2007 from MrExcel.
Transcript of the video:
Hey, alright, welcome back to the MrExcel netcast, I'm Bill Jelen.
Well, we're getting close to the holiday season.
You might want to be sending out the holiday cards, and if you already have your customer list in Excel, we can take that data and create labels over in Word.
This is always a hard problem.
People say, "Why don't you write about this in your books?" And I say, "It's real 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 data starts out in Excel and I'm getting questions about it.
The first thing, that data in Excel has to have the headings in Row 1.
And in this case, it doesn't.
Now, we could delete that nice title up there, or instead just create a named range.
So I'm going to select all of that data and create a named range here-- click in the name box, type "Data", and now the first row of the name range has the headings.
So we'll save that and close it.
We can actually close out of Excel and go over to Microsoft Word.
Now, in Microsoft Word, we're going to start with a blank document; we're going to go to 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 document layout" and click Label options, and figure out what kind of labels you have.
Now, usually I have 5160s-- those are 30 labels to a sheet, but if you have, you know, company name and suite numbers, you might need a taller label like a 5162-- click OK, and you see it creates some blank labels there.
Now, we click Next, and this is where we get to browse for our Excel file.
So it starts out in some weird folder, I'll go back to the folder where I saved that file; specify; and I'm going to make sure to choose the range name-- the Data range name-- and first row of data contains column headers; click OK.
Alright, so it shows me all the data and we're good here.
Next, arrange your labels.
Well, they try and give you an address block that never works for me.
I always choose more items and basically I'm going to add these 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 kind of format that.
So I'm going to click right before the City and hit a return between the City and State, put a comma, space, between the State and Zip, put a space right before "Addressed to".
I'm going to hit return right before Street.
It's interesting: If there is no Address To, there is no Suite number, it won't put that line in.
You know, I put Title in, I really don't want that-- I'm going to get rid of the Title.
First... between first and last, I'm going to put a space, and then before first put a return.
So we get everything nicely arranged.
And once we have that first label, we then click this button over here on the right-hand side, "Update all labels".
Alright.
And basically, it just copies that layout down to all the other labels on one sheet.
Notice we still have a one sheet document; we'll click Next; Preview your labels; and they show us what the first sheet's going to look like; click Next again.
And it's interesting, we have to say "Edit individual labels"-- Edit individual labels will allow us to actually create a brand new document with all of the labels.
Now, here we have four pages of labels ready to send out 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.
Want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
Well, we're getting close to the holiday season.
You might want to be sending out the holiday cards, and if you already have your customer list in Excel, we can take that data and create labels over in Word.
This is always a hard problem.
People say, "Why don't you write about this in your books?" And I say, "It's real 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 data starts out in Excel and I'm getting questions about it.
The first thing, that data in Excel has to have the headings in Row 1.
And in this case, it doesn't.
Now, we could delete that nice title up there, or instead just create a named range.
So I'm going to select all of that data and create a named range here-- click in the name box, type "Data", and now the first row of the name range has the headings.
So we'll save that and close it.
We can actually close out of Excel and go over to Microsoft Word.
Now, in Microsoft Word, we're going to start with a blank document; we're going to go to 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 document layout" and click Label options, and figure out what kind of labels you have.
Now, usually I have 5160s-- those are 30 labels to a sheet, but if you have, you know, company name and suite numbers, you might need a taller label like a 5162-- click OK, and you see it creates some blank labels there.
Now, we click Next, and this is where we get to browse for our Excel file.
So it starts out in some weird folder, I'll go back to the folder where I saved that file; specify; and I'm going to make sure to choose the range name-- the Data range name-- and first row of data contains column headers; click OK.
Alright, so it shows me all the data and we're good here.
Next, arrange your labels.
Well, they try and give you an address block that never works for me.
I always choose more items and basically I'm going to add these 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 kind of format that.
So I'm going to click right before the City and hit a return between the City and State, put a comma, space, between the State and Zip, put a space right before "Addressed to".
I'm going to hit return right before Street.
It's interesting: If there is no Address To, there is no Suite number, it won't put that line in.
You know, I put Title in, I really don't want that-- I'm going to get rid of the Title.
First... between first and last, I'm going to put a space, and then before first put a return.
So we get everything nicely arranged.
And once we have that first label, we then click this button over here on the right-hand side, "Update all labels".
Alright.
And basically, it just copies that layout down to all the other labels on one sheet.
Notice we still have a one sheet document; we'll click Next; Preview your labels; and they show us what the first sheet's going to look like; click Next again.
And it's interesting, we have to say "Edit individual labels"-- Edit individual labels will allow us to actually create a brand new document with all of the labels.
Now, here we have four pages of labels ready to send out 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.
Want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
