How To Print Barcodes With Excel And Word

Label the first column “Type” and create three records: “Code128”, “Code39”, and “QR Code”, just like you see in this image.

Label the second column “Label” and create three records: “987654321”, “CLEARLY123”, and “Clearly Inventory is easy!”

Label the third column “Barcode” and create three records: “987654321”, “CLEARLY123“, and “Clearly Inventory is easy!” Yes, they look almost the same, but this is the actual information that we’ll be encoding in the barcode itself.

Those asterisks are CRITICAL. Without them, your barcode reader can’t decode the information as “CODE39”.

Click “Save As”.

By default, Windows creates a “My Data Sources” folder in your “My Documents” folder. This is where Word will look for your tables when doing mail merges. You don’t have to use this folder, but it will make this example, and life, easier to follow.

Please use the filename “BarCodeExample.xlsx”. We’ll be referring to it later on.

Open Word and click the “Mailings” tab. If you don’t have this tab, you might need to customize your toolbar to activate it. Google how to do that if necessary. You might notice that Word has a “Barcode” menu item. I did too. And to this day, I can’t get it to work properly. If you know how to, please drop us an email at support@clearlyinventory.com.

Once you’re in the “Mailings” tab, click on “Labels”. This will bring up a Label dialog window for you to select a label type.

Click this area (you may not notice that it’s clickable, but it is, trust me). Then select the Avery US Letter, 5161 Easy Peel Address Labels. That’s what we’ll use for this example, but you can choose any number of labels when you do this on your own.

A lot of people make the mistake of clicking “Print” from this dialog. Instead, click the “New Document” button. Don’t be one of those people who can’t follow directions and gets lost.

Yeah, I forgot to put a “1” on this image, but just bear with me. The image at left is what your New Document will look like. I point this out because, at this point, you will probably have TWO Word documents open: the original one where we selected the label type and this New Document. Make sure you’re working with the document that has the outlines of the labels.

Make sure you click the “Mailings” tab again so you can see the Mail Merge options; otherwise, step 2 won’t make sense.

Click “Select Recipients”. This may seem weird, but what we’re really doing is choosing a table or spreadsheet with values in it. Because the most common use of this feature is mailing lists, Microsoft calls the data “Recipients”. Just keep up, and it will all become clear.

Choose “Use an Existing List…”. Remember the spreadsheet we created and saved at the beginning of this tutorial? It’s about to come back into play.

Remember how I told you to use the default My Data Sources location? This is why. MS Word will automatically open it when you click “Select Recipients”. It’s a real time-saver.

Select “BarCodeExample.xlsx” (you did follow my instructions earlier and use this name, right?).

Yes, the image got clipped, but I didn’t have time to fix it. So go complain to your coworker about it. Any who, click the “Open” button, even though we’re not going to actually open the spreadsheet.

Depending on your default Excel settings, you might have more than one worksheet. I only have one. But if you have multiple worksheets in your Excel document, you will be asked to select the table. Just make sure to select “Sheet1$” like the arrow says.

Then click “OK”. Why doesn’t Word spell it out as “Okay”? I don’t know, okay? OK.

Click on “Insert Merge Field”. Merge Fields are specially encoded fields that will pull data from data sources. Here, the Merge Fields will be showing information from our BarCodeExample table.

Hey, isn’t it interesting that the three options exactly match our table column headings? Not really. If you’ve been following directions, that’s what’s supposed to happen. But kind cool. Let’s select “Type” first.

Congratulations! You’ve inserted a MERGEFIELD. What’s going on here?

Everything between those curly brackets is code that tells Word “Go to that table, get the first value in the column titled “Type”, and put it here. It’s important to note that the stuff inside the curly brackets will not print in the final document “as is,” so it may appear that the label won’t fit the content. But as long as the data fits, everything is ok. Another important point: You can’t just create a MERGEFIELD by typing curly brackets and putting stuff in the middle. So don’t even think about it. Do it the way I’m showing you in this exercise.

Same deal as above, only now we’re putting the “Label” in. This can be useful when you want a “human readable” section of the label. That’s why we created this column. We could have just used the barcode column, but sometimes barcodes need to have special characters that people don’t need to see, so it’s good practice to keep them separated.

IMPORTANT: If your page doesn’t look like this one you probably see words inside of << >> like <>. Here’s what to do. Move your mouse to put your cursor inside of the << >> and right click to bring up Word’s contextual menu. Then click the menu item called “Toggle Field Codes”. That should reveal the codes you see on our example. If it doesn’t, well, I’m not sure what to tell you.

Your label should now look like this, with two merge fields. If it doesn’t, go call your mom. She misses you.

Repeat the above steps for the Barcode field. You’ll notice that they are all together. This will be hard to read, so format your label a little. See the next image and steps.

Put a colon and space between the Type and Label fields, then add a hard “Return” or “Enter” to put the Barcode merge field on another line. When you’ve done that, it should look like the image to the left (except for the orange arrow and number).

I’m ashamed to say it, but this part took me over two hours of research to figure out. For some reason, Microsoft doesn’t want you to know about it. We’re going to need to change the word “FIELD” to “BARCODE”. First, highlight the word “FIELD” exactly as you see here.

Now, type the word “BARCODE” instead. THIS is the secret sauce that makes it all possible.

Now you need to tell the MERGEBARCODE field what kind of barcode font to use. So you need to type “CODE128” here to tell Word that “When you get the data in the Barcode column and present it here, make sure the font is CODE128.” We could just as easily tell it to make the font “CODE39” or “QR”, and we will.

Now we have to tell Word to print another label, so make sure your cursor is in the top left of the next label area, as shown, and click on the “Rules” menu option.

This is where the “Rules” menu option is. Please click it.

Now click “Next Record”.

You’ll now see the “NEXT” merge field. This tells Word to go to the same Excel table and use the next row of values when pulling data from the columns. If this field is not present, Word will just repeat data from the same row again and again.

Now, select all of the merge fields from the first label, as you see here, and copy them.

Paste the fields here after the “NEXT” merge field.

Now copy this…

into the third label.

Now, let’s tell Word to use a different type of barcode font. In this case, we’ll replace the “128” with a “39” for “CODE39”.

Make sure it now looks like this.

Do the same for the last label. Select “CODE128” and…

…type “QR”. Do not type the word “CODE” before “QR”; just type “QR” like I said to.

Click “Finish & Merge”. We are almost there now.

Select “Edit Individual Documents”. Do not select “Print Documents”. This will create a new completed document that you can edit.

Make sure the “All” option is selected and click “OK”.

Hold your breath.

Take a breath and enjoy the awesome sauce. You have just created barcodes of many varieties using only Microsoft Word and Excel.

You will find that it’s difficult to move the barcode. This is because Microsoft defaults to the least usable image format. We’ll need to change that. Click the barcode.

Then click this little doohickey (I don’t know what it’s really called).

Chose this option: “Square”.

Now you can easily move the barcode…

Do the same with the other barcodes just to practice

And we’re ready to print these bad boys. Click “File”.

Then click “Print” and follow the print dialogs. You don’t have to have label stock in your printer; just regular paper is fine for this test.