Barcoding systems are nifty tools that boost efficiency. In our previous discussions, we mentioned that standard-setting bodies like GS1 and ISO assigned some common business barcodes. Others, however, could be generated within the establishment that would use them.

SKUs and QR codes are some of the handiest barcodes around because of their many applications. You have several options for making your own. In this article, we explain how you can print barcode labels using the familiar programs Excel and Word, even if you’re working with older versions.

What Are the Different Ways of Generating Barcodes?

Before we discuss how you can print your own barcode labels, we shall first enumerate the ways to obtain them. It is important to remember why some barcodes are licensed while others are not.

Here are the different approaches to getting your own barcodes:

1.

Purchase Barcodes from Licensing Organizations

Barcodes like the UPC-A, EAN, and ISBN are obtained this way. You use these ciphers to sell products at major retail stores like WalmartTarget, and Amazon

You can download UPC fonts to help create your own, but you won’t be able to use them outside your establishment if they’re not registered. Additionally, internally generated UPCs can get you sanctioned and get your selling privileges revoked as they will be considered unregistered or fake barcodes.

2.

Buy Barcodes from Third-Party Vendors

It will take hundreds of dollars to get your first ten UPC-A and EAN barcodes from GS1. Meanwhile, third-party vendors offer a cheap alternative by providing registered barcodes that have long ago been relinquished by their old owners.

Many of these sellers are reliable and do business online. However, you’re risking fraud if you don’t do your due diligence in finding the right agent. You can do that by reading online reviews and asking friends for referrals.

This method is really quick and cheap. Additionally, third-party barcode vendors do not require a lengthy registration process. They can also release the barcode images to you immediately upon payment.

3.

Use a Barcode Generator

SKUs and QR codes are barcodes that do not need to be standardized as long as they are used internally. Barcode-generating apps are most convenient for creating these two ciphers. These programs use Code 39, Code 128, QR code, etc. to make thousands of different barcodes automatically, accurately, and fast.

Besides efficiency, offline barcode generators provide users some privacy, as when a top-secret product or formula needs to be encrypted. Free online software is also available, but the most reliable barcode generators cost a little money.

Learn More:

4.

Create Barcodes without Using a Barcode Generator

You can make your own barcodes without using a barcode generator. You can even do so with Excel and Word, two commonly used Microsoft programs.

Here’s essentially how it works:

  • Barcoding systems rely on combinations of bars, dots, and spaces that stand for letters, numbers, punctuations, and other data types. 
  • For 1D barcodes without a checksum digit, generating them is as easy as converting a character into a barcoding font. Examples are Code 39 and Code 128, which are used for making SKUs.
  • Creating other 1D barcodes is not as straightforward, especially if you would rather avoid the math. They need algorithms to generate the correct checksum digit. Examples of such ciphers are UPCs and EANs. Barcode errors can result from inaccurate checksum digits and may harm your business. You may need to integrate barcode-generating apps with Excel and Word to come up with error-free codes and do so efficiently.
  • For 2D barcodes like the QR code, you need Excel and Word plug-ins to generate the correct ones. These cipher systems rely on error-correcting mechanisms that are more elaborate than the checksum digit.

This method works best for creating organization-specific barcodes like SKUs and QR codes. You can choose this approach if you wish to spend minimally and use up as little time as possible online.

Before you print your own barcodes, you must also remember one key difference between 1D and 2D barcodes. This is discussed in the next section. Keep it in mind because it can help you create a functional barcode system fast.

What Is a Barcode Database and How Should You Make One?

In our blog, What You Need to Know About 2D Barcodes, we mentioned that one huge difference between 1D and 2D barcodes is the need for a barcode database. This arises from the fact that 1D barcodes can only encrypt a limited set of information compared to their 2D counterparts.

The short character strings of 1D barcodes are not meaningful or useful unless assigned to a more detailed data set, such as complete product information. That is why they need a more extensive database where the assignment of such character strings is clear.

For example, this Code 128 barcode only encrypts the string “REDTEE0009XL.”

Code 128 barcode

The barcode clearly refers to a red t-shirt sold at a store. To use it for an SKU system, you need to construct a database that also describes its movement within the store’s warehouse, i. e. how it got there, where it was stored, when it was bought, etc. You will not be able to make the most out of your SKU system without these details.

On the other hand, 2D barcodes can encrypt thousands of characters and non-human-readable information. They can skip the extra step that requires assigning encoded strings. You can use them to simplify a very elaborate SKU system, though they also have other convenient functions.

QR code

A standard-size QR code, for example, can encrypt over 1,800 Japanese characters and about 4,300 western characters and symbols. You can use QR codes to take note of your loyal customers’ purchasing habits, provide easy access to your online content, fashion security tags, etc.

Sheet Labels

