Articles Blog

How to Track Inventory in Excel with a Barcode Scanner

How to Track Inventory in Excel with a Barcode Scanner


Hello and welcome to a POSCatch.com
product presentation, and today we are going to show you how to use Microsoft
Excel as a quick, easy and inexpensive way to keep track of your barcoded
items such as for asset tracking or for inventory tracking. And you would also do
this with a barcode scanner. Now actually this barcode scanner is a Datalogic
QuickScan; this is actually one of the better barcode scanners on the market
today, and if you wanted to know more about this barcode scanner definitely
review our other YouTube video that gives a really great, nifty product
presentation for the Datalogic QuickScan. So what we’re going to do is we’re
going to open up Microsoft Excel; this is actually Microsoft 365, but if you have
an older version of Excel it will still function the same way. Now what we’re
going to do is we have 3 different items here with 3 different bar-
codes, and we’re going to scan these 3 different items. Now you can have of
course more than 3 (doesn’t really matter). We’re just going to use this as
an example. So as you can see you can just scan right in. See that? It’s going
to scan down to the next line, and that’s because we have carriage return enabled
on this barcode scanner. If for whatever reason your barcode scanner does not
have carriage return enabled to where it can do this (scan down to the next line);
you’ll want to refer to your [scanner’s] users manual because you can turn that
function on and off quite easily. OK, so we’re just going to go ahead and
scan these, and we are going to mix them up a little bit here….so as you can see it’s scanning in. It is just going to keep scanning down, and that’s
the way you want to do it right there… do one more…OK… So what we’re going to want to do is put
in a header to this, so it’s very important. So we’re just going to put in a
generic header of “Barcodes”. Now it’s very important that you do put in this
header because if you don’t your count is going to be off. OK, so you put in “Barcodes”, go ahead and highlight this, and then what you want to do is you want to ‘Sort’ and you want to
sort from ‘A to Z’. And as you can see what that does is that groups your barcodes, so it groups all the same barcodes in their own identifying group, which is what you want. And then the next thing you want to do is you want to go up here to ‘Data’…go to
‘Data’ and then go over here to ‘Subtotal’ It’s going to open up a new box…under ‘Use Function’ it’s going to default to ‘Sum’, but you want to change
that to ‘Count’, click on ‘Count’, make sure that your column ‘Barcodes’ here the
check mark is there. Don’t change anything down here. Leave
that as default and then click ‘OK’… …open this up, and then look at that! So we have a ‘count’. Do see that here? So for all of these barcodes (these are all the same
barcode ending in 7546) so all barcodes ending in 7546 there’s a total count of
6. For all barcodes ending in 8438, there’s also account of 6. See that?
And then for all barcodes ending in 192Z, there’s a count of 3. And it gives
you a grand total. You have a grand total count of 15, and then over here you have
these 3 boxes; it says 1, 2 & 3. Number 3 is what you see on your
screen now, and then if you click on box number 2, then it condenses everything
down into your total count. So you have , ‘count’, barcode ending in 7546 is count 6. Barcode ending in 8438 it’s count of 6, and then barcode ending in 192Z is a count of 3 for a total of 15. OK, now this is a great way to do it because let’s just
say you have, you know, 50 or 100 different barcodes and maybe
you can have 2 to 20 pieces of each. So this is an awesome way and a
very inexpensive way for you to keep track of your inventoried items. Now one
thing to note, however, is when you scan a barcode like this, just a regular barcode,
what you’re going to get is just the number. You’re not going to get the item
description. So in this case this is a vitamin, but when you scan this it’s not going to
say what it is. So that is one downside to a regular UPC
barcode, but that said, what you can do is you can just simply add a column in here
and then you can just type in what it is. You can put an identifier in
here, so you do want to keep track of that. But the main thing is to do your
count, and that’s what barcodes are for. Also, too, if you have a inventory
control program you can upload this data from Excel into that program as well,
which is a great tool to have as well. So if you have any further questions about this, please don’t hesitate to contact us or visit our website at https://poscatch.com. Thank you for watching.

