How to Create a Simple Inventory System in Excel Using Barcodes

If you are still typing SKU numbers into a spreadsheet by hand, you are paying for it twice — once in time and again in the typos that creep in around item four hundred. You do not need a pricey ERP to fix that. A plain spreadsheet plus a barcode scanner gets you most of the way to a real inventory system.

But there is a step almost every guide skips, and skipping it is what turns a working system into one that quietly loses data: Excel will rewrite some of your barcodes the moment they land in a cell, and it will not warn you. So that comes second here, right after the layout — before you print a single label.

1. Plan the spreadsheet first

Before generating a single code, set up a clean source of truth in Excel — one table, one row per item:

• Product ID (SKU) — a unique value per item, e.g. PROD-001.
• Product name — the human-readable label.
• Current stock — quantity on hand.
• Location — where it lives, e.g. Shelf A-1.
• Reorder point — the level at which you restock.

Keep the Product ID alphanumeric with no spaces or odd symbols; some older scanners stumble on them.

2. Stop Excel from eating your codes

A cell formatted as General — the default — tries to interpret whatever arrives as a number. For barcodes that goes wrong in three distinct ways.

Leading zeros disappear. Scan 007123 into a General cell and you get 7123. The label and the spreadsheet now disagree, and every lookup on that SKU misses.

Long codes turn into scientific notation. A 13-digit EAN shows up as 5.90123E+12. Annoying, but at least visible.

And the one that actually costs money: digits past the fifteenth are replaced with zeros. Excel keeps only 15 significant decimal digits. Anything longer is silently truncated — permanently, not just in the display.

Retail barcodes survive. Pallet labels do not. If you handle SSCC-18 — and you do, the moment you ship on pallets or receive from a distributor — every one of them loses its last three digits.

Here is the part that makes this genuinely dangerous rather than merely annoying. You might reasonably expect a check-digit test to catch the damage. We generated 100,000 valid SSCCs, ran each through a numeric cell, and re-tested it: 9.9% of the corrupted codes still pass a check-digit test. Validation catches roughly nine in ten — and the tenth looks perfectly correct all the way to the pallet.

The fix, before you scan anything:

1. Select the SKU column.
2. Format Cells → Text. Do this before any data arrives; changing the format afterwards does not bring back digits that are already gone.
3. Importing an existing file? Use Data → From Text/CSV and set the code column to Text in the import dialog, not after.

A quick way to confirm you got it right: text-formatted entries align left by default, numbers align right. If your codes are hugging the right edge of the cell, Excel still thinks they are numbers.

3. Generate the barcodes

Now turn those IDs into something a scanner can read. A lot of people reach for a "barcode font" in Excel — these look amateurish and fail to scan the moment the size or margins are slightly off. A font also cannot add a check digit or a quiet zone, so it will happily render an invalid symbol.

The reliable route is to generate proper SVG barcodes for each SKU, so every code is standard-compliant and crisp. Create one per SKU on BarcodeReady and download them as SVG to keep the edges sharp through printing. For a catalogue of any size, bulk generation from a CSV beats doing it one at a time.

4. Print and label

Move from screen to shelf:

1. Print onto adhesive label sheets such as Avery.
2. Put one label on each item, or on the shelf location where it is stored.
3. Test-scan a sample before doing a thousand, and confirm the scanner drops the right Product ID into Excel.

Step 3 is not optional, and it is worth doing on the printer you will actually use — the dot grid changes the printed dimensions in ways that are easy to miss. Common barcode printing errors covers what to measure.

5. Wire up a scanning station

Here is where it starts to feel automatic. A barcode scanner behaves like a keyboard: it "types" the value and then sends a suffix keystroke.

Which suffix it sends decides how your sheet fills up:

Nearly every scanner lets you switch this by scanning a configuration barcode from its manual. It is a two-second change that decides whether your data lands in the shape you wanted.

Make a second sheet called Scanner Log, format the ID column as Text (see step 2), click a cell, and scan — the ID lands and the cursor moves.

6. Validate every scan with a check digit

A scanner misreads far less often than a human mistypes, but "far less often" is not never, and a damp or scuffed label raises the odds. The last digit of a GTIN exists precisely so you can check the rest of it, and Excel can do that check for you:

=MOD(10-MOD(SUMPRODUCT(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)
   *(3-2*MOD(LEN(A2)-ROW(INDIRECT("1:"&LEN(A2))),2))),10),10)

Put the code without its final digit in A2; the formula returns what the final digit should be. Compare it with the digit you actually scanned.

Why the formula looks more complicated than the ones you will find elsewhere: GS1 weights the digits from the right, not the left — the last digit of the body gets weight 3, then 1, 3, 1 and so on. If you count from the left instead, you get the right answer only when the body has an even number of digits. That covers EAN-13 (12 digits) so the simpler formula appears to work — and then silently fails on UPC-A (11) and GTIN-14 (13). The LEN(A2) term is what makes one formula work for all of them.

Two honest limits worth knowing. A check digit catches 91% of adjacent transpositions — swapping two neighbouring digits, the classic manual-entry error. The ones it misses are exactly the pairs that differ by 5, and that is inherent to the 1/3 weighting rather than a flaw: swapping two digits shifts the weighted sum by twice their difference, and 2 × 5 = 10 disappears modulo 10. And as shown in step 2, a check digit will not reliably tell you that Excel truncated the code — fix the cell format instead of relying on validation.

7. Look up details automatically

To pull in a product name next to each scan:

=XLOOKUP(A2, Products!$A$2:$A$5000, Products!$B$2:$B$5000)

Note the bounded ranges. It is tempting to write Products!A:A — it is shorter and it works — but a whole-column reference points at all 1,048,576 rows of the sheet, on every row of your log:

That is 210 times more cells for the same answer. Modern Excel optimises much of this away, but it is the single most common reason a scan log that felt instant in week one takes a visible pause by week six. Better still, turn your product list into a real Table (Ctrl+T) and reference Products[SKU] — the range then grows with the data on its own.

8. The daily rhythm

With that in place, day-to-day use is quick:

• Receiving: scan the item, add to the stock count.
• Shipping: scan the item, subtract from the count.
• Stocktake: scan what is on the shelf and compare the totals to spot discrepancies. Running a full stocktake has its own workflow.

When to outgrow Excel

A spreadsheet is a great starting point, but it has a ceiling. The honest signals that you have reached it:

• Two people need to scan at the same time. This is the real limit, and it is not about size — a shared workbook handles simultaneous writes badly, and someone's scans get lost.
• You handle SSCC-18 pallet labels routinely. Step 2 makes them safe, but one colleague opening the file and re-formatting a column undoes it silently.
• Your catalogue passes a few thousand SKUs and the lookups start to lag even with bounded ranges.
• You need live sync with a shop — Shopify, WooCommerce or similar. See the Shopify & WooCommerce barcode guide.

Until then, Excel plus barcodes is the fastest and cheapest way to put real structure around your stock — provided the cells are formatted as text before the first scan. Generate the codes you need on BarcodeReady and start scanning.

Related Articles

• Asset Tracking: Manage Company Equipment with Barcodes
• How to Barcode a Warehouse: Step-by-Step Guide to Location Labeling
• Shopify & WooCommerce Barcode Guide
• How to Generate Barcodes in Bulk: The Complete Guide
• How to Print Barcodes at Home
• How to Run a Stocktake with a Barcode Scanner and Excel
• SSCC-18 Pallet Labels: The Complete Guide