How to Use a Barcode Scanner with an Excel

This article may contain affiliate links. We may earn a commission when you sign up through our links — at no extra cost to you. Our recommendations are based on independent evaluation and are not influenced by commissions.

TL;DR: Connect a keyboard-emulation barcode scanner, build a Master Inventory tab and a Stock Log tab, and let XLOOKUP name the product the moment you scan it. A SUMIFS column then recalculates on-hand stock from every logged IN and OUT. Setup takes about 30 minutes. The only unavoidable cost is the scanner — roughly $20–50 — because Google Sheets and Excel for the web are free.

• This is semi-automated, not hands-free. The scanner types the barcode; you still supply the timestamp, IN/OUT, and quantity.
• Format both barcode columns as Text before you scan anything. A Text/Number mismatch between the two tabs makes XLOOKUP fail on barcodes that plainly exist.
• XLOOKUP needs Microsoft 365, Excel 2021, or Excel 2024. Excel 2016 and 2019 do not have it — use the VLOOKUP variant below.
• Never timestamp with NOW(). It rewrites every past entry the next time the workbook recalculates.

Barcode scanner workflow with an Excel inventory spreadsheet for small business stock tracking

Who this guide is for

This is for small business owners, solo operators, retailers, and lean stockroom teams who already track stock in a spreadsheet and are tired of typing long barcode numbers by hand. Manual barcode entry creates an avoidable error point: a mistyped digit can produce an unknown code or, less commonly, match the wrong item if that code already exists. A scanner removes that keystroke without requiring a database or inventory-software subscription.

Who should skip this

Skip it if you need real-time syncing across locations, several people scanning simultaneously, or stock that updates itself when a sale closes. Teams already on dedicated inventory software will find this workflow a step backwards — it is deliberately manual on the fields the scanner cannot fill.

Skip it too if your products carry no barcode labels yet, because there is a decision to make first. For internal tracking, assign your own unique item IDs and encode them in Code 128. For products sold through retail or marketplace channels that require a GTIN, use a properly assigned identifier from GS1 rather than inventing a UPC or EAN number.

The workflow at a glance

1. Get a scanner. USB or Bluetooth, HID / keyboard-emulation mode, with an Enter suffix. A 2D imager only if you scan QR codes.
2. Set up two tabs. Master Inventory (your product database) and Stock Log (every movement).
3. Build the master. Scan each product once, add its name, starting quantity, and reorder point.
4. Log movements. Scan into the log; XLOOKUP names the product; you add time, IN/OUT, and quantity.
5. Track and flag. SUMIFS gives live on-hand counts, and conditional formatting turns low stock red.

Prerequisites

Tools

• A spreadsheet with XLOOKUP: Microsoft 365, Excel 2021, or Excel 2024. Microsoft states that XLOOKUP is not available in Excel 2016 or Excel 2019 — on those versions use the VLOOKUP variant in the templates section. Google Sheets has supported XLOOKUP since 2022 and works throughout.
• A USB or Bluetooth scanner in HID / keyboard-emulation mode. A 1D scanner covers UPC, EAN, and Code 128. Choose a 2D imager only if your labels are QR codes — a 1D-only unit will never read them.
• Physical labels on your products: existing UPC/EAN for retail goods, or your own Code 128 item IDs for internal tracking.

Before you buy the scanner, confirm two things on the product page: that it supports USB HID or keyboard-emulation mode, and that it can be configured to send an Enter (carriage return) suffix after each scan. Without the Enter suffix, you lose the automatic row advance this workflow depends on.

People

• One lead to build the workbook and own the Master Inventory tab.
• Stockroom or retail staff to run the scanner. They should only ever touch the Stock Log.

Estimated time: about 30 minutes to build, plus however long your initial catalog scan takes
Difficulty: Beginner

Step-by-step workflow

Step 1: Set up your two tabs

The structural decision that matters most is keeping the product database physically separate from the transaction log. Open a blank workbook and create two tabs: Master Inventory and Stock Log. To match the example workbook and screenshots in this guide, use Row 1 for a sheet title, Row 2 for column headers, and start your data in Row 3. During a fast scanning session it is easy to overwrite a row; separating product definitions from movement records limits the damage of a bad paste or scan session.

Master Inventory, Row 2:

• A2: Barcode
• B2: Product Name
• C2: Starting Quantity
• D2: Reorder Point
• E2: Current Stock (the formula starts in E3 in Step 5)

Stock Log, Row 2:

• A2: Timestamp
• B2: Barcode Scanned
• C2: Product Name (the formula starts in C3 in Step 4)
• D2: Movement Type (IN or OUT)
• E2: Quantity

