Clearly Inventory

The Online Inventory Management App

Phone: 800-300-0160    Contact Us

Clearly Inventory
search x
Try it for Free! Sign In

The Online Inventory Management App

Phone: 800-300-0160   Contact Us

Clearly Inventory

How to Print Barcodes With Excel and Word

The Best-Kept Secret in Microsoft Office

Before you spend money on special fonts or software, you should take a look at one of the best hidden native features of Microsoft Office—creating and printing barcodes. You can print several different kinds of codes from within Word. 1D codes like CODE128, CODE39, UPC A and UPC E, and EAN are available, and the big daddy of 2D barcodes, the QR code, is also included. This guide will show you exactly how to take advantage of these features.

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40

Create a table in Excel, exactly like the one below…

Make a three column table in Excel that we will use to create barcodes.  The three columns are titled 'Type' 'Label' and 'Barcode'

…By following these steps:

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

Alternate Text Label the second column "Label" and create three records: "987654321", "CLEARLY123", and "Clearly Inventory is easy!"

Alternate Text 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.

Alternate Text Those asterisks are CRITICAL. Without them, your barcode reader can't decode the information as "CODE39".

Click "Save As"…

How to save the barcode table

…by following these steps:

Alternate Text Click "Save As".

Alternate Text There is no step 2.

Save the file in this exact location…

The file location should be \MyDocuments\MyDataSources\ and the filename should be BarCodeExamples.xlsx

…by following these steps:

Alternate Text 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.

Alternate Text Please use the filename "BarCodeExample.xlsx". We'll be referring to it later on.

Open a new Word document and select "Mailings"…

Open a new word document and click the 'Mailings' tab

…By following these steps:

Alternate Text 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.

Click "Labels"…

Select 'Labels' from the Mailings Menu

…By following these steps:

Alternate Text 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.

Select these exact labels…

Select the Avery 5161 Easy Peel Labels

…By following these steps:

Alternate Text 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.

Click the "New Document" button…

Click the 'New Document' button

…By following these steps:

Alternate Text 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.

And your New Document should look like this…

Make sure your new document has the outline of the labels

...and you don't have to do anything:

Alternate Text 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 to click the "Mailings" tab in your new doc…

If the 'Mailings' tab isn't selected, make sure it is.

…by following these steps:

Alternate Text Make sure you click the "Mailings" tab again so you can see the Mail Merge options; otherwise, step 2 won't make sense.

Alternate Text 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.

Select your "recipients" (hint: they're really your barcodes)…

Click 'Select Recipients' and 'Use an Existing List…'

…by following this step:

Alternate Text 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.

Choose the file we saved earlier…

Click 'Select Recipients' and 'Use an Existing List…'

…by following this step:

Alternate Text 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.

Alternate Text Select "BarCodeExample.xlsx" (you did follow my instructions earlier and use this name, right?).

Alternate Text 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.

Choose the file we saved earlier…

Make sure 'Sheet 1' is selected and click 'OK'

…by following this step:

Alternate Text 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.

Alternate Text Then click "OK". Why doesn't Word spell it out as "Okay"? I don't know, okay? OK.

Insert a "Merge Field" called "Type"…

Click 'Insert Merge Field' and then click 'Type'

…by following this step:

Alternate Text 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.

Alternate Text 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.

Make sure the merge field is in the first label…

Make sure the mergefield is in the first label.

…by following this step:

Alternate Text 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.

Insert a second "Merge Field" called "Label"…

Click 'Insert Merge Field' and then click 'Label'

…by following this step:

Alternate Text 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.

Make sure the "Label" field looks like this…

Make sure the mergefield for 'Label' is right next to the 'Type' mergefield

…by following this step:

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

Finally, insert the "Barcode" merge field…

Do the same thing for 'Barcode'

…by following this step:

Alternate Text 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.

Format the label a little…

Add some separator characters and put the barcode on a new line within the label

…by following this step:

Alternate Text 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).

This is the most important part of this tutorial…

You MUST change the word 'Mergefield' to 'Mergebarcode' for the barcode field, or this will never work

