Photography | Kayaking | Paddleboarding | Electric Unicycling | Tipis | Programming | Databases | Pharmacy | Informatics | Robotics
www.jonathantolhurst.com

Creating a Simple EAN13 Barcode Labelling Spreadsheet Using Excel

Disclaimer: The information contained on this page forms part of my GPC CPD portfolio and is therefore my personal opinion and in no way reflects the opinion or policy of my employer.

Introduction

In addition to expanding my knowledge of barcode systems, the aim of this learning exercise was to create a very simple spreadsheet application that would contain a list of EAN13 codes, which could then be printed on to labels so that they could be attached to product packs that were not supplied with a barcode label. This allowed these additional products to be scanned into an automated product storage and retrieval system.

Requirements

  • Excel 2007 or above
  • EAN13 barcode font (ean13.ttf) installed on each PC that you wish to open the spreadsheet.
  • Label printer
  • This project uses code and font provided in the “barcode fonts and encoders” distribution available on Sourceforge.

Instructions for use

  1. Download the Barcode-Creator.zip file here and then unzip the file
  2. Go to Start>>Control Panel>>Fonts
  3. Click File>>Install New Fonts…
  4. Navigate to the location that you unzipped the parent “Barcode-Creator” folder and select the ean13.ttf
  5. Open the Excel Spreadsheet
  6. A security warning bar will appear – Click: Options>>Enable this Content>>OK
  7. The Barcode Creator tool is made up of two sheets “BarcodeCreator” and List.
  8. To add a product and barcode to the system, navigate to the “List” Spreadsheet.
  9. Enter the product description in column A and then the first 12 digits of the chosen EAN code in column B. (The last digit is the check digit and is calculated – once you know what it is you can add it to column C).
  10. Once you have added the entry in the “List” spreadsheet, make sure you save your changes.
  11. Go back to the “BarcodeCreator” sheet and then select the product from the dropdown list.
  12. On selection, four barcodes will appear to the right (I required 4 identical barcodes to be printed on one  760mm x 380mm label, although you can modify to suit your own purposes by adjusting the print area).
  13. Click on the “Print” button.
  14. The print area will be shown in print preview. You may need to modify the paper size and orientation settings to suit your requirements.
And that’s it! Very simple printing of barcode labels using a Macro-Enabled Excel Spreadsheet.

Screenshots

The "List" Sheet where the product details and EAN13 code are entered.

The "BarcodeCreator" Spreadsheet where the product is selected and the barcode is displayed

Barcode Label Print Preview

Review of Background Knowledge

Barcode Quiz

I have created this set of questions on as a learning exercise in order to help me fulfill my CPD requirements.
Start
Congratulations - you have completed Barcode Quiz. You scored %%SCORE%% out of %%TOTAL%%. Your performance has been rated as %%RATING%%
Your answers are highlighted below.
Return
Shaded items are complete.
12345
End
Return
Jonathan Tolhurst

About Jonathan Tolhurst

Based in London, my interests include photography, kayaking, paddle boarding, electric unicycling, teepees, robotics, programming, databases, informatics and pharmacy.

Contact me

Email: Contact Me
Jonathan Tolhurst