Now format both barcode columns as Text before scanning. Excel can display long numeric codes in scientific notation, but the display format alone does not mean the stored value has changed. The real risks are different: numeric entry removes leading zeros, and Excel preserves only the first 15 digits of a number. Storing barcodes as Text keeps the identifier exactly as entered and also prevents a Text/Number mismatch between the master and log lookup columns.

If you already scanned codes before changing the format, inspect the original labels. A code that lost a leading zero or exceeded Excel’s numeric precision should be scanned again after the column is formatted as Text; changing the format does not reconstruct digits that were already altered.

Expected outcome: A workbook with a dedicated master database and an active log, both storing barcodes as text.

Step 2: Connect the scanner and prove it works

Plug the scanner into a USB port and wait for the operating system to recognize it. Most scanners are “keyboard wedge” devices: the computer treats them as an ordinary keyboard, so there is no driver to install and no software to configure.

Open Notepad or any blank text field and scan a physical item. You should see the digits appear instantly, followed by a line break. If the digits appear but the cursor stays on the same line, the Enter suffix is not enabled — find the configuration barcode in the scanner’s manual and scan it to switch the suffix on. Do this now, in Notepad, rather than discovering it halfway through your first catalog session.

Expected outcome: A scan types the barcode digits and presses Enter, with no software in between.

Step 3: Build the product database

The Master Inventory tab is the source of truth for everything that follows, so it is worth the time to get it right. Click cell A3 and scan your first product. The barcode lands in A3 and the cursor drops to A4. Move across to B3 for the product name, C3 for starting quantity, and D3 for the reorder point.

Repeat for every unique item. With a few hundred SKUs this goes much faster in pairs — one person scans down column A while the other types names into column B afterwards.

The Master Inventory tab: one row per product, barcodes stored as text

One rule: a barcode should map to one product definition. Do not use Remove Duplicates as the first cleanup step on the master, because Excel removes duplicate records rather than asking which product row is correct. Instead, select the barcode range and go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Review every highlighted barcode, decide which product row is correct, and then correct or delete the bad record manually.

Expected outcome: A reviewed list of every item you carry, with one product definition per barcode plus starting stock and a reorder threshold.

Step 4: Log stock IN and OUT

Be clear about what this step is and is not. The scanner removes the barcode typing and Excel identifies the product automatically. It does not know whether the box in your hand is arriving or leaving, or how many units are in it. Those two fields, plus the timestamp, stay manual.

On the Stock Log tab, click C3 and enter:

=XLOOKUP(B3, 'Master Inventory'!A:A, 'Master Inventory'!B:B, "Unknown Product")

That fourth argument does the job of the IFERROR wrapper you may know from VLOOKUP: an unrecognized barcode shows “Unknown Product” rather than an error. Drag the formula down. On Excel 2016 or 2019, use the VLOOKUP version from the templates section below.

Now scan a product into B3. The barcode lands, Enter fires, the cursor drops to B4 — and C3 fills in with the product name from your master sheet. That instant confirmation is the whole payoff: if C3 says “Unknown Product”, you know immediately that the item is missing from the master, and you can fix it before the count is wrong.

XLOOKUP in the Stock Log names the product the moment the barcode is scanned

In column D, record IN for received stock and returns, or OUT for anything sold or shipped. Do not leave this to typing. Select column D, go to Data > Data Validation, choose List, and enter IN,OUT as the source. The SUMIFS formula in the next step matches on these exact strings, so a stray “in ” with a trailing space silently drops that movement out of the count. A dropdown removes the possibility entirely. Enter the quantity moved in column E.

Finally, the timestamp in column A. In Excel on Windows, select the cell and press Ctrl+; for the date, type a space, then press Ctrl+Shift+; for the time. Google Sheets uses the same two shortcuts. This writes a static value — which is the point, and the reason the next section warns you off NOW().

Expected outcome: Scanning identifies the product with no typing. You complete each row with a fixed timestamp, an IN/OUT value from the dropdown, and a quantity.

Step 5: Calculate current stock and flag low inventory

Go back to Master Inventory, click E3, and enter:

=C3 + SUMIFS('Stock Log'!E:E, 'Stock Log'!B:B, A3, 'Stock Log'!D:D, "IN") - SUMIFS('Stock Log'!E:E, 'Stock Log'!B:B, A3, 'Stock Log'!D:D, "OUT")

Fill it down. The formula takes the starting quantity, adds every IN logged against that barcode, and subtracts every OUT. From here on, the on-hand number recalculates the moment a Stock Log row is completed.

SUMIFS turns the movement log into a live on-hand count

To flag low stock, select E3:E100 — start at E3, the first data row — and go to Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Enter:

=AND($E3>=0,$E3<=$D3)

