A Guide to Generate & Use Barcode Using Google Sheets or Excel [+4 Tools]

Want to track your inventory efficiently and in an error-free way? Wish to automate your business’s procurement system? You need barcodes, and the best way to create or manage them are Excel barcodes and Google Sheets barcodes.

It has been more than 50 years since the patent grant of barcodes in favor of Norman Joseph Woodland and Bernard Silver. Still, barcodes are indispensable when you need an easy and efficient way to read concise data written on consumer products or business assets. In fact, technology businesses are developing new versions of it by referencing the basic ones.

These days, businesses automate barcode making, on-demand printing, and decoding using robust apps like Excel and Sheets. Please continue reading to find more on barcodes, their usage, and more.       

What Is a Barcode?

A barcode, consisting of bars and spaces, is a machine-readable representation of numerals and characters.

By using barcodes, machines can read to identify the tagged product from a connected inventory or retail sales database. These images contain black lines and white spaces. The lines could be of different widths and shapes depending on the barcode version, standard, symbology, etc.

The black lines are usually parallel with pre-programmed white spaces between them. Such placement of black and white colors could resemble the concept behind Morse Codes. The fact is, Morse Codes were the basis for the invention of barcode technology.

Barcodes usually contain digits and alphabets. The length of the data depends on its convention. For example, the Industrial 2 of 5 barcode systems can have 2 to 30 characters.      

How Does Barcode Work?

The working principle behind barcodes is the shape and width of the black parallel lines. Different width of black bars generally represents the numerics 1 and 0. Now, to portray the numerics between 0 and 9, black bars need to be sequenced in a special manner.

The pattern of the black bars and their corresponding data depends on the convention of the barcode system.

The followings are the popular barcode standards:

• Universal Product Code (UPC)
• European Article Number (EAN) used in Europe
• Code 39 is exclusively used by the US Department of Defense
• Code 128 is exclusive in the B2B industry
• PDF417 has been specially designed for ID cards  

This sequencing of black bars is a pre-planned algorithm saved on a retail purchase system, inventory tracking software, etc. Now, all you need to do is scan the barcode.

For scanning, specialized equipment is fixed with laser light, usually red and green. Inside the scanner is an optical to a digital signal converter that reads incoming laser. 

When you flash the laser on a barcode, the signal converter scans a reflected part of the laser. Then, a connected computer understands the pattern of the black lines and white spaces and decyphers the embedded data.     

Apps for Barcodes: Google Sheets

Google Sheets is a completely free web app that lets you create barcodes in a flash. If you own a small store, online marketplace, or freelancing work and need a cheap way of inventory tracking, the Google Sheets barcode system is sufficient for you.

Google Sheets come with the related formulas, fonts, and printing properties to produce mass barcodes.  

Apps for Barcodes: Microsoft Excel

Let’s consider your business uses the Microsoft 365 suite for all sorts of documentation and content management, and you do not want to switch to a different workspace. Then, you can conveniently use Excel to generate and print any number of barcodes you want.

Excel consists of various barcode fonts, printing properties, and barcoding fonts for the swift creation of barcodes for any need. 

Excel Barcodes

Excel Barcodes are similar to any other barcodes that businesses use. But, in this case, you convert the target data to a barcode format using fons, formulas, etc., from the Excel app. Many businesses make robust barcoding systems with Excel. 

Such a system often includes the followings: 

• a database of products and tracking numbers
• a VBA code to automate the barcode generation
• pre-formatted printing properties
• matching data to the database after barcode scanning 

Use Cases of Excel Barcodes

Inventory Monitoring

Warehouses of any business generate and paste barcode stickers on inventory items. When new things come from the manufacturing plant or stored items go to distribution points, warehouse keepers scan the barcodes. 

Asset Monitoring

Companies create barcodes and tag their assets like desktops, laptops, TV screens, tables, chairs, etc. When the organization needs to audit its asset inventory, it can just scan the barcode stickers and match the data with the existing stock list.  

Invoice Processing

Invoices come with pre-printed barcodes. When the accounts receivables or payables team needs to process the invoice and record it in a ledger, they can scan the barcode. 

Retail Channel

In grocery stores, shop owners use barcodes to create invoices quickly, track inventory, and prevent theft of products. 

Mail Tracking

Perhaps, you can find the most crucial usage of barcodes in the mailing system of all the countries around the world. At every checkpoint of a mail article movement, postpersons scan the barcode. Thereby, you get article tracking information. 

Mail Merge

Businesses often use barcodes in a mail merge to send bulk product information emails to vendors or clients. 

Hospital RCM

The hospital revenue cycle management system generates a barcode when you admit a patient. The hospital staff then gives a wristband with the barcode to the patient. 

Now, throughout the patient’s lifecycle within the hospital, staff would scan the barcode for check-ups, pathological tests, OT procedures, etc. The RCM will then automatically manage the ledger of the patient.   

Web Page Hyperlinking

Advanced barcodes like 2D QR codes are popular for linking mobile apps, websites, content portals, etc., through URL hyperlinking. 

Benefits of Using Excel for Barcode

Cost Savings

If you can create a system of barcode creation and automate that in Excel, you can save a lot on a separate barcode tool.

Increased Accuracy

When carefully created and tagged, Excel barcodes are highly accurate for tracking and managing physical articles.

No or Less Learning Curve

