How to Scan Barcodes Into Excel or Google Sheets

Typing barcodes by hand is slow and one wrong digit breaks the whole list. Scanning them into a spreadsheet takes a few minutes to set up and then just works. There are two ways to do it.

Way 1: a USB or Bluetooth barcode scanner

Most handheld scanners act like a keyboard. When you scan, they “type” the number and press Enter. That means they work with Excel, Google Sheets, Numbers or any other app, with no software to install.

1. Plug in the scanner (or pair it over Bluetooth).
2. Open your spreadsheet and format the barcode column as Text (see below).
3. Click the first empty cell in that column.
4. Scan. The number appears and the cursor moves down a row, ready for the next one.

If your scanner moves right instead of down, or doesn’t press Enter at all, check its manual. Most scanners are set up by scanning special setup barcodes printed in the manual.

Way 2: your phone

No scanner? Your phone’s camera can do the same job. You can’t type into a desktop spreadsheet from a phone camera directly, so the usual way is: scan a batch in an app, then export a file.

1. Scan the products one after another in an app with a batch or list mode.
2. Export the list as a CSV file (Excel, Google Sheets and Numbers all open it).
3. Open the file on your computer, or import it into Google Sheets (File › Import).

QR Code Scan Kit works this way. Batch scan keeps the camera running and adds every new code to a list. Stock count adds +1 when you scan the same product again, so 12 cans of the same soup become one row with a quantity of 12. Then you export a CSV file that opens in Excel, Numbers or Google Sheets, print the list or save it to a folder.

Keep barcodes as text (the step everyone misses)

Spreadsheets think a barcode is a number. That causes two problems:

• Leading zeros vanish. A UPC like 036000291452 becomes 36000291452, which is a different, invalid code.
• Long numbers turn into scientific notation. A 13-digit EAN like 6151234567890 shows as 6.15123E+12. Excel also only keeps 15 significant digits, so anything longer gets rounded and corrupted for good.

The fix is to treat the column as text before any barcodes go in:

• Excel: select the column › right-click › Format Cells › Text. When importing a CSV, use Data › From Text/CSV, click Transform Data and set the barcode column type to Text.
• Google Sheets: select the column › Format › Number › Plain text.
• Numbers (Mac and iPhone): select the column › Format › Cell › Text.

If the damage is already done, re-import the original file. Formatting a column as text afterwards won’t bring back a lost zero.

Count what you scanned

Once barcodes are in column A, you can count them without retyping anything.

How many times was each barcode scanned? In column B, next to each barcode:

=COUNTIF(A:A, A2)

A clean stock list (one row per product):

• Excel: select the data › Insert › PivotTable › drag the barcode field to Rows and again to Values (set to Count).
• Google Sheets: Insert › Pivot table, add the barcode as a row and as a value summarised by COUNTA.

Or skip the pivot table: if your app already counts repeat scans, the export has a quantity column.

Add product names

A barcode on its own is just a number. To turn it into a readable list, keep a second sheet with two columns, barcode and product name, and look names up with:

=XLOOKUP(A2, Products!A:A, Products!B:B, "unknown")

(XLOOKUP works in Excel 365 and Google Sheets. In older Excel, use VLOOKUP.)

Free help

• Our barcode scanner to Excel tool reads barcodes with your computer’s camera or from pictures and gives you a ready-made spreadsheet file.
• The free inventory template has the columns already set up, barcodes stored as text.
• If you’re working with UPC and EAN numbers side by side, UPC vs EAN explains how the two fit together.

Sources

• Microsoft Support: Keeping leading zeros and large numbers
• Microsoft Support: Excel specifications and limits (15 digits of number precision)