Barcode-Based Stock Tracking in Excel

Last updated: 27 June 2026

A USB barcode scanner works with Excel out of the box — it acts as a keyboard, so scanning a barcode types the barcode number into the active cell automatically. You do not need special software, macros, or a subscription to start barcode-based inventory tracking. This guide shows you the exact Excel setup: item master, scan-entry sheet, VLOOKUP to fill in the item details, and stock balance formulas.

Key takeaways

Any USB or Bluetooth HID-mode barcode scanner works with Excel without drivers or add-ins — it simply types the scanned value as keyboard input.
Set up a scan-entry form where the scanner populates the barcode column; VLOOKUP fills in the item name, HSN code and GST rate automatically.
Use SUMIFS on the scan log to calculate live stock balances per barcode.
Print Code 128 or Code 39 barcodes directly from Excel using a free barcode font — no label software required.
The free template works in Excel 2010 and later; no macros needed.
For high-volume operations (1,000+ scans per day), Excel has practical limits — consider this a starting point, not a permanent solution.

Fact box. Most affordable USB barcode scanners sold in India (1D laser or 2D CCD type, ₹1,000–₹5,000 range) operate in "HID keyboard emulation" mode by default. In this mode, the scanner sends keystrokes — including a configurable suffix (usually Enter) — directly to whichever application is in focus. No driver installation is needed; it just works like a keyboard.

What type of barcode scanner works with Excel?

Any scanner operating in HID (Human Interface Device) keyboard emulation mode works. This includes:

Scanner type	Works with Excel?	Notes
USB wired (1D laser)	Yes	Most common; lowest cost; plug and play
USB wired (2D imager)	Yes	Reads QR codes and 2D barcodes as well
Bluetooth HID	Yes	Pair as a keyboard; slight latency
Serial/RS-232	No without driver	Needs middleware to route to Excel
Bluetooth SPP	No without driver	Serial profile, not keyboard emulation
Android phone with barcode app	Sometimes	Depends on app — use Bluetooth keyboard emulation apps

When buying, look for "HID keyboard emulation" or "plug and play" in the product description. Almost all budget scanners from local distributors or online marketplaces in India support this.

How do I set up barcode scanning in Excel?

Step 1 — Build the Item Master sheet

Create a sheet named Master with at least these columns:

Barcode	Item Code	Item Name	HSN Code	Unit	GST Rate (%)

Enter one row per SKU. The Barcode column is the lookup key. Format it as Text (right-click → Format Cells → Text) to prevent Excel from truncating long numeric barcodes.

Step 2 — Build the Scan Log sheet

Create a sheet named ScanLog with these columns:

Date	Barcode	Item Name	Item Code	HSN Code	GST Rate	Type	Qty	Rate (₹)	Value (₹)

Date: enter manually or use =TODAY() with a macro trigger (optional).
Barcode: this is the scan-entry column — click this cell, then scan the item.
Item Name, Item Code, HSN, GST Rate: auto-filled by VLOOKUP.
Type: IN or OUT — enter manually or use a Data Validation dropdown.
Qty and Rate: enter manually after scanning.

Step 3 — Wire up VLOOKUP formulas

In the Item Name column (C2), enter:

=IF(B2="","",IFERROR(VLOOKUP(B2, Master[Barcode]:Master[GST Rate], 3, 0), "BARCODE NOT FOUND"))

Adjust the column index for each field:

Column 2 = Item Code
Column 3 = Item Name
Column 4 = HSN Code
Column 5 = Unit
Column 6 = GST Rate

Or use XLOOKUP (Excel 365 / 2019+):

=IF(B2="","",IFERROR(XLOOKUP(B2, Master[Barcode], Master[Item Name], "NOT FOUND"), ""))

Step 4 — Configure your scanner

Most scanners send a carriage return (Enter) after each scan. This moves the cursor to the next row automatically. If your scanner doesn't do this, check the manual for a "suffix" setting or scan a configuration barcode to add Enter as a suffix.

