How to Use a Barcode Scanner for Inventory Counts in Excel

What Is a Barcode?

A Barcode is a machine-readable code consisting of spaces and varying widths of parallel lines representing numers and characters that is used to encode product information. Barcodes increase accuracy and speed and help streamline inventory control.

What Is a Barcode Scanner?

A Barcode Scanner is a handheld or stationary device used to capture and read the information contained in barcodes. A Barcode Scanner consists of a lens, a light source, and a light sensor for translating optical impulses into electrical signals.

How to Use Barcode Scanners for Inventory in Excel:


  • Select the cell you want to contain the barcode. (F5 in the example below).

How to Use Barcode Scanner for Inventory in Excel

  • Scan the barcode for the first inventory item. The barcode number and other product information will populate the corresponding cells in that row.

  • Scan the remaining product barcodes.
  • Sort by barcode.

How to Use Barcode Scanner for Inventory in Excel

  • Select all the relevant barcodes.
  • Go to the Data tab.
  • Click on Outline.
  • Pick Subtotal.

A Subtotal wizard popup window will appear.

  • Choose Count from the Use function box.
  • Check the Replace current subtotals and Summary below data options.
  • Press OK.

How to Use Barcode Scanner for Inventory in Excel

You’ll see the inventory count for each barcode and the grand total of scanned items.

  • Use the boxes on the far left (+ or -) to expand or collapse each section as needed. You can see only the Grand Total, the Subtotals and Grand Total, or all the cells.

How to Use Barcode Scanner for Inventory in Excel

How to Use Barcode Scanner for Inventory in Excel

Read More: Barcode Scanner Entry to Excel Database with Timestamp

Inventory Barcode Scanner Reminder

Although this example shows inventory data like Serial Number, Date, Product Name, Storage Time, etc, that isn’t always the case. Available product information varies based on company requirements and the capability of specific scanners.

Download Practice Workbook

Related Articles

<< Go Back to Use Barcode ScannerBarcode in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Naimul Hasan Arif
Naimul Hasan Arif

Naimul Hasan Arif, a BUET graduate in Naval Architecture and Marine Engineering, has been contributing to the ExcelDemy project for nearly two years. Currently serving as an Excel and VBA Content Developer, Arif has written more than 120 articles and has also provided user support through comments His expertise lies in Microsoft Office Suite, VBA and he thrives on learning new aspects of data analysis. Arif's dedication to the ExcelDemy project is reflected in his consistent contributions and... Read Full Bio

  1. When you scanned the barcode, where did the other information come from? Such as “product 1”, date, storage time, SL, etc? This is what I came onto your post to find out, but you make no reference to where that particular comes from. Please advise.

    • Reply Avatar photo
      Naimul Hasan Arif Dec 19, 2022 at 11:04 AM

      Thanks for your response.
      Normally additional information associated with Barcode is connected through a centralized dataset. When a UPC (Universal Product Code) code is scanned, the scanner sends the code to a server, which looks up the corresponding information in the database and sends it back to the scanner. In other cases like QR codes, the additional information might not be stored in a centralized dataset. QR codes can encode a URL that will take you to the related information regarding the product through scanning.

  2. I want to move from tracking inventory sold tick sheets to Barcode tracking. While at the same time have a daily running inventory stock balance. If I’m going to do this and use a barcode reader how do I download the data collected from the multiple handheld readers.

    • Reply Avatar photo
      Naimul Hasan Arif Aug 1, 2023 at 11:20 AM

      Thanks for your comment. If you go through this article, you might have known that we can extract the defined information with the barcode through scanning. We can also have the sum of all goods in Excel. If you have multiple handheld readers, you can create a separate worksheet for each barcode scanner and summarize them in a new worksheet. In the summarized sheet, you can define the inventory stock balance and subtract the total sold products summing them from different sheets. I hope you have got what you are searching for.

Leave a reply

Advanced Excel Exercises with Solutions PDF