How to Run a Stocktake with a Barcode Scanner and Excel
Written by Tomasz Lichosik
Ready to generate barcodes?
Create high-quality labels in seconds.
Plenty of guides tell you how to build an inventory system. Far fewer tell you what to do on the morning you actually have to count everything — with a borrowed scanner, a spreadsheet, and a warehouse that has to reopen at noon.
That gap matters, because a stocktake is where a barcode setup either proves itself or quietly falls apart. The mechanics are simple enough that people skip the preparation, and then spend the afternoon arguing about whether a variance is real.
Here is the version that survives contact with a real count.
Your scanner is a keyboard
Almost every USB or Bluetooth barcode scanner ships in what is called keyboard wedge mode. The computer sees a keyboard. The scanner "types" the digits it read, then sends a terminating keystroke — usually Enter.
This single fact explains everything else. You do not need drivers, software, or an integration. You need the cursor to be in the right cell. And because the scanner types, whatever Excel does to typed text, it will do to your barcodes.
Decide the suffix before you scan anything
The terminating keystroke decides the shape of your sheet. Most scanners are configured by scanning programming barcodes out of the manufacturer's manual, and it takes about a minute.
| Suffix | Cursor moves | Use when |
|---|---|---|
| Enter (CR) | Down one row | One scan per row — the count log below |
| Tab | Right one column | You capture several fields per item |
| None | Stays put | Almost never; you will overwrite the cell |
Pick Enter and a one-column log. Every alternative sounds cleverer and causes trouble at 300 scans in.
Format the column as text first — this is the one that bites
Do this before the first scan, not after: select the column, set the cell format to Text.
Excel treats a scanned barcode as a number, and numbers have two behaviours that quietly destroy identifiers:
- Leading zeros disappear. A UPC-A that reads
012345678905becomes12345678905. It now matches nothing. - Precision stops at 15 significant digits. An 18-digit SSCC pallet code such as
003456789012345675comes back as3456789012345670— the tail is replaced with zeros, and Excel may display it as3.45679E+15for good measure.
The second one is nastier because it looks like a formatting glitch rather than data loss. It is data loss: the digits are gone from the cell, not just from the display. Widening the column will not bring them back.
If you inherit a sheet where this already happened, the honest answer is to rescan the affected column. Re-formatting after the fact recovers nothing.
Scan into an append-only log, not into a quantity cell
The tempting design is a master sheet with a Quantity column that you increment as you scan. Do not do that.
Use a flat log instead — one scan, one row:
| Timestamp | Location | Code | Counter |
|---|---|---|---|
| 09:14:02 | A-01-3 | 5901234123457 | TL |
| 09:14:04 | A-01-3 | 5901234123457 | TL |
| 09:14:09 | A-01-3 | 4006381333931 | TL |
Then aggregate at the end with a PivotTable, or =COUNTIF(C:C, <code>) for a quick total.
The reason is auditability. When a variance turns up — and one will — a log lets you answer when and where the item was counted. A Quantity column that was edited in place has thrown that evidence away, and you are left with two numbers and no way to choose between them.
It also makes the log recoverable. If someone counts the wrong aisle, you delete those rows. If the totals were live-edited, you re-count.
Two counting modes, and when to switch
Scan every unit. Accurate, and unbeatable for mixed stock where quantities are small and irregular. It also gets miserable fast — 4,000 units means 4,000 trigger pulls.
Scan the SKU once, type the quantity. Far quicker for pallets and full cartons, and it moves the error from mis-scanning to mis-typing, which is the error people actually notice.
A reasonable rule: scan per unit while a location holds fewer than about 20 of an item, and switch to scan-plus-quantity above that. The exact threshold matters less than agreeing on it before you start, so two counters do not use different methods on the same aisle.
Count blind, and freeze the movement
Two disciplines separate a count you can trust from one you cannot.
Do not show the expected quantity to the counter. If the sheet says 40, a person who counts 38 will look for two more before writing anything down. This is not dishonesty, it is how attention works — and it converts a real discrepancy into a clean sheet with a hidden problem.
Stop stock moving, or record it separately. If picking continues during the count, every variance becomes arguable. When you cannot pause operations, keep a separate log of everything that moved during the window and reconcile it explicitly.
Reconciling: read the negatives first
Put expected against counted, and let the difference sort itself:
| Code | Expected | Counted | Variance |
|---|---|---|---|
| 5901234123457 | 40 | 38 | −2 |
| 4006381333931 | 12 | 14 | +2 |
Then work the negatives before the positives. A shortfall is usually one of three boring things: the item sits in a second location nobody scanned, it was counted under a different code, or a scan did not register. Theft is far down the list, and treating it as the first explanation poisons a count.
One signature worth knowing: the double scan. Scanners fire faster than people let go of the trigger, so a single item occasionally lands twice. In an append-only log it is obvious — the same code, same location, timestamps a second or less apart. In a live-edited quantity column it is invisible, and it looks exactly like a real surplus.
Where this approach stops working
Being straight about the limits: a spreadsheet count is a snapshot taken by trusted people. It has no permissions, no concurrency, and no audit trail beyond what your log happens to record.
Signs you have outgrown it:
- more than one person needs to write to the sheet at the same time;
- you need stock levels to be correct between counts, not just on count day;
- someone asks who changed a number, and the file cannot answer;
- the log has passed roughly 100,000 rows and the PivotTable has become the slowest part of your day.
None of these are reasons to avoid starting in Excel. They are reasons to know what you will replace it with.
Label what you will count next time
Most stocktake pain traces back to items that were never labelled properly. A count is the natural moment to fix it, because you are already handling everything once.
If you are generating labels in bulk, paste or import the codes as a CSV, pick a label template that matches the sheets you own, and print the lot in one pass. BarcodeReady supports bulk generation onto Avery A4 sheets (L7160, 3×7; L7163, 2×7; L7651, 5×13) and US Letter stock (5160, 3×10 at 2.625″×1″; 5163, 2×5), plus DYMO roll sizes — so the barcodes land on the label geometry you actually have, rather than being scaled to fit and losing the proportions the scanner depends on.
Two things to get right on that print run: keep the codes at 100% rather than "fit to page", and leave the quiet zones intact. A label that scans on the desk but not on a moving pallet is almost always one of those two.
The short version
Set the scanner suffix to Enter. Format the column as Text before the first scan. Log one scan per row and aggregate afterwards. Count blind, freeze movement, and read the negative variances first.
None of it is difficult. All of it is the difference between a count that closes by noon and a count you have to run again.