Pick a red fill. The rule flags stock at or below its reorder point while leaving negative balances for a separate data-quality rule.

Add a second rule with =$E3<0 and a clearly different fill. In this workflow, a negative on-hand balance is a data-quality exception — usually a duplicated OUT movement or a missing starting quantity. Because the low-stock rule explicitly excludes negative values, the two colors cannot compete.

If you also want movement history, select the Stock Log data and go to Insert > PivotTable: Barcode Scanned to Rows, Movement Type to Columns, Quantity to Values. The pivot shows flow per item; column E shows the balance. Because your timestamps are static, you can filter the source by date to build a clean monthly summary.

Expected outcome: A live on-hand count per product, with reorder candidates in red and impossible values flagged.

Step 6: Harden it before you hand it to staff

The workbook works at this point. It is not yet robust, and the gap shows up on day two rather than day one.

The XLOOKUP you dragged down column C is evaluating empty rows too, so every unused row below your data reads “Unknown Product”. That is noise at best, and at worst it trains staff to ignore the exact message that is supposed to stop them. Wrap it:

=IF(B3="", "", XLOOKUP(B3, 'Master Inventory'!A:A, 'Master Inventory'!B:B, "Unknown Product"))

Then do three small things that cost a minute each:

• Freeze the header row (View > Freeze Panes > Freeze Top Row) so the columns stay labelled 400 rows into a scanning session.
• Validate the Quantity column (Data > Data Validation, whole number greater than 0) so nobody logs a movement of zero — or of “5 boxes”.
• Protect the Master Inventory tab and leave the Stock Log open. Staff need to add rows to the log; they never need to edit a product definition.

Expected outcome: A workbook that survives contact with a busy stockroom and a second pair of hands.

Common pitfalls and how to avoid them

• “Unknown Product” for a barcode that is definitely in your master sheet.
  ⚠️ Why it bites: The two barcode columns are stored in different formats — one Text, one Number. XLOOKUP looks for an exact match and never finds one. The count is then wrong in a way that looks like a scanning problem rather than a formatting problem.
  🔧 Fix: Format Master Inventory column A and Stock Log column B as Text before scanning. If data is already in, reformat and re-enter it — changing the format alone does not convert existing values.
• A barcode displays in scientific notation or loses a leading zero.
  ⚠️ Why it bites: Excel is treating the identifier as a number. Scientific notation can be a display format, but numeric entry can remove leading zeros and values longer than 15 digits can lose precision.
  🔧 Fix: Format both barcode columns as Text before scanning. If a code was already altered, re-scan or re-enter it after changing the format.
• Every timestamp in the log suddenly shows the same time.
  ⚠️ Why it bites: Someone used =IF(B3="","",NOW()) because it looked convenient. NOW() is a volatile function: it recalculates whenever the workbook does, so every historical entry silently rewrites itself to the current time. Your audit trail is gone and nothing warned you.
  🔧 Fix: Enter timestamps as static values — Ctrl+;, space, Ctrl+Shift+;.
• A movement is logged but the count does not change.
  ⚠️ Why it bites: SUMIFS matches the literal strings “IN” and “OUT”. A typed “in ” with a trailing space, or “Out ” pasted from somewhere else, matches neither criterion, so the row is quietly excluded from both sums.
  🔧 Fix: Force the values with a data validation dropdown on column D. If a count looks off, filter column D and check for anything that is not exactly IN or OUT.
• The lookup returns the wrong product.
  ⚠️ Why it bites: The same barcode appears more than once in Master Inventory, and XLOOKUP returns the first matching result.
  🔧 Fix: Highlight duplicate barcode values with Conditional Formatting, review the conflicting rows, and correct or delete the bad product record manually. Do not run Remove Duplicates blindly on the master database.
• Current Stock goes negative.
  ⚠️ Why it bites: In this workflow, a negative balance usually points to a duplicated OUT movement or a missing starting quantity. The formula is exposing a mismatch between the log and the physical count.
  🔧 Fix: Add the =$E3<0 conditional formatting rule from Step 5 so the exception surfaces immediately.

Scenario fit: when to adapt this workflow

• Moving to Google Sheets. Worth it if the sheet needs to be reachable from more than one machine. XLOOKUP has been supported since 2022, and SUMIFS, data validation, and pivot tables all behave the same, so the formulas carry over unchanged. The catch is hardware, not software: a USB scanner needs an OTG adapter to reach an Android tablet, and iPads generally want a Bluetooth scanner.
• Upgrading to dedicated software. Catalog size alone is a weak migration trigger. The stronger signals are structural: two people needing active scan sessions, stock that must move when a sale closes, counts separated by location, or requirements for serial numbers, lot tracking, or shipping integrations. Those needs push beyond the simple shared-log design used here.
• Printing your own labels. For internal tracking, assign a unique item ID and encode it as Code 128. For products sold through retail or marketplace channels that require a GTIN, use a properly assigned GS1 identifier rather than inventing a UPC or EAN number.