: 6
: 100
: 3-1/3″ x 4″
: Deskjet & Laser Printers

How Do You Print Barcode Labels Using Only Excel?

Creating a barcoding system is one thing. Printing labels is another. You can do both using Excel only, with one file containing both the database and labeling system. The process is not automated, but it lets you use even old versions of Excel.

Making 1D Barcode Labels Using Excel

Here, we assume that you will use 1D barcodes for making SKUs.

1. SKUs typically use Code 39 or Code 128. Code 39 generates longer barcodes, so it is less suitable than Code 128 for smaller containers. Whichever symbology you prefer, the first step is to download its barcoding font. Code 39 and Code 128 barcoding fonts are offered for free by several websites. Some charge a fee, but the font may be provided together with barcode-generating software.

2. Create a new Excel file.

3. Assign Sheet 1 as your database and Sheet 2 as your label page

4.On Sheet 1, make three columns. The first one is for the character string you want to encode. The second is for the barcode corresponding to that character string. The third is for detailed information assigned to the character string and its barcode.

a. Format the first column to show text in the non-barcoding font you want to use. Give a title to this column. In this example, we used “Product Code.”

b. Format the second column to show text in the barcoding font you prefer. Give a human-readable title to this column. In the example, we used “Barcode” as the column title and Code 128 as the barcoding language

c. The third column will have the same human-readable font of your choice as it will display details about your product. In our example, we entitled the third column “Product Description.”

d. To make the database presentable and easy to understand, you may use features like autofit, center text, wrap text, etc. You can find them on the top panel under “Cells.”

e. Fill out the first column with your SKU codes. We gave guidelines for making these character strings in our blog, SKU and UPC: What’s the Difference, and Why Does it Matter? You may use Excel formulas to automate product code generation if you know how to maneuver them. Otherwise, you will have to generate sequential product codes manually

f. SKU codes using Code 39 must have an asterisk before and after the main character string. The asterisks mark the start and stop signs for barcode scanners. Code 128 characters do not need these asterisks.

Glass Bottles Bar Code

g. In the second column, copy the text under the first column and convert it to your desired barcoding font. Note that the formula bar will show the text’s human-readable form, but the cell itself will display the barcode.

h. Fill out the third column with the product details

i. Do steps 4e to 4h for your other products.

j. Save your file. 

second excel screenshot

5. Sheet 2 is for creating barcode labels without leaving Excel. You may start by doing the following:

a. Right-click any gray portion of the top menu. This is the part that is also called the “ribbon.” A window will appear with options about what to do with this panel. Choose “Minimize the ribbon.”

b. Click “View” then “Page Layout.” This should make the ruler guides appear while letting you view the final page layout.

c. Measure the margins and dimensions of your sheet labels and compare them with the ruler guides. From here, you will have an idea of what values to give when customizing the page margins, row height, and column width.

d. Different suppliers will have different numbers for the above parameters. At EnKo Products, we offer barcode labels of different sizes. Our 30-per-sheet address labels typically require the following settings:

      • Page margins: 0.20” (0.5 cm) on all sides
      • Number of rows inside each label: 5
      • Height of the cells within the labels: 0.16 – 0.17” (0.40 – 0.43 cm)
      • Number of rows between the labels: 1 
      • Height of the cells between the labels: 0.23 – 0.25” (0.58 – 0.64 cm)
      • Number of columns inside each label: 3
      • Width of the cells within the labels: 0.85″ (2.2 cm)
      • Number of rows between the labels: 1
      • Width of the cells between the labels: 0.12” (0.3 cm)
third excel screenshot

By using these settings, each page must have 59 rows and 11 columns. Each label has three columns, five rows and one empty cell on each side separating it from neighboring labels.

However, note that Excel may automatically adjust these settings. You may also have to repeat your measurements for every fresh sheet to ensure that the text lands in the middle of the label every time. Leave a safe zone of about 0.10 – 0.12″ (0.25 – 0.3 cm) wide on each side of your label to ensure that the text does not go beyond the borders.

  • To center your text vertically, go to the second row of each label. Merge three columns and set the text to appear at the center. Do the same for the third and fourth rows and leave the first and last rows untouched. You can adjust the other labels in the same manner.
  • Copy one of your product codes from Sheet 1 into the first line of your first label. Copy its corresponding barcode on the second line, making sure to use the correct barcoding font. On the third, you may type the price or other vital information that you want to note on your label. Do these steps for your other products.
  • When you’re done with all the labels, you can save your Excel file and print it.

Sheet Labels

: 30
: 110
: 1″ x 2-5/8″
: Deskjet & Laser Printers

Making QR Code Labels Using Excel

The following are the steps in obtaining QR codes and creating their labels using Excel 2013 and later versions.

1. Create a new Excel file.

2. Assign Sheet 1 as your database and Sheet 2 as your label page.

