Hi guys, today I'm going to be showing you how you can print address labels using Microsoft
Excel and Word. We're going to be using the kind of sticky labels you can get from any
stationers or online, and if you haven't bought your labels yet that's fantastic because I'm
going to be giving you some tips on which labels to buy that are guaranteed to work
with Word.
I've actually made 2 videos on this subject - in this video, I'll show you how you can
use Excel to put together a list of addresses, and then use Word to print those addresses
onto your labels.
And I've made another video where I show you how to use Word to print out pretty much whatever
you want to type into your labels.
So if you don't want to use Excel, then check out that video instead.
The methods I'm going to show you will work if you have a Windows computer or if you have
a Mac. I'm going to be demonstrating this on my Windows computer, but you should be
able to follow along with most of this if you have a Mac.
Don't forget if you'd like me to answer your questions about technology you can leave them
in the comments below, and please hit the subscribe button too.
So, let's get started.
I'll give you one piece of advice before we start that can save you some hassle - choose
the right labels.
Labels come in many different sizes, and Word needs to know where to position the text on
the page so that it prints out properly on each of your labels.
To make this easy, most good label brands put a product code on the packaging. You can
select this code when you make your labels in Word, and Word then knows the exact measurements
for your labels.
So let's take a quick look how to check which labels are available.
Open Word, and click on Blank Document. Look on the ribbon menu for Mailings. Click
on Mailings, then click Labels. Now click on Options, and you should be looking
at a box like this. And if you're using a Mac, you don't need
to press Options. And we want to see the brands, so look for
Label Vendors, and then click the drop-down box. This is Label Products on the Mac.
So when you're shopping for labels, make sure you buy one of these brands.
I'll show you what I did - I know Avery is a good brand, so I went to Amazon and searched
for Avery Labels. The first hit was these labels - there are 21 labels on a sheet, which
is a perfect size for address labels. There's a product code on the front of the pack, L7160.
So we can go back to Word, and have a look at the product numbers under Avery. Great,
L7160 is in the list, so these labels will do the job. I ordered these labels, and it
turns out they did a really good job, and if you want to buy them, I've put an Amazon
link in the description below.
I kind of missed the huge notice that these are for Laser printers and I have an inkjet
printer, but it didn't seem to make any difference, they worked perfectly.
So let's see how to put together a list of addresses using Excel, and then I'll show
you how to print off those addresses onto labels using Word.
Open Excel, and click on Blank Workbook.
Now, I recommend you lay out your spreadsheet to look something like what I have here. And
by the way, all the names and addresses in this video are fake, I just got them from
an online generator.
In the first column I've put the name of the person. Then I've got a column for each line
of the address. Basically, for each line you want on the labels, you should have a column
in this spreadsheet.
I've got 5 columns in total, you might have 4 or you might have 6. You'll find some of
your addresses take up more lines than others, so just make sure you have enough columns
for your longest address.
The top row contains the headers for the spreadsheet. I'm going to select this row and make the
text bold, so it stands out as headers.
Once you've got the list how you want it, you need to save it.
Click File, then Save. Now wherever you choose to Save this file
make sure you remember it, because we'll need to find it later on.
I'm going to save it on the Desktop. Give it a name - I'm going to call it Addresses.
And now you can close Excel, and there's our Addresses file, saved on the Desktop.
Now we're going to use Word to set up the labels, and we're going to bring in the addresses
you've just saved.
So open Word, and click on Blank Document. Click on Mailings, then click Start Mail Merge.
And click on Labels. Where it says Label Vendors, select the brand
of labels you'll be using. And then pick the product number of the labels from the list.
If your labels aren't on the list, you could go to New Label and type in measurements,
but I don't recommend doing this if you can avoid it.
Now that you've chosen your labels, click Ok. And there's our sheet of labels.
Now we're going to bring in your addresses.
Click Select Recipients, and use an existing list.
We need to find the Addresses file we made earlier. So I'm going to browse to the Desktop,
and click on Addresses, and click Open.
Now it's found the spreadsheet containing our addresses. It's asking if the First Row
of Data contains column headers. Well if you remember, it does contain the header names
like Name, Address 1, Address 2. So we need to leave this ticked. And click Ok.
And then your screen should look like this - every label should say "Next Record", except
for the first one.
Next, we need to check that our names and addresses are coming through, so we need the
next button along, which is Edit Recipient List. Click this.
There we go, all our names and addresses are working, and we have the right names for the
headers. So click Ok.
Now we can start putting the addresses onto the labels so we want the next set of buttons
along. I find the easiest way is to insert the lines, or fields, one by one. So click
on Insert Merge Field, and there you can see our headers. Let's start with the first one
- click on Name. You see how it puts the Name field onto the first label? We want to repeat
this with the other fields. So we want a new line after Name - but watch what happens if
I press Enter. It's a big jump down. I don't want it that spaced out. So I'm going to backspace,
and this time I'm going to hold Shift while I press Enter. That looks better. So make
sure you press Shift-Enter for each new line.
Now we can go back up to Insert Merge Field, and choose Address 1. Then Shift-Enter. And
just keep selecting these fields, with a shift-enter after each one.
And you should end up with a first label that looks like this. You can change the style
of this if you like, just click on the Home tab, then select all the text in this label,
then make any changes - like making it bigger, or changing the colour. But I'm going to leave
this how it was. When you've got this label looking right, you can go back to the Mailings
tab.
Now we need to put the address fields onto all of the labels. So click this button, Update
Labels. And the fields should appear on all the labels. Don't worry that it says Next
Record, that's normal.
And at this point, we can see how our data looks on the labels - click Preview Results.
Perfect - there's our names and addresses. You will only see one page of addresses - if
you're expecting to see more than this don't worry, it's only a representation of how the
labels will look. You can click on this forward arrow to see how your addresses further on
will look. If you're happy with the layout and everything, it's a good idea to Save this
document now - so click File, Save, choose a location and give it a name - I'm calling
mine My Labels. And click Save.
Now we can print the labels.
It's important that you don't just go to File - Print, because it won't have all of your
addresses. What you need to do is click on Finish and Merge, and then click Print Documents.
When you see this, you need to select All, then click Ok. And when you're ready to print,
click Ok.
Now when I print labels, I like to take all the plain paper out of my printer, and then
just put in a single sheet of labels, even if you're printing off several sheets. Most
printers will tell you to insert another sheet when the first one has printed, so you can
feed in another sheet then, manually.
I know it seems a slow way to do your printing, but labels are expensive and you don't want
to risk the printer chewing up your paper or taking up 2 sheets at once.
Now what do you do the next time you want to print off these labels - say your list
has changed. Well you just need to make your changes to the list in Excel, and then print
off the Word document. You don't need to edit the Word document at all.
I'll show you how to do this now. First, close this document, and yes I want to Save it.
Now we're going to open our Addresses file in Excel. Now make any changes. Say I want
to delete a few of these lines. We now have a much smaller list. Let's save this spreadsheet.
And close Excel.
Now we can open My Labels.
You get a safety message - this is completely fine, so just say Yes.
And because it's pulling in the addresses from our Excel file, straight away it's using
the shorter list we changed it to. This makes it really easy to update your list and print
off some labels.
So hopefully that helped you print off your labels. Please let me know in the comments
below how you got on, and if you found this video helpful please give it the thumbs up.
If you’d like to see more tutorials from me in future, click on my face below, and
hit the red subscribe button.
And don't forget to check out the other video I just made on how to type directly onto labels
using Word.
Thanks for watching and I'll see you next time.