In this post, you can learn how to quickly generate single/multiple barcodes in Google Sheets, which follows the Code39 standard.

In this standard, we can encode numbers, upper case letters, and some special characters.

What are those special characters?

They are asterisk, space, dollar, percent, forward slash, period, plus, and minus symbols.

Among these special characters, the asterisk is used for start/stop delimiters.

You may want to create/generate and use barcodes or bar codes because you know the benefit of using them.

It’s a visual representation of data and machine-readable. Since it’s machine-readable, it can avoid human errors and can save time.

Let’s leave that benefit part and see how to create barcodes in Google Sheets.

Steps to Generate Barcodes in Google Sheets

I am going to generate barcodes in Google Sheets for some landscaping materials.

We can categorize the settings to generate barcodes in Google Sheets under “Basic” and “Formatting.”

Basic Settings (Sheet1):

1. Enter item description in A2:A.

2. Enter item codes in B2:B. When creating item codes, use only letters A-Z, numbers 0-9, and the seven special characters (except the asterisk) mentioned above.

3. In cell C2, insert the following formula.

=ArrayFormula(if(B2:B="",,"*"&B2:B&"*"))

Why should we use the * (asterisk) symbol?

As an example, let’s take the aggregate material in cell A2, which is “Gravel 20-40 (Beige)”. Its code is in cell B2, which is “GB-20-40”.

Within the formula, we are converting the code to “*GB-20-40*”. The asterisks are the start/stop characters and which are meant to tell a bar code scanner when the code begins and ends as they will match at both ends.

Formatting (Sheet1):

Here we will modify the font and font size. It’s the important part when creating a barcode in Google Sheets.

1. Select C2:C and locate and click the “Font” drop-down in the toolbar.

2. Select the font “Libre Barcode 39″ or Libre Barcode 39 Text”. If not available, click “More fonts” and search.

3. Locate and click the “Font Size” drop-down in the toolbar and change the size to preferably 39.

This way, we can quickly create barcodes in Google Sheets.

Repeat Barcodes N Times in Google Sheets

I want to print each generated barcode a random number of times. For example, print/repeat the barcodes for Gravel 20-40 (Beige) 5 times, Gravel 5-40 (Beige) 10 times, and so on.

I’ll guide you on to do that with another array formula and a column with the number of times to print.

Steps to Repeat Barcodes in Google Sheets:

1. In cell range Sheet1!D2:D, enter the number of times you want to print the barcode label.

2. Add a new tab (Sheet2) by clicking the + (Add Sheet) button on the left bottom corner of your tab bar.

3. In cell B1, in that new sheet (Sheet2), insert the following array formula, which would return the item descriptions as the field labels.

=transpose(filter(Sheet1!A2:A6,Sheet1!A2:A6<>""))

It first filters out blank rows and then transposes the description from vertical to horizontal.

4. In cell B2, insert the below formula. It will print/repeat the barcodes n times against each item.

=ArrayFormula(
     transpose(
        split(
           rept(
              filter(Sheet1!C2:C6&"♡",Sheet1!C2:C6<>""),
              filter(Sheet1!D2:D6,Sheet1!C2:C6<>"")
           ),"♡"
        )
     )
)

The REPT formula repeats the generated barcodes n times, and the SPLIT splits them. The “♡” character (delimiter) is used to separate the repeated item.

5. Select B2:F and format the formula result (font to Libre Barcode 39 Text and font size to 36)

6. Print this sheet after proper spacing.

Can We Lookup a Decoded Code?

Yes! Assume I have a decoded code 39 barcode in a cell. I can Lookup it to return the description or price whatever detail I have using Vlookup as below.

Actually, on my table, I don’t have enough information to Lookup.

From the decoded item code, we can only Lookup and return the item description.

I’ll walk you through that. I hope you can follow that to return an entire row after Lookup.

I have decoded the barcode generated in Google Sheets in cell A1 in Sheet3. It’s the value “GB-05-40.”

The table to Lookup is in Sheet1!A1:B, and the lookup column is column 2, which contains the “Item Code.” So we want to perform a reverse Vlookup, which is so simple to perform in Google Sheets.

In Sheet3!B1, insert the following Vlookup formula.

=vlookup(A1,{Sheet1!B2:B,Sheet1!A2:A},2,0)

If you have a list of barcodes decoded in column A in Sheet3, use a Vlookup array formula as below in cell B1.

=ArrayFormula(IFNA(vlookup(A1:A,{Sheet1!B2:B,Sheet1!A2:A},2,0)))

That’s all about how to generate/create barcodes in Google Sheets.

Thanks for the stay. Enjoy!