3. On sheet 1, make two columns instead of three — one for your product’s characteristics and another for the QR code.

a. The first column may be given the title “Product Description.” If you’re selling your product online, you may type its URL here instead of the full description.

b. Assign the second column under the heading “QR Code.”

c. On the top menu, click “Insert” and “Get Add-ins.”

d. When the Microsoft Office Store appears, search “QR4Office.” This is a free add-in, but you need to log in to your Microsoft account to get it. Click to install.

e. QR4Office will be installed in your system. After that, the QR4Office dialog box will automatically appear on the right side of the screen. You will be asked to enter the text you want to encode. You can put in a URL, email address, phone number, pre-programmed text message (SMS), geographic location, or any custom text. You can copy the complete product description under “Custom” or enter its URL.

f. Once you’ve generated your QR code on the right-side panel, you can change its color and that of its background. You can also resize it.

g. You can set the error correction level. The standard value is 15%. Higher levels will yield more repetitions of the encoded text inside the image. This can make the QR code too crowded and harder to read. Lower levels will make fewer repetitions and may likewise reduce scanning efficiency.

h. Click “Insert” in the QR4Office dialog box. This will create an image in the middle of the page.

i. Resize the image and transfer it to the second column of your database.

j. Do steps 3e to 3i for the rest of your labels.

k.If your Excel program was created before 2013, online barcode generators can help you make QR code images for your data. You can copy them into your Excel file once they’re made.

4. Creating QR code labels on Excel is similar to making 1D barcode stickers using the same program. Make Sheet 2 your label page. You can adopt the same margins and label dimensions. However, you have to merge different cells, e. g. the third column of each label, to create enough space for the QR code.

5. Save your file. You can also print labels from here.

You may scan your barcodes to make sure that they retrieve the right information. Note that the method described in this section is not the best one for creating Excel labels. However, it will help those who rely on the software’s older versions, which cannot handle mail merge.

How Do You Print Barcode Labels Using Only Word?

Creating a database and barcode labels is easier using Word than Excel. However, expanding the database to accommodate future stock reports will not be as straightforward.

Making 1D Barcode Labels Using Word

The following are the steps for creating SKUs using Word.

1. Download the Code 39 or Code 128 font.

2. Create a new Word File.

3. Make a three-column table similar to that in Sheet 1 for making 1D barcode labels on Excel. Save your file.

4. For creating labels, click “Mailings” at the top panel.

5. Click “Labels” on the left side. The “Envelopes and Labels” dialog box will appear.

Making QR Code Labels Using Word

The following applies to Word versions created from 2013 onwards.

1. Create a new Word file.

2. Make a two-column table similar to the one you made for creating QR codes on Excel. Getting the QR4Office add-in involves pretty much the same steps.

3. After constructing your Word table, click “Mailings” at the top panel to make your labels next.

4. Click “Labels” on the left side to make the “Envelopes and Labels” menu appear.

5. On the menu, click the “Label” panel on the right. Set “Product number” to “30 Per Page” then click “OK.” Click “New Document” when you’re back on the “Envelopes and Labels” window. You now have an editable Word label template.

6. Copy your label information into the template and center the text. Resize the text and images as needed.

7. Save both files. You may print your labels at this point.

For older Word versions, you may generate your QR codes using online barcode generators.

The databases created using Word have to be expanded every time the stocks are updated. You will need to make new columns each time, unlike in a spreadsheet like Excel, where you only have to assign pre-existing columns to new information.

How Do You Print Barcode Labels Combining Excel and Word?

Combining Excel and Word lets you maximize both programs’ features. It makes setting up a database and label template efficient. However, you can only do this if you’re working with a Microsoft version that allows you to use mail merge.

Making 1D Barcode Labels Using Excel, Word, and Mail Merge

The 2013 or later versions of Microsoft can help you create SKUs using Excel, Word, and Mail Merge. Start with the Excel files you saved earlier. Use Code 39 or Code 128 font that you downloaded previously.

1. You may edit your Excel file to make it merge-ready. You may retain the “Product Code” and “Barcode” columns. Since not all the information in the third column will fit, you can insert another column containing only the product characteristic you want to be retained on your label. In our example, we’re inserting the “Price” column between the “Barcode” and “Product Description” columns. Save the file.

2. Create a new Word File.

3. Click “Mailings” at the top column.

4. Click “Labels” on the left side and follow the steps for creating a 30-labels-per-sheet editable file.

5. With the new label file open, click “Select Recipients” then “Use Existing List.”

6. In the next window, find your saved Excel file and open it.

7. Next, you will be asked which sheet you’re using. Select “Sheet1$,” which is your Sheet 1 database, then click “OK.”