32 thoughts on “How to Track Inventory in Excel with a Barcode Scanner”

  1. Can anyone point me in the direction of doing this for not only a roster of kids but also inventory for a martial arts school?

  2. Hello,
    when you scan a code, the reader sends a CR + LF, Excel populates the cell and passes to the cell below.
    Which parameter should I activate, because the cursor remains at the end of the code, it does not go to the next cell, neither to the right nor below.
    thank you,
    bernard in France

  3. This is a great video, but how come when I use my barcode scanner, it enters into say exp A2 it uses the formula, not the actual barcode #

  4. I'm not a genius with this mate but whenever i start scanning, my item will move to the side instead downwards. How do ensure when it scan, it will go downwards?

  5. When i scan my barcode scanner over barcode, the 0 that comes at beginning of UPC dissapeared in the cell, what do i do

  6. If I have 3 different products can I use 1 barcode and just Duplicated for that one item and just buy two others for the remaining did too and duplicate for all the products that are the same

  7. Hi, is there a way to scan a barcode, and locate it within excel? Say, you already added in several barcodes, but when you scan it again, it takes you right to that cell, rather than adding it to the excel sheet?

  8. hello every one , i am an industrial engineer in a garments industry, currently we are facing a severe issue regarding data collection (the number of pieces produced on each operation by operator) to make timely payments for operators as we have piece rate working. the problem is that when our data collecting guys visit production floor, there they dont get exact number of peices produced by operator this way the working of one operator being noted in the account of other operator, so we are in search of a solution through a simplest and cheapest bar code system, if any one here can help us please contact me on 923366561551 from out of pakistan, and for those residing in Pakistan can contact me on 03366561551.

  9. There's no need to select all the barcodes prior to the sort; you need only select one cell among the data.

  10. The count for other barcodes are not correct. How do I make sure when I scan in a barcode they will automatically be counted under the same barcode. My reason for asking is, I scanned in three different barcodes, just one of them show a how count, the rest displays, a count of 1. Can you help point me in the right direction? Thanks a million.

  11. Hello Sir,
    I do have one problem, when I scan the bar-code, the cell jumps to another 2 cell, like A1 to A3, but supposed to be jump into cell A2, (Excel 2013) Please need your help and Thanks in Advance.

  12. I think excel is good for certain level but can't be the best option for inventory. With limit functionality it becomes are hard later in the business. Instead you can try HDPOS. It have inventory alongside with billing, accounting and tax Checkout at: https://bit.ly/2C0u7C9

  13. Hi there it was great watching the video.
    With this video it will record the inventory.
    How to keep a track of outgoing inventory please

  14. Would excel work to setup scanning admission tickets for an event…?
    (No I don't want any of those ticket sales website online or cloud apps for my phone, so plz don't go into that).

  15. i wanna use excel as a quick and dirty inventory control program. how could i had this automated to the point of simply scanning it and it'd add it to the stock quantity? or vice versa to remove from stock quantity when sold? it is possible, so don't try to sell me your product no matter how nice it may be i don't need 99% of it's features.

  16. is there a scanner that allows you to input the number of pieces? eg. there are 50 pieces in one box, with one bar code, but shipment is based on number of pieces generally, not boxes. Is there a way I can input the number of pieces from the scanner to make it faster than manually inputting the numbers later?

  17. I feel like an idiot sometimes. I watched your video 20 times yet I am Thank you.
    confused. You clicked and installed a header called "Barcodes". Did you just insert a cell there? When you go to sort I assume you put a checkmark in "My list has headers? Then when you sort it has two more questions 1) sort anything that looks like a number as a number 2) sort numbers and numbers stored as text separately
    I didn't see you do any of that. I am on Mac with Office 365. Thank you. If I want to buy things can I go to your website?

  18. Great video!! I own an Inventory auditing business in which we are constantly looking for a better way of comfortably taking item level counts. We even have a voice activate windows based system that works but with quirks. Could you please contact me to discuss equipment? Thanks! Scott G. 704-657-6152

  19. hola buen dia, te hago una consulta tengo una lectora symbol ds6878 la base enciede y luego de 2 segundos se apaga, sera q tiene que leer algun codigo para solucionarlo, saludos desde argentina

Leave a Reply

Your email address will not be published. Required fields are marked *