Tracking inventory in Excel with a barcode scanner

The short answer

• The hardware: a USB keyboard-wedge scanner — it types the barcode into the focused cell and presses enter.
• The structure: one scan per row into a log sheet, quantity and context columns beside it; never edit the on-hand sheet directly.
• The habit: reconcile the log to on-hand on a schedule, before drift compounds.
• The ceiling: Excel tracks; it cannot allocate, resolve conflicts or keep history — that is the line where a live record starts.

The setup that works

A keyboard-wedge scanner needs no software — it is a keyboard that types barcodes. Plug it in, click a cell, scan, and the code lands with an enter. The trap is scanning into a "stock on hand" grid directly: every scan becomes an edit, edits overwrite each other, and nothing explains how the number got there. The structure that works instead is a log sheet: one row per scan event — barcode, quantity (typed after the scan), action (received, picked, adjusted, counted), date and initials. A second sheet holds the product list: barcode, product, current on-hand.

The on-hand sheet is then derived, not edited: reconciled from the log on a schedule that matches your volume — daily during busy periods, weekly otherwise. Excel formulas (SUMIFS over the log) or a pivot table do the arithmetic. The scanner makes the log fast enough to actually maintain, which is the entire contribution hardware makes here: the discipline is still yours.

The habits that keep it honest — and the ceiling

Scan at the moment of movement, not in batches at day's end — the log's value is that it happened when it happened. One action per row, even when it feels slow; combined rows cannot be reconciled later. Reconcile on schedule, every time — a log that drifts from on-hand is two records already. Lock the structure: whoever adds a column without announcing it breaks the reconciliation quietly.

The ceiling arrives on schedule too. The first allocation problem — two people promising units the sheet says are there — is not a discipline failure; the sheet has no allocation concept. The second person makes it two logs. The first "where did this unit go?" has no answer beyond the log's recent rows. Those are structural: a spreadsheet tracks; a live record allocates, arbitrates and remembers. That is the graduation moment — what a real inventory system does with every scan is the honest preview.

Where BSimple fits

We build BSimple, so weigh that. It is the live record at the top of this ladder: scanning through any phone browser or a wedged scanner writes movements to shared stock — receiving against purchase orders, picking against orders, stocktakes with variance review, batch and lot tracing — with orders, purchasing and invoicing reading the same record and accounting handed to Xero or MYOB. From $180/month (AUD), and the free trial imports your Excel list to start.

Until the ceiling arrives, the Excel stack above is a legitimate phase — this page will not talk you out of it. When it arrives, the free inventory-and-scanning guide maps the ladder, and the free Excel templates and PDFs cover the export side for teams migrating from paper.