Set the cursor to the Barcode column of the next empty row before each scan session.

Step 5 — Build the Stock Balance sheet

Use SUMIFS on the ScanLog table:

Stock Balance = Opening Qty
  + SUMIFS(ScanLog[Qty], ScanLog[Barcode], [@Barcode], ScanLog[Type], "IN")
  - SUMIFS(ScanLog[Qty], ScanLog[Barcode], [@Barcode], ScanLog[Type], "OUT")

This gives a live balance for each barcode as you scan items in and out.

How do I print barcodes from Excel?

You can generate and print Code 128 or Code 39 barcodes directly in Excel using a free barcode font.

Method — Free barcode font

Download a free Code 39 or Code 128 font (search "free Code 39 font" — widely available). Confirm the download source is legitimate before installing any font.
Install the font on your PC (right-click the .ttf file → Install).
In Excel, create a "Label" column next to your barcode number column.
For Code 39, the formula adds asterisks around the code:
```
="*"&A2&"*"
```
Set the font of the Label column to the Code 39 font. The text renders as a scannable barcode.
Print using a standard label size (30 × 20 mm or Avery labels) — adjust column width and row height to match.

Fact box. Code 39 supports letters, numbers and a few special characters; it is self-checking and widely supported by all scanners. Code 128 is more compact (smaller barcode for the same data) and supports the full ASCII set. For item codes that are purely numeric and short, Code 39 is the simpler choice. For GTIN/EAN-13 barcodes (retail packaging), you need a separate barcode generation tool — Excel fonts do not generate the check digit automatically for EAN-13.

Free barcode inventory template

The free Excel template includes:

Item Master sheet with barcode column and VLOOKUP-ready structure.
Scan Log sheet with all formulas pre-wired.
Stock Balance summary sheet.
Label printing sheet with Code 39 formula (font must be installed separately).
Instructions sheet.

Free to download, no sign-up.

Download the Barcode Inventory Excel Template »

How Ankeshan helps: Ankeshan's Excel add-in accepts scanned barcodes directly into the transactions log and auto-fills HSN codes and GST rates from your item master — saving the manual VLOOKUP step for every scan. It's launching soon; join the waitlist.

Frequently asked questions

Does the barcode scanner need to be connected when Excel is open? Yes — the scanner must be plugged in (USB) or paired (Bluetooth) before you click into the Excel cell. If you unplug and re-plug a USB scanner, click back into Excel before scanning.

Can I scan 2D QR codes into Excel? Yes, if you have a 2D imager scanner (not a 1D laser). The scanned text — whatever is encoded in the QR code — types into the active cell. This is useful if your supplier prints QR codes with product information.

My barcode column shows scientific notation (e.g. 1.23E+12) — how do I fix it? Format the Barcode column as Text before entering any data: select the column → right-click → Format Cells → Text. If data is already entered, clear and re-enter. Alternatively, use a leading apostrophe ('123456789) to force text, but that makes VLOOKUP matching unreliable. Always format as Text first.

Can Excel handle 500 SKUs and 200 scans per day? Yes, easily. Excel starts to slow noticeably above about 50,000 transaction rows with complex SUMIFS formulas. At 200 scans per day, you would hit that limit in roughly 250 working days — so it is fine for one financial year, after which you archive old data.

What if my supplier uses EAN-13 barcodes on packaging? EAN-13 is a 13-digit numeric barcode. Your scanner reads it as a 13-digit number. Format the barcode column as Text (see above) and enter EAN-13 codes in your Item Master. VLOOKUP matches on the scanned number exactly.

Sources

GS1 India — barcode standards and EAN/UPC specifications (gs1india.org).
CBIC GST rate notifications effective 22 September 2025 (56th GST Council).
GSTN HSN reporting requirements (gstn.org.in).

General information, not professional advice. Verify on the official portal for your case. Reviewed by a Chartered Accountant; last updated 27 June 2026.