…by following this step:

Alternate Text 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.

Change the highlighted “FIELD” to “BARCODE”…

The 'MERGEFIELD' should now appear as 'MERGEBARCODE'

…by following this step:

Alternate Text Now, type the word "BARCODE" instead. THIS is the secret sauce that makes it all possible.

Add the type of barcode here…

Enter 'CODE128' after the field type. This tells Word to use the CODE128 barcode font.

…by following this step:

Alternate Text 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.

This is how you add the other barcode labels…

Insert a 'Next Record' mergefield in the second label

…by following this step:

Alternate Text 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.

Alternate Text This is where the "Rules" menu option is. Please click it.

Alternate Text Now click "Next Record".

Make sure you precede the other labels with this code…

Make sure the 'Next' mergefield comes before the next barcode mergefields.

…by following this step:

Alternate Text 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.

And paste...

Copy mergefields to the second label after the 'next' mergefield.

…by following this step:

Alternate Text Paste the fields here after the "NEXT" merge field.

Change the highlighted “FIELD” to “BARCODE”…

The 'MERGEFIELD' should now appear as 'MERGEBARCODE'

…by following this step:

Alternate Text Now, type the word "BARCODE" instead. THIS is the secret sauce that makes it all possible.

Rinse and repeat…

Repeat the process again for the third barcode label

…by following this step:

Alternate Text Now copy this…

Alternate Text into the third label.

Don't forget to change the barcode type…

Select CODE129

…by following this step:

Alternate Text 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".

To this…

Change CODE129 to CODE39 for this barcode label.

…by following this step:

Alternate Text Make sure it now looks like this.

And don't forget to change this barcode…

Change CODE129 to QR

…by following this step:

Alternate Text Do the same for the last label. Select "CODE128" and…

To this…

Make sure this is changed to QR

…by following this step:

Alternate Text …type "QR". Do not type the word "CODE" before "QR"; just type "QR" like I said to.

Now it's time to put it all together…

Click 'Finish and Merge' and 'Edit Individual Documents'

…by following this step:

Alternate Text Click "Finish & Merge". We are almost there now.

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

Make sure "All" is selected and click "OK"…

Select 'All' and click 'OK'

…by following this step:

Alternate Text Make sure the "All" option is selected and click "OK".

Alternate Text Hold your breath.

BAM! Call your Mom and let her know what you just did…

Your barcode should now appear like this with a CODE128 in the first label, a CODE39 in the second label, and a QR code in the third label.

…by following this step:

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

Let's arrange the codes a little better…

Click the 'image format' icon.

…by following this step:

Alternate Text 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.

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

Select this option…

CChoose 'Square' as the type. This allows you to move the barcode position.

…by following this step:

Alternate Text Chose this option: "Square".

Now you can easily move the barcode…

Move the barcode over to the right side of the label.

…by following this step:

Alternate Text Now you can move your barcode around like a boss.

Arrange the other barcodes…

Move the other barcodes over to the right side as well.

…by following this step:

Alternate Text Do the same with the other barcodes just to practice.

Click "File"…

Click 'File' to print your barcodes.

…by following this step:

Alternate Text And we're ready to print these bad boys. Click "File".

Now it's time to copy and paste, just like first grade…

Select all of the mergefields from the first barcode label.

…by following this step:

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

And print…

Click 'Print' and follow the prompts. Your Barcodes should now print.

…by following this step:

Alternate Text 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.

The above method is a great way to print lots of barcode labels without spending a lot of money or using new software or hardware. It's also helpful when you're doing a large batch of barcodes. You will find lots of software and hardware that is more "barcode friendly," but those will all cost money, and some can be really expensive when using a database. For example, the Zebra GC420 line of thermal printers comes with some great barcode software for free, but if you want to hook it up to a table like we did here, it's going to cost you around $400 per computer. 

Now that you know the basics, you can start to play around with different ideas. There are even more controls available for the barcodes. For a complete description, go to this website from Microsoft that explains how to write MERGEBARCODE fields and options.

keyboard_arrow_up