How to Track Inventory in Excel with a Barcode Scanner
Articles, Blog

How to Track Inventory in Excel with a Barcode Scanner

November 16, 2019


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.

70 Comments

  • Reply Mit Patel November 20, 2016 at 8:20 pm

    hi, how do u add more (same) items, having the same barcodes?

  • Reply Yessenia Gonzalez December 19, 2016 at 10:01 am

    Is there any video you could suggest for an inventory that isn't a count. I need to use excel to track individual barcodes against a printed report and they are all different.

  • Reply Carmen Lopez December 27, 2016 at 12:02 am

    What is the scanner called? And how can I order one?

  • Reply RTjman December 27, 2016 at 4:48 pm

    Great video and very well explained, this will help me at my job. Thanks for uploading!

  • Reply Abu Ahmed December 31, 2016 at 8:56 am

    very good lesson and fine explanations, thanks for all you help Sir.

  • Reply Saqib January 2, 2017 at 3:08 am

    I was put forward for a role within the Processing area which will consist of data inputting, unpack aging, repackaging, use of handheld scanning guns, manual stock movement. Is this the data inputting part? First time warehouse work.

  • Reply Mud Prince January 3, 2017 at 9:46 pm

    interesting information, what if you wanted to scan the price tag along the barcode

  • Reply Joanne Kaighn January 12, 2017 at 12:40 pm

    How do you connect the scanner to Excel?

  • Reply Erika Montalvo January 20, 2017 at 6:13 pm

    thank you for the video, Do you know how can I modify the scroll on the scan? I need to move to the next column either row.
    Help please!!! I though to do it by VB on excel but I don't have the code. thanks

  • Reply aiinad January 25, 2017 at 11:49 pm

    Hello, Do you know if the barcode actually contains information about the product (in this case vitamin, brand X, product Y) or is it that the barcode needs to be assigned to each of the manufacture's database (for logistics purposes maybe) and also store's database to track inventory and assign prices?

  • Reply Lesley G. January 26, 2017 at 4:15 pm

    Hello,
    Does it only scan barcodes or can it scan serial numbers as well?

  • Reply A Syl January 31, 2017 at 10:28 pm

    @POSCatch.com Is there any way to scan items out of the list in Excel? After I have scanned them in How will i know how many have been taken out of the inventory batch? Also do i have to use this particular brand of barcode reader or will any barcode reader do?

  • Reply Gizard1000 February 1, 2017 at 2:52 pm

    I am based in UK and I like your inventory video, it is something I think I might us. How do I get my scanner is connected to my PC.

  • Reply Kwik Retail February 5, 2017 at 7:58 pm

    Great

  • Reply Steve Chalom March 2, 2017 at 4:15 am

    Hi there, and thanks for the great vid. I have been tasked with taking on stock items specifically old comic books. There are 1000;s of items, and I have to capture all details (eg isbn,title,price) is there a way to scan these directly into excel, or do i need POS software? thanks

  • Reply Briana Cox March 6, 2017 at 12:23 am

    i am applying for a scanner coordinator at a grocery store. What advice can you give me so that I can be successful at it. I want to know what tools do I need, and etc

  • Reply arms drin March 7, 2017 at 6:18 pm

    Hi…. Can we scan only numbers ?? thanks and appreciate

  • Reply Curt Hodapp March 28, 2017 at 5:12 am

    Thanks, Saved me a lot of work.

  • Reply Rivenworld April 10, 2017 at 6:59 pm

    Brilliant, thanks, at last I can now use my scanner… 😉

  • Reply Mike Decker April 17, 2017 at 6:31 pm

    What if I want to use a scanner to take things out of an inventory?

  • Reply bijaya rai Bizeya hh wau nice umma May 13, 2017 at 10:54 pm

    must needded..for me…nicc

  • Reply chandra dwi putra June 6, 2017 at 5:21 am

    how to make excel auto enter when do scanning barcode ?

  • Reply rajiv sen June 25, 2017 at 1:46 pm

    hi, i am suffering from a problem i.e. after scan a barcode in excel sheet it is enter twice like if the cursor is on A1 and after scanning of barcode the cursor will be gone on directly to A3. i want to use it as only one enter. Please help

  • Reply Sapna Mehta July 14, 2017 at 3:15 am

    Thanks for making it so easy

  • Reply ALAM , BKK July 14, 2017 at 2:47 pm

    very goods

  • Reply falak international nagpur July 26, 2017 at 4:25 pm

    how to link my barcode scanner to excel

  • Reply Sadaqat ali August 24, 2017 at 12:47 pm

    if i enter manually barcodes in this sheet than the sheet manually entered barcodes are showing in one by in diff column, so kindly help me if i scan barcodes with barcodes scanner or manually barcodes enter quantities showed in a column.

  • Reply LAZY LIFE SEATS & COVERS September 13, 2017 at 1:17 pm

    We have been trying to find a solution for tracking our work orders as they go through our manufacturing process. Basically, we would like to use bar code (generated from order #) to track when the order leaves the following stages: Order Entry, Cutting, Sewing, Assembly, Shipping. Your video intrigued me because it's somewhat headed in the right direction for us. However, I would like to capture the date of the scan. And possibly need separate scanner set up for each stage. Thoughts or recommendations?

  • Reply Soumya Roy October 2, 2017 at 3:00 pm

    How can i create barcodes in MS Excel?

  • Reply Muhammad Hammad October 19, 2017 at 5:29 pm

    In our inventory we have a scanning sheet on which gives the type, name, size, color and code of the product on scanning. How are we getting it?

  • Reply Abdelrahman Refaat November 17, 2017 at 10:06 am

    my excel cant read barcode scanner

  • Reply MUNBYN POS December 6, 2017 at 5:04 am

    Very good training. THANKS.

  • Reply Austin Semeta December 10, 2017 at 1:36 am

    Is there a system that can do count while recording what time it was scanned too?

  • Reply AMIN khan December 20, 2017 at 12:42 pm

    Hello I m working as store keeper in mobile company and I m scanning every day around 20000 imei# with scaner datalogic and I don't know how fix the formula for qunting the imei#plz tell me how I can fix that for example if I scan 40 imei# that file on top showing 30 only .

  • Reply It's Me December 22, 2017 at 12:05 am

    How to scan left/right then down back to left/right because c'mon man

  • Reply Kirsten Turner January 4, 2018 at 2:03 am

    thank you for the info and helping out a small business!!!!

  • Reply AMIN khan January 11, 2018 at 7:29 pm

    Hello good evening I want to scan mobile phone with barcode how I can do for expmale I have one box 20 pcs how I can scan that plz help me.

  • Reply Norman Bataille January 15, 2018 at 1:00 pm

    HOw can I make in EXcel a check in and check out system and a list of user , example to assigne a device witha barcode to an user?

  • Reply Muhammad Hammad February 6, 2018 at 10:30 pm

    what if we need to have the name of the product rather than numbers

  • Reply Evolve Arts MA February 13, 2018 at 5:16 am

    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?

  • Reply bam4pro February 19, 2018 at 5:24 pm

    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

  • Reply Ernie Zamora February 24, 2018 at 2:37 am

    Which scaner gun are you using

  • Reply Jennifer R March 20, 2018 at 9:50 pm

    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 #

  • Reply Ahmad Daud March 22, 2018 at 11:13 am

    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?

  • Reply Jennifer R March 25, 2018 at 6:50 pm

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

  • Reply Boldzest March 28, 2018 at 2:40 pm

    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

  • Reply Alyvia Steenhoek March 29, 2018 at 3:36 pm

    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?

  • Reply Kumar Shrestha March 30, 2018 at 7:58 am

    U don t upload the video how these barcode delete process. Because i m in problem like this

  • Reply John Damage April 12, 2018 at 8:43 pm

    Just what i was looking for, thanks

  • Reply John Damage April 13, 2018 at 6:18 pm

    How would you scan out? Please

  • Reply Noor Ali May 5, 2018 at 4:58 pm

    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.

  • Reply tEiNe SaMoA June 6, 2018 at 5:36 pm

    How about if you want to count these items by cases or does it only counts it by eaches?

  • Reply Abbott Katz June 25, 2018 at 2:58 pm

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

  • Reply Nathaniel Montgomery, Sr. July 15, 2018 at 11:30 pm

    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.

  • Reply vishal k choudhary August 4, 2018 at 2:25 pm

    You make the super video I like it video

  • Reply BLACK CHERRY August 25, 2018 at 1:23 pm

    cheap quality dependable office scanner … https://amzn.to/2oa4ypY

  • Reply Bam Bahadur September 17, 2018 at 12:34 pm

    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.

  • Reply Div Jangid October 8, 2018 at 1:32 pm

    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

  • Reply Shakil Khan October 10, 2018 at 12:13 pm

    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

  • Reply Selvakumar M.G.R November 2, 2018 at 7:04 pm

    thank you so much boss

  • Reply Boudoir Bizarre November 3, 2018 at 12:12 am

    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).

  • Reply Don Mega November 10, 2018 at 3:51 pm

    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.

  • Reply Nard Cox November 17, 2018 at 8:47 am

    Just use a pivot… And a helper table with barcode and name… This is absolutely no way to keep track of inventory…

  • Reply Sean Hayes Love one another. As I have loved you, January 17, 2019 at 10:45 am

    How to remove the count from the code

  • Reply periklis z February 14, 2019 at 6:46 am

    Can we do that in an android mobile or tablet? I can't find the subotal function in the android excel

  • Reply Jacob Youngpeter February 19, 2019 at 11:12 pm

    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?

  • Reply Rick McCulloch May 12, 2019 at 8:41 pm

    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?

  • Reply Goodman Beach House May 27, 2019 at 12:24 pm

    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

  • Reply Global Print Connection June 21, 2019 at 3:59 am

    how to insert 10,000 data different Qr Code Help Me

  • Reply geovanny carvajal July 25, 2019 at 4:22 pm

    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