What this setup costs

Most of the cost here is a one-time hardware purchase, not a subscription. US pricing checked July 2026.

Realistically, the new setup cost is mostly the scanner if you stay on Google Sheets or Excel for the web. Microsoft’s US commercial pricelist for Microsoft 365 Business Standard changed to $14 per user per month effective July 1, 2026; existing customers remain on their current price until renewal.

Comparing that against inventory software is not a hardware-payback calculation. A subscription is not buying you a scanner; it is buying you the things this workbook structurally cannot do — mobile scanning, several people updating at once, automatic stock movement on a sale, and location-level counts. The inventory spreadsheet vs. software guide works through where that line falls, and the Sortly setup guide shows what a barcode workflow looks like once it lives in an app instead of a workbook.

Templates and formulas

If you would rather not build it from scratch, start from one of these:

• Blank template (.xlsx) — the two tabs, headers, and formulas, ready for your catalog.
• Completed example (.xlsx) — a filled-out version showing how the master, log, and current stock formulas interact.

Or copy the formulas directly:

For a movement summary, build a pivot table from the Stock Log with Barcode Scanned in Rows, Movement Type in Columns, and Sum of Quantity in Values. That gives you flow per item; the live balance already lives in column E of the master.

Frequently asked questions

Do I need special software for the scanner?

No. A standard USB scanner is a keyboard wedge — the computer treats it as a keyboard and types the digits straight into whichever cell is active. No driver, no middleware.

Does this work in Google Sheets?

Yes, and the formulas transfer without edits — Sheets has supported XLOOKUP since 2022, and SUMIFS, IFERROR, and pivot tables all work the same. The difference is hardware: a desktop browser needs nothing extra, while a USB scanner on an Android tablet needs an OTG adapter.

How do I get barcodes for products that have none?

For internal tracking, assign your own item IDs, encode them as Code 128, print labels, and enter the same IDs in Master Inventory. For products sold through retail or marketplace channels that require a GTIN, use a properly assigned GS1 identifier rather than inventing a UPC or EAN number.

Why does my scanner type unexpected characters?

In keyboard-emulation mode, the scanner and the operating system need compatible keyboard layouts. Check the computer’s keyboard language first, then check the scanner manual for its USB HID or Bluetooth HID country-keyboard setting. A layout mismatch can map scanned characters to the wrong keys.

Will a Bluetooth scanner work?

Yes. A Bluetooth scanner in HID keyboard-emulation mode can feed the spreadsheet in the same basic way as a wired keyboard scanner. Pairing, battery behavior, keyboard layout, and Enter-suffix configuration still vary by model, so test the scanner in a blank text field before using the Stock Log.

Can several people scan into the workbook at the same time?

Excel and Google Sheets can support concurrent editing, but this specific workflow uses one shared Stock Log with no row reservation or transaction lock. Two scan sessions can interleave records or compete for the same input area. For more than one active scanner, give each operator a separate log and consolidate the logs, or move to inventory software built for concurrent inventory transactions.

Sources and notes

• Prices and product terms below were checked in July 2026 against Microsoft’s own pages.
• Microsoft — XLOOKUP function — syntax, the if_not_found argument, and confirmation that XLOOKUP is absent from Excel 2016 and Excel 2019.
• Microsoft — NOW function — the volatile recalculation behavior behind the timestamp warning.
• Google Docs Editors Help — XLOOKUP — XLOOKUP support in Google Sheets.
• Microsoft 365 Personal — $99.99/year and $9.99/month US pricing.
• Office Home & Business 2024 — $249.99 one-time price and commercial-use rights.
• Microsoft 365 pricing and packaging updates — the Business Standard price change effective 1 July 2026.
• GS1 — Get a barcode / GTIN — the distinction between internal Code 128 labels and assigned GTIN, UPC, and EAN identifiers.
• Zebra — What is a 2D barcode? — the 1D versus 2D scanner distinction for QR codes.

Disclaimer

This guide is for general informational purposes only and does not constitute professional advice. Workflow steps, tool interfaces, feature availability, and pricing were verified against each product’s official documentation in July 2026 and may change without notice. Test any workflow on a copy of your data before relying on it. PickrTech may earn a commission when you sign up through our links, at no extra cost to you. Our recommendations are based on independent evaluation and are not influenced by compensation.

Last reviewed: July 2026 by the PickrTech editorial team.