Since your staff knows how to operate Excel, the Excel barcode production system will be familiar to the team. You do not need to train the employees separately on a new app.

Instant Availability of Excel Data

When you use an Excel barcode system, scanned data instantly becomes available in your Excel file. Employees do not need to invest time in importing scanned data to Excel.

Integration

Most barcode generation tools and systems for Excel are also compatible with other Microsoft apps like Word, PowerPoint, Publisher, etc.     

How to Create Bulk Barcodes in Excel

Download and Install Barcode Fonts

The latest versions of Excel should contain a few barcode fonts. However, if there are no such fonts, you can download one from Dafont. Feel free to use any other website you like. You may want to purchase a barcode font with business licensing for commercial purposes.

Once downloaded, follow these steps: 

• Unzip the file and double-click on the BarcodeFont.ttff file.
• Click Install to add the font to your operating system.  

Creating A Barcode Generating System on Excel

#1. Open Excel and create two column headers: Product ID and Barcode.

#2. Under the Barcode header, copy the following formula:

"*"&A2&"*"

#3. Now, click the cell that contains the formula and then select the BarcodeFont from Home > Font section > Font selection drop-down list.

#4. Copy this cell (B2) and paste it below up to 10 to 20 rows per your requirement.

#5. Then, enter any characters from the English alphabet and numbers to get a barcode on the right-hand side of that cell up to the cell count where you have copy-pasted the formula.

#6. Increase the font size of the Barcode column to get printable barcodes.     

So far, you have learned about barcodes and the manual creation of Excel barcodes.

If you want to make your inventory tracking system flawless and scalable, you may consider the following tools and apps for barcode generation: 

TBarCode Office: TEC-IT

TBarCode Office is a modern add-in for Word and Excel apps of Microsoft. You can download the add-in for free. Once downloaded, install the add-in to Excel and Word for barcoding tasks. 

In Word, it will help you generate barcodes for mailing and mail merges. You can also print out barcodes in Word and paste them as stickers on products. In Excel, the same add-in lets you generate barcodes in bulk and print.

The add-in is suitable for Microsoft Office 2007 and later versions. You can use the add-in on both Windows 32-bit and Windows 64-bit computers. However, the add-in is not compatible with non-Windows desktops or laptops.

If you need to automate the Excel barcode system using VBA coding, you may download the TBarCode SDK from the same portal. The SDK also supports programming automation with Microsoft Access.       

ActiveBarcode: Add-In for Excel

Yet another popular barcode add-in for Excel is ActiveBarcode. Once you successfully add the tool to your Excel software, it adds a dedicated ribbon menu on your Excel app. You get all the options to create, edit, automation, print, etc., on that ribbon menu.

ActiveBarcode is good for backward compatibility for Microsoft Office. You can install the add-in to Microsoft Office versions as old as 2000. All the latest Microsoft versions are also supported. Also, you will need a 32-bit or 64-bit Windows PC. The add-in is not compatible with macOS computers.

Key features of this add-in are: 

• Customized Excel barcode positioning on your data tables
• Changing the size of the barcode precisely by an inch or centimeter 

The barcoding add-in easily integrates with various types of Excel tables. You can also automate the barcode production by linking it to cells. When the cell data changes, the barcode changes as well.       

Barcode Software: ConnectCode

Barcode Software is a standalone app for Windows 10 and 11 computers. Microsoft Store hosts the app for free downloading. The app runs as any other Windows app that you use regularly. But, the principal difference is Barcode Software app will send the barcode data to an Excel file.

Hence, you will need Excel software to use this app. Alternatively, you can export the outputs to apps like Adobe PDF, Word, Access, and other graphic design tools. The barcoding app comes with 3 built-in barcode fonts. These are: 

• Industrial 2 of 5
• Code 39
• POSTNET

Therefore, you can only create barcodes of the above 3 standards. You can not create barcodes in other formats like UPC or EAN. Hence, this is a good app if your business supports the above three barcode standards.        

Barcode Generator in Excel: Tech Nitya

Tech Nitya’s Barcode Generator in Excel is an add-in you need to install in your Excel software. Once set up correctly, you can generate barcodes in bulk with a single click. The tool comes in different types of commercial licenses. 

Therefore, if you want a licensed tool for unlimited commercial usage, you may look at this app. Key tool highlights are:

• User-friendly barcode designer interface.
• Customize the actual barcode any way you want.
• Comes with standard encoding for many barcode conventions like EAN13, UPCA, Interleaved2of5, ISBN, etc.
• It also offers barcodes formatted for postal services like SingaporePost, SwissPostParcel, USPS OneCode, Postnet, Australia Post, etc. 

The tool has been compatible with any version of Microsoft Office from 2007 onwards. Moreover, you can export your barcodes to the Microsoft Word app.    

Final Word

Barcodes are truly handy for managing inventory of products, assets, stock control, etc. Sometimes, tech-savvy people find it a way of data communication in codes that simple eyes can not understand.

Whether you use barcodes for business use or as a tech hobby, you need software and tools to automate the generation of these machine-readable codes. You can use Excel barcodes or Google Sheets barcodes by creating them manually. 

Suppose you do not want any commercial apps. But, if you are looking for faster editing and production of barcodes for Excel or other compatible spreadsheet apps, you can try any of the above third-party apps.

Since you liked this article, you may find Excel Add-Ins for SMBs and Google Sheets add-ons interesting.