VBA & Macro Problems

How to Use a Barcode Scanner to Update Inventory Quantities in Excel

Olivia Miller Oct 10, 2026 872 views

Question details

The user wants to connect a barcode scanner to Excel to automatically input product codes and update inventory quantities.

How to Use a Barcode Scanner to Update Inventory Quantities in Excel

Product: Excel
Device & OS: not provided
Scenario: Setting up a barcode scanner to track and manage inventory in a spreadsheet.
Observed behavior: The goal is to have Excel automatically register scanned barcodes and use lookup formulas or VBA to update stock levels without manual typing.

Before you start

Ensure your USB or Bluetooth barcode scanner is properly connected to your computer and that any manufacturer-required drivers or software are installed.

Solution 1Recommended

Set Up and Scan Barcodes into Excel

Configure your scanner and Excel to accept barcode inputs smoothly into consecutive cells.

Most standard barcode scanners act as a keyboard wedge, meaning they type the barcode numbers as if you were typing them on a keyboard, followed automatically by an 'Enter' keystroke.

Open the workbook

Open the Excel file where you want to record your inventory.

Select the starting cell

Click on the specific cell where the first scanned barcode should be entered.

Scan the barcode

Use your scanner to scan a barcode. The scanned value will be entered into the selected cell, and the cursor will automatically move to the next cell.

Control cursor movement

To force the cursor to move to a specific column or row after each scan, adjust the worksheet protection settings to only allow selecting unlocked cells.

Auto-Movement Configuration: If your scanner does not move to the next cell automatically, you may need to scan the 'Carriage Return' or 'Enter Key' configuration barcode found in your scanner's manual.

Solution 2

Count Scanned Inventory using Subtotals

Quickly calculate the total number of scanned product codes using Excel's built-in Subtotal feature.

Solution 3

Automate Inventory Updates with VBA

Use VBA macros to automatically increase or decrease stock levels when a product is scanned.

Manage Inventory Efficiently with WPS Spreadsheet

WPS Office Spreadsheet provides full support for barcode scanner inputs, advanced lookup formulas, and VBA macros to help you build a robust inventory management system completely free of charge.

1. Create an inventory file: Open WPS Spreadsheet and create a new workbook with columns for Barcode, Product Name, and Quantity.
2. Connect hardware: Plug in your USB or Bluetooth barcode scanner and click on the target input cell.
3. Scan products: Scan your items to automatically populate the cells with product codes.
4. Analyze and subtotal: Use the Data > Subtotal feature in WPS Spreadsheet to quickly count and categorize your stock levels.

Seamless compatibility with Microsoft Excel (.xlsx and .xlsm) formats.Fully supports external hardware inputs like USB and Bluetooth barcode scanners.Advanced functions like VLOOKUP, XLOOKUP, and VBA macros available.Lightweight and runs smoothly on all major operating systems.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my barcode scanner put all scans in the same cell?

Your scanner may not be configured to send an 'Enter' keystroke after scanning. You can change this by scanning the specific configuration barcode provided in your scanner's user manual, which tells the scanner to add a carriage return after each scan.

How do I prevent users from accidentally typing over scanned barcodes?

You can lock the cells containing historical data and protect the worksheet. Go to the Review tab and click Protect Sheet. Leave only the current input cell unlocked so the scanner can input data without risking overwriting past entries.

Can I use formulas instead of VBA to update quantities?

Yes, you can use formulas like COUNTIF or VLOOKUP. By scanning all items into a 'Log' sheet, you can use a COUNTIF formula on your master inventory sheet to count how many times a specific barcode appears in the log, effectively calculating the updated inventory quantity.

Excel & Data Problems