8. Click “Insert Merge Field.” You have four choices here, which correspond to the four columns you made on your Excel database. Choose “Product Code.” The programmable line “{MERGEFIELD Product Code}” will appear on your first label. A programmable line is not simply a combination of brackets and letters. Word generates it for you and fills out the information when the merge is complete.

9. Click “Insert Merge Field” again, but this time, choose “Barcode.” The programmable line “{MERGEFIELD Barcode}” will show up. Delete “FIELD” in “MERGEFIELD” and substitute “BARCODE.” After the word “Barcode,” type “CODE128,” if that is what you used. Otherwise, type “CODE39.” The final format of this programmable line should appear as “{MERGEBARCODE Barcode CODE128}” or “{MERGEBARCODE Barcode CODE39}”.

10. For the third information on your label, click “Insert Merge Field” again. Then click “Price” or whatever name you gave for this column. The programmable string “{MERGEFIELD Price}” should appear. The label should now have the lines “{MERGEFIELD Product Code} {MERGEBARCODE Barcode CODE128} {MERGEFIELD Price}”. If Code 39 is used, “{MERGEBARCODE Barcode CODE39}” will instead appear in the second string.

11. Click “Rules” on the “Mailings” menu then select “Next Record.” You will see the programmable string {NEXT} on the next label.

12. Copy all the programmable strings on the first label after the line “{NEXT}” in the second label. You will now have the following string on the second label: “{NEXT} {MERGEFIELD Product Code} {MERGEBARCODE Barcode CODE128} {MERGEFIELD Price}” unless Code 39 is used.

13. Copy and paste “{NEXT} {MERGEFIELD Product Code} {MERGEBARCODE Barcode CODE128} {MERGEFIELD Price}” in the other labels.

14. Click “Finish & Merge” then “Edit Individual Documents.” The latter command will let you edit each label before you save or print.

15. The “Merge to New Document” window will appear. Select “All” then click “OK.”

16. The Word label template should now show the assigned text and barcodes. You may fix the label by realigning the text, resizing the barcode, setting image layout options to “Square,” adding spaces or punctuations, etc.

17. You can save or print your labels at this point.

word

Making QR Code Labels Using Excel, Word and Mail Merge

Here, the 2013 or later Microsoft versions are also necessary. An integrated QR-coding program will let you use merge fields to fill out all information, including the 2D barcode. If not, you will have to use the QR4Office app to generate QR code images and insert them manually in the Word label template. 

(a) Creating QR Code Labels with a Barcode Generator

1.Your Excel file is already merge-ready with only the “Product Code” and “Barcode” columns in it. Under “Product Code,” retain only the URL. Under “Barcode,” retype the URL without converting it into a barcoding font. Save.

2. Do steps 2 to 17 above for mail-merging 1D barcode labels. However, type in descriptive or introductory phrases like “Try our product here:” before {MERGEFIELD Product Code}” in the first label or “{NEXT}” in the other labels. Do not enclose the phrase with brackets. 

3. Instead of “{MERGEBARCODE Barcode CODE128}” use the string “{MERGEBARCODE Barcode QR}.” 

4. Once you’ve finished the data and chosen “Edit Individual Documents,” the labels will show the introductory phrase, the product’s URL, and the URL’s QR code. Adjust the text and images.

5. Save or print.

MERGEBARCODE 2

(b) Creating QR Code Labels without a Barcode Generator

1. Start with your two-column Excel file. Under “Product Code,” type the product’s URL. Under “Barcode,” generate the QR code using QR4Office or an online QR code generator.

2. You can only use the mail merge feature on the product code/URL column. Click “Insert Merge Field” then “Product Code.”

3. Once “{MERGEFIELD Product Code}” appears on your label, type your introductory phrase before it.

4. Create your next label by clicking “Mailings,” “Rules” then “Next Record.” When the line “{NEXT}” appears on your next label, copy your introductory phrase before it and “{MERGEFIELD Product Code}” after. Do the same for the rest of the labels.

5. Click “Finish & Merge” then “Edit Individual Documents.” Under “Merge to New Document,” choose “All” and click “OK.”

6. Each label will show your introductory phrase and a different URL. From here, you will have to copy the QR codes individually. Make sure that the 2D barcodes can be moved where you want them by setting the image layout options to “Square.” Adjust accordingly.

7. Save or print.

Conclusion

You can generate your own barcodes inexpensively using the popular Microsoft programs Excel and Word. Here, we presented the different ways for you to do just that. We even showed methods for creating barcodes using Excel exclusively, Word exclusively and a combination of both using mail merge.

However, the barcodes created in this manner are only those that one can use within his own facility. Examples are SKUs and non-standardized QR codes. They are not for making barcodes that should be registered with licensing organizations like GS1 and ISO. If you want cheaper alternatives to standardized barcodes like UPCs and EANs, you may check with reliable third-party vendors.

Leave a Reply