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.
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:
Barcodes like the UPC-A, EAN , and ISBN are obtained this way. You use these ciphers to sell products at major retail stores like Walmart , Target , 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.
Print Custom Barcode Labels on Excel with Sheet Labels from enKo Products!
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.
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:
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:
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.
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.”
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.
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.
Get 10% OFF on Your Barcode Labels!
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.
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.
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.
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:
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.
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.
Save up to 47% OFF on Zebra Barcode Labels!
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.
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.
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.
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 .
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 “” 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 “” 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 “” or “”.
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 “” should appear. The label should now have the lines “ ”. If Code 39 is used, “” will instead appear in the second string.
11. Click “Rules” on the “Mailings” menu then select “Next Record.” You will see the programmable string on the next label.
12. Copy all the programmable strings on the first label after the line “” in the second label. You will now have the following string on the second label: “ ” unless Code 39 is used.
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.
