This Podcast may change the qulaity of your office life forever!
So, You have address information in Excel and you want to produce mailing labels in Word. Should be easy, right? Moslty, Wrong. No venture into Word is easy. In Episode #1484, Bill will show us the hassle-free method to produce labels in Word 2010 using our Excel data.
...This episode is the video podcast companion to the book, "Learn Excel 2007 through Excel 2010 from MrExcel". Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
So, You have address information in Excel and you want to produce mailing labels in Word. Should be easy, right? Moslty, Wrong. No venture into Word is easy. In Episode #1484, Bill will show us the hassle-free method to produce labels in Word 2010 using our Excel data.
...This episode is the video podcast companion to the book, "Learn Excel 2007 through Excel 2010 from MrExcel". Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel for MrExcel Podcast, episode 1484: Mailing Labels in Word from Excel Data.
Hi, today's question, sent in by Mary Ellen-- that's right, Mary Ellen Jelen. Today, she wanted to do some mailing labels, and she said, "This has to be easy," and I'm, like, "No, it's not-- it's the hardest thing in the world".
First off, we have our data in Excel, and I'm going to save that.
So, we'll Save, and then File, Close.
Then, we have to switch over to this tiny, little add-in-- this tiny, little add-in you might have heard of-- it's called Microsoft Word.
Alright, and Microsoft Word is good at a few things that Excel isn't good at-- probably got three-- and one of them is the Mail Merge.
So, we Start Mail Merge, and we are going to Create Labels, and the labels I have are the "30 Per Page"-- the 1" by 2.63".
Good.
Alright, so, now, what the heck do we do now?
We see these Paragraph symbols and these other Next Record symbols.
Well, first, we have to Select Recipients.
And, I go to Type New List?
No, I'm going to Use Existing List and, of course, that list is not coming from any of these types of things; it's coming from Excel, what else?
It's really-- it's, it's-- an affront that they don't just make Excel be the default.
Where else would people be getting their data from?
So, we'll browse the right folder, and let's just locate Podcast1484, click Open.
They want to know if there's a named range, and when there's not, they're just going to say, "Okay, we're going to take everything on Sheet1$”.
First row contains Column headers?
Yes, that's right.
Alright, now, what the heck do we do now?
Well, the next place we go from Select Recipients is, Insert Merge Field.
We're going to build our label, and, see, these are the fields that I have--Name, Street, Apartment.
So I'm going to choose Name, and then Street, and then Apartment, and then City_St_Zip.
Alright, so, it adds those fields, and then I format it.
So, we come here, we add a little Return, and then add a little Return, and then add a little Return.
Now, if there's anyone here watching this who knows anything about Microsoft Word, or maybe you have a friend, or the Admin, who knows something about Microsoft Word, or even knows a Microsoft Word MVP, my question is: What the heck were they thinking, that they forced us, every single time, to come here to Home, open this little drop-down, and say remove the idiotic space before the paragraph?
I mean, when they-- when they-- check the SQream data-- Microsoft, if you're watching-- a million times a day, people have to go in and change that setting.
What-- it's the most insane thing.
Now, there's insane things in Excel, I'll grant you that, but this one, for us people-- non-work people-- coming over to Word, what are you doing here?
This just drives me crazy, but that's my little rant.
Let's go back to mailings, alright?
So, now we've created the first label, and then, the completely un-intuitive thing is, we have to say we're going to Update Labels.
And what that does, is that takes the layout here in the first label, and it replicates it throughout the whole first page.
So, right now, we have a one-page document, 30 labels, and all of this stuff.
Alright, so, Start Mail Merge, choose the type; Select Recipients, Excel; Insert Merge Fields; and then Update Labels.
Once you've done all of that, then you're practically done.
Just come out here to Finish & Merge, and we want to Edit Individual Documents- let me get that so you can see it, Edit Individual Documents.
That's the code word for, "Yeah, just put the labels in Word so I can print them or save them or something like that.
And we want All, click OK, and there is our data from the original data set.
So, here, this one had a Suite, uses four lines; this one didn't have a Suite, it'll leave the blank line there-- that's pretty smart, credit where credit is due, on that one.
So, that is it.
The Mail Merge from Excel to create labels.
Once you get the hang of it-- Start, Select Recipients, Insert Merge Fields.
Update Labels, Finish & Merge-- it's easy.
But, if you don't have those steps, this is one of-- just-- those horrific things.
Well, hey.
Well, I thank everyone for stopping by.
Tomorrow, we'll be back in a product we all know and love, Microsoft Excel, and out of this tiny little add-in here.
Well thank you for stopping by, see you next time for another netcast with MrExcel.
Learn Excel for MrExcel Podcast, episode 1484: Mailing Labels in Word from Excel Data.
Hi, today's question, sent in by Mary Ellen-- that's right, Mary Ellen Jelen. Today, she wanted to do some mailing labels, and she said, "This has to be easy," and I'm, like, "No, it's not-- it's the hardest thing in the world".
First off, we have our data in Excel, and I'm going to save that.
So, we'll Save, and then File, Close.
Then, we have to switch over to this tiny, little add-in-- this tiny, little add-in you might have heard of-- it's called Microsoft Word.
Alright, and Microsoft Word is good at a few things that Excel isn't good at-- probably got three-- and one of them is the Mail Merge.
So, we Start Mail Merge, and we are going to Create Labels, and the labels I have are the "30 Per Page"-- the 1" by 2.63".
Good.
Alright, so, now, what the heck do we do now?
We see these Paragraph symbols and these other Next Record symbols.
Well, first, we have to Select Recipients.
And, I go to Type New List?
No, I'm going to Use Existing List and, of course, that list is not coming from any of these types of things; it's coming from Excel, what else?
It's really-- it's, it's-- an affront that they don't just make Excel be the default.
Where else would people be getting their data from?
So, we'll browse the right folder, and let's just locate Podcast1484, click Open.
They want to know if there's a named range, and when there's not, they're just going to say, "Okay, we're going to take everything on Sheet1$”.
First row contains Column headers?
Yes, that's right.
Alright, now, what the heck do we do now?
Well, the next place we go from Select Recipients is, Insert Merge Field.
We're going to build our label, and, see, these are the fields that I have--Name, Street, Apartment.
So I'm going to choose Name, and then Street, and then Apartment, and then City_St_Zip.
Alright, so, it adds those fields, and then I format it.
So, we come here, we add a little Return, and then add a little Return, and then add a little Return.
Now, if there's anyone here watching this who knows anything about Microsoft Word, or maybe you have a friend, or the Admin, who knows something about Microsoft Word, or even knows a Microsoft Word MVP, my question is: What the heck were they thinking, that they forced us, every single time, to come here to Home, open this little drop-down, and say remove the idiotic space before the paragraph?
I mean, when they-- when they-- check the SQream data-- Microsoft, if you're watching-- a million times a day, people have to go in and change that setting.
What-- it's the most insane thing.
Now, there's insane things in Excel, I'll grant you that, but this one, for us people-- non-work people-- coming over to Word, what are you doing here?
This just drives me crazy, but that's my little rant.
Let's go back to mailings, alright?
So, now we've created the first label, and then, the completely un-intuitive thing is, we have to say we're going to Update Labels.
And what that does, is that takes the layout here in the first label, and it replicates it throughout the whole first page.
So, right now, we have a one-page document, 30 labels, and all of this stuff.
Alright, so, Start Mail Merge, choose the type; Select Recipients, Excel; Insert Merge Fields; and then Update Labels.
Once you've done all of that, then you're practically done.
Just come out here to Finish & Merge, and we want to Edit Individual Documents- let me get that so you can see it, Edit Individual Documents.
That's the code word for, "Yeah, just put the labels in Word so I can print them or save them or something like that.
And we want All, click OK, and there is our data from the original data set.
So, here, this one had a Suite, uses four lines; this one didn't have a Suite, it'll leave the blank line there-- that's pretty smart, credit where credit is due, on that one.
So, that is it.
The Mail Merge from Excel to create labels.
Once you get the hang of it-- Start, Select Recipients, Insert Merge Fields.
Update Labels, Finish & Merge-- it's easy.
But, if you don't have those steps, this is one of-- just-- those horrific things.
Well, hey.
Well, I thank everyone for stopping by.
Tomorrow, we'll be back in a product we all know and love, Microsoft Excel, and out of this tiny little add-in here.
Well thank you for stopping by, see you next time for another netcast with MrExcel.
