How do I do basic inventory management in Excel?
By: POSGuys - 1/4/2015

![Converting a list of part numbers to organized columns in Excel][1][600][]

[1]: https://posguys.blob.core.windows.net/content/images/blog/9275/illustration.png

A frequent question we receive is how to use Excel for inventory tracking. Ask POSGuys shows one way to do that by scanning your barcodes and then applying a few Excel formulas to clean up the data. We definitely recommend this as a tool for supplementary use with other point of sale or asset tracking software.

Video Transcribed Below

Hi this is Travis with POSGuys.com. Today we're doing a very simple demonstration of how to do some asset tracking. Basically I'm going to scan our breakroom and a bunch of food items we have leftover from the holidays in our break room. I'm going to add them to just a Word document, actually a Notepad document using a CS3070 and a Microsoft Windows tablet. We're just going to scan straight into the document. I'm then going to take that document and put it into Excel and then I'm going to run some functions on it that will parse together the data and allow you to have a count of each part number in it. You can then take that part number and import that into your point of sale software or your asset tracking software and you'll have updated totals on your inventory.

So lets get to it. From Windows I pulled up the desktop. I have a basic notepad application running and then I have a CS3070 and I have this in Bluetooth mode. I'm going to use it to do keyboard wedge entry. You can actually just use this and do a straight batch mode and then take the file off of your memory scanner and bring that into Excel as well but for the purpose of this demonstration I'm going to use it as Keyboard Wedge. You don't have to have a Bluetooth scanner, you could use a corded scanner. You don't have to have a tablet you could just use a corded PC as well. These are all just kind of low cost options and you might already have the hardware needed to do some basic asset tracking. So going forth I'm going to take the 3070 and I'm just going to start scanning. I'm going to scan everything once. So as you notice each time it scans it hits a carriage return. So I'm just going to go through and scan everything. And then for the sake of this demonstration I'm going to scan a couple of the items again to basically represent having multiples of the same item. So that is it. What you'll have is a text file of just the different SKUs or UPC codes of the items and we're going to bring this into Excel and create an asset file with that.

Ok here we have that file that we created on the tablet. Ibrought it over into my desktop computer and lets look at what we have. A bunch of scans that are just all carriage return delimited. I'm going to ctrl+a and then I'm going to 'Copy' them all and then I have Excel going here. I have a brand new spreadsheet. Select the first column and 'Paste' them in. So there we can see all of the values there. I'm just going to name this column, call it 'Part Numbers'. So we have the list of all the part numbers that we scanned. That is useful in that we know what all the part numbers are but we don't know how many we have so what we're going to want to use is a couple of formulas that are built into Excel. I'm going to highlight the column. I'm going to go over here to 'Formula' and what you are looking for... actually I'm going to go into 'Data' is this 'Subtotal' feature. When you click 'Subtotal' it is going to bring up a prompt and ask which columns you want to do and it is going to ask if you want the 'Sum'. What you actually want to do is 'Count'. You want to count the part numbers. 'Replace current subtotals' we haven't done subtotals so it doesn't matter. 'Summary below data' um that is fine. So we say 'OK'.

So now what that did was it created a new column here and so there is our part number. It says 'Count' and then what happens in this column 'B' is how many we have of each one but if you look at it one thing that won't make sense is that you'll see part numbers that we've already scanned and the count will be '1' where we had more than one scan. So there is one more step that we need to do. So I'm going to reverse this and hit 'Ctrl + z'. We're going to go back to our overall listing before we do anything we're going to need to 'Sort' our data. So up at the top of Excel there is your 'A-Z Sort'. So we're going to run our subtotal function again. Our column is highlighted, we hit 'Subtotal'. 'Part Number' selected. If you leave it on 'Sum' here instead of 'Count' when you hit okay it will appear to work except that when you look at your count it is going to be funny. Instead of having like two, three, four, five or whatever number you have you see a value. If you do that on accident it is no big deal just 'Ctrl + z' and get out of it. Hit your 'Subtotal' function again and then just remember to hit 'Count'. Okay so now if we check our counts it looks like what we expected to see. We have some part numbers that we scanned four times, one time, two times. Those are the numbers I would expect to see. So we have a sum here which is useful but we can't import this into any kind of software at this point. So we'll need to do grouping.

One nice feature of Excel is that they do that for you. You see these three buttons that have appeared here? It uh collapses the data. If you hit number three it shows everything. If you hit number two it collapses it down into just the part numbers that we want and if you hit 1 then we only see the count for everything. So this shows we have 39 items total. So I'm going to expand this out and what we're going to want to do is take the data from just these sells. If we cut and paste like this into a new spreadsheet it is going to take all of the data. So I'll just show you. So we copy, we add a spreadsheet, and we paste. So it brings in all the data. That is not quite what we want. Clear that out. What you can do though is highlight the fields that you want and if you hit F5 there is a 'Go To' option. Click the 'Special' button and then you're going to do 'Visible cells only' and if you do that then it only selects what you see on the screen. Copy that over. Go to our new sheet.

You can 'Select All' and hit 'Paste'. There we have all of our items. A couple of house keeping type of tips. If you look at your cell here it has the part number and then the word 'Count'. You can easily replace that you just do a 'Find and Replace'. So we're going to do 'Ctrl + f" and that will bring up the dialogue for find and replace. What you're looking to find is the word 'Count' and you're going to replace with blank if you do that. Go to 'Replace All'. It will tell you that it has replaced it 21 times and then removed that word. So it is just the part number and then it is just the quantity. Additionally you will see a couple of fields that are in scientific notation. If you click on the field it is the actual part number so when you export it out you are not going to see it that way but if it bothers you, you can go to 'Format Cells' and set it as 'Text'. Say 'OK' and then you've kinda got to double click the cell but once you do so then you don't see it in scientific notation anymore. So that is basically how you create a part number and quantity spreadsheet.

With these spreadsheets you import it into your point of sale software so you can update the quantities that you have in stock, you can also get the total value of all of your inventory. This of course is only the part numbers and quantities. It doesn't have the fields like cost, price, or description. That information you're going to have to get out of your point of sale software or you're going to have to use a program like Access to access the database directly and then import in your new inventory values and when you look at them together you'll have a total with a total value using that information. So this has been a basic, basic information on how to put together an inventory spreadsheet. For more information please visit us at POSGuys.com. Thank you!

End Video Transcription

Featured Scanner: 
<a href = "https://posguys.com/mobile-barcode-scanner_67/Motorola-CS3070_1262/" title="Symbol CS3070 Bluetooth Scanner">Symbol CS3070</a>

Featured Software: 
<a href = "http://office.microsoft.com/en-us/support/getting-started-with-excel-2013-FX102827997.aspx" title="Microsoft Office Excel 2013">Microsoft Excel 2013</a>

Got a question for the Guys? Email your own point of sale question to <a href="mailto:asktheguys@POSGuys.com" title="Email Ask POSGuys a question and you may end up as our next featured question.">asktheguys@POSGuys.com</a> or leave a comment below!

Share this post:

How do I do basic inventory management in Excel? By: POSGuys - 1/4/2015

Recent Posts By POSGuys

How do I do basic inventory management in Excel?
By: POSGuys - 1/4/2015