Stamp Inventory Spreadsheet – I have already received many mails from the contact form asking how Excel can be used to make inventory but without using the bar code. Of course, this post is for you if you have thrown me that question or if for some reason you need to know how to do your inventory in Excel but you do not have barcodes available.
By the way, some of you have asked me about the prices of bar code readers, since you have found yourself with quite high prices and without knowing which one to choose. Well, I’ll be putting comparisons and criteria to decide on the reader but, for now, I’m preparing a full analysis of 2 bar code readers that have a price below 20 euros, so you have no excuse;).
When you have to make an inventory of products without using barcodes to identify them, it can be only due to 2 causes: either your products do not have barcodes or they do have them but you do not use them. The truth is that it does not matter too much what the problem is, what really matters is that your inventory is going to be more laborious than the others. It will not hurt a little help.
What I have done in this post is to generate a template so you can make an inventory of products in Excel without using barcodes (with barcodes you already have it more than explained in this post).
I explain step by step how to make the template and also how with this same template you can generate barcodes for your products, so that the next time you have to take inventory, you have the help of the codes …
Chart of options for not using bar codes
As it is possible that your products are not encoded because there is simply no barcode that you can burn in your system, I have also prepared the template so you can generate a label with an internal code but encoded in bars, that is, if you sell comics old, without barcode, but your Action Comics nº1 of 1938 you call “AC1-38”, we will generate a label with the code “AC1-38” in bars, so that next time you can use a code reader of bars to read your articles instantly.
After explaining how to make the template, at the end of the post, I put a case study with examples and data so that there is no doubt about how the template is generated and used. I hope you find it useful.
Before I can see in detail what the template looks like and how to do it, I ask you to be clear about the reason why you will not use barcodes because depending on that, you can give the template a use or another. I explain:
At the beginning of the post I told you that there are only two reasons: either your products do not carry codes or they do carry but you do not use them.
It is clear that there are products that do not carry a bar code. Nothing to say.
On the other hand, if your products have barcodes, you will know why you intend to make an inventory without using them but in most cases it is due to a single main reason:
You do not have them recorded in the system.
Whatever the system is and for whatever reason. But you do not have them.
Well, simply, be clear about your specific motive so that you can use the template correctly. Let’s go to the mess:
An inventory in which you are not going to use bar codes is, basically, a list of your products with the annotation of the quantity next to each one.
I’m going to assume that you do not have advanced location management, that is, that each product is in a single place or location, so you only have to count it once.
The structure of the inventory template will be basically your own list of products but we will add a couple of spaces to record the quantities and barcodes so that, in case the product has a bar code, you can register at the time of inventory and so you can collect all the necessary data so that the next time you have to do inventory you can do it with barcodes.
Let’s go step by step and I’ll tell you how I’ve proposed it, to see what you think:
First step: loading the list of products in Excel
The first and most important thing is to indicate in columns A and B of Sheet1 the internal code and the description of your products, of all those that you have and that you have to inventorize.
Sample image of Excel sheet for inventory without barcode
In the image you can see that I have marked the columns where you have to put the information in blue and I have included 20 example products.
Immediately to the right, in columns C and D, I have left the space for you to indicate the units of each product (column C) and a free cell so that you can register the barcode of each product that you are recounting whenever the product have a barcode but you do not have it registered in your system. Later you can use the information in this column to incorporate it into your management system. Just give the titles to the columns and that’s it.
With respect to columns C and D you just have to keep in mind that it is best to mark them as unblocked if you protect the sheet. I explain:
Select both columns and go to the cell format (using the right mouse button) and uncheck the “Blocked” option in the “Protect” tab. This will, as you should know, be able to enter data in these two columns and block the rest of the sheet so as not to accidentally alter data while the inventory is being made.
Second step: print labels with internal codes
Well, once you have put your data in the blue columns of Sheet1 and you have marked columns C and D so that they do not get blocked (until now I have not used formulas or anything), go to Sheet 2 and fill in the columns A, B and C exactly with the same information we have in Sheet1, that is: internal product code, description and units.
I in column D have left a space of separation, you do it as you want, the important thing is that in the next column with data (in the case of the image, column E) add an asterisk at the beginning and end of the internal code .
For example, in cell E2 I used the formula:
= “*” & A2 & “*”
Detail of the sheet2 Excel
In this way, using only the Free 3of9 font, which, as we saw in the post about creating bar codes in Excel, encodes the text and numbers in Code39, we obtain a barcode but it represents the internal code of our product.
You can use this barcode as a bar code for internal use to take control of your products, so you will not have to write your name or memorize the internal codes for each time you want to refer to them.
In fact, you can print these bar codes and label the locations or spaces in which these products are found (or even the products themselves). Then we see an example at the end of the post.
Another thing that I added in column G (I have left a separation again in column F) is the barcode of the product that you collect during inventory but encoded in Code39. Basically I do it in case the problem you have with your products is that you do have a barcode but you do not have it physically in the place where the product is stored or exposed.
This case occurs a lot in shops and very small stores. For example:
In many cases, the products do not allow us to have a physical barcode even if we have it in the original container / packaging / case, as it can happen for example with products that are sold in bulk. This could be solved, for example, by adding a label to each product with the barcode or labeling the location where the product is offered in bulk but how to generate and print an EAN13 (or UPC) is very simple but generate and print 1,000 EAN13 codes can become a nightmare, often the products or locations remain uncoded.
What we get by coding an EAN or UPC in Code39 is to be able to generate it instantly in Excel with a simple formula and a typography. Simply print these codes and they will be perfectly readable by 99% of the barcode scanners on the market. I only remind you that this can be done for the internal management of your store or business, but it does not help you to codify the products with labels that will leave your company to reach the end customer (only for internal use).
Well, you know, the option you already have.
Third step: counting and collecting barcodes with the scanner
The third step is the most entertaining: you have to tell and “shoot”.
(In case you have had a problem with the reader’s connection, I leave the link to this post on the subject).
Here it is important to keep in mind that you are going to make an inventory count of one of the less optimal forms that exist but you have a great opportunity to improve your system: Take advantage of the fact that you will physically pass in front of all your products to collect the information you may need (such as the barcode of each product) or to label each item or location with additional information (such as the code39 tag I mentioned earlier).
The inventory can be a golden opportunity to collect information about your products that can be very useful and that you may not have introduced in your management system: the weight, the dimensions or the location of your products can be very important data. contribute to other processes within your company can be done much more optimized. In this way, the time you invest in making the inventory count will not be completely lost but will contribute to making the next inventories simpler and, with a bit of luck, to the fact that the work at some other point in the chain is also more easy.
This is the time to do it, if you do not do it during the inventory, you will not find “a hole” to do it until you take inventory next year (because if you do not register the barcodes in your system, you’re sure to be those who only take inventory once a year …).
Fourth and last step: use the information
Well, you would have finished the inventory and the most complicated part would be done but now you have to use all the information you have collected. If you like, we’ll look at a practical example and see how we can use the information but with concrete data. You think?
Final section: Practical example
Let’s go to the practical example so that everything we’ve seen is better understood:
Suppose you are in a company that consists of a store in which it is sold to the public and a store / exhibition with a large part of the assets for sale.
Let’s suppose that these are antiques that, of course, our ancestors did not deign to label with their corresponding bar code.
Suppose also that your stock should be:
1 old motorcycle
10 wall clocks
20 sewing machines
100 vinyl records
Somewhere, you should have a list like this, with all the products and their corresponding internal code (if for some reason you do not have an internal code, it would be enough to number the products from 1 to 1681 and assume that this is the internal code of each product, about internal codes we will discuss later, in future posts.):
Excel product list
We simply use this list to put it in columns A and B (simply copying and pasting) of Sheet1 (the colored part in blue).
Once we have the list of the 1,681 products in our Sheet1, we put the titles of columns C and D and unlock these same columns so that we can then write to them even if the sheet is protected. You only have to select columns C and D, click on the right button and access the cell format. Once there, select the “Protect” tab and uncheck the “Blocked” check:
Unlock cells in Excel
Just by having fed the codes and descriptions in Sheet1, you will have generated the barcodes with the internal code in Sheet2. Column E.
Internal code in bars format
You can print and use these barcodes if the problem you have is that your products do NOT have a barcode. Remember that these labels only serve you for internal use in your company.
Once you have printed the labels, if it is your case, we have to move on to the more laborious part: counting and scanning.
If your products had a barcode but you did not record this barcode in your system, in column D of Sheet1 you have the space to write down these codes while doing the inventory, taking advantage of the fact that you have the product in your hand.
If you do not have a barcode reader, I remind you, as I said at the beginning of the post, that it does not serve as an excuse for not doing this step, since these days I am preparing the comparison between two barcode readers that have a price below 20 euros and the results are being positive (Yes! There are at least 2 fully functional readers for less than 20 euros and they are totally useful for taking your inventory), so that excuse does not work for me 😉. I will bring all the results of the comparison to the blog so we can share them all.
Well, once all the antiques are recounted and hypothetical bar codes are entered, your Sheet1 should look like this:
Appearance of the sheet1
And Sheet2 should look like this:
Appearance of the sheet2
Obviously, depending on whether your products have codes or not, the appearance will be different but you can see that the template serves both to generate barcodes based on your internal product code and to code in Code39 a possible EAN or UPC code that can be Take your items and with what you can label a shelf or a location without problems.
Ok, now let’s see what we can do with all this information:
With columns A, B and C of Sheet 2 we have a complete list of inventory, with all the products we have and those that we do not have in stock.
With the columns A to G we can generate the labels with the internal product code but in bars
With column E we can generate a bar code with the EAN or the UPC of our product but in code39 format to label something like the shelf or the location where the stored product is located.
The sum of the units in column C must match the total stock that we think we have or that the computer system tells us we have.
If you use the VLOOKUP function as I explained in this video, you can compare the inventory information you just made (columns A, B and C of Sheet 2) with a hypothetical list of inventory extracted from your computer system to detect possible errors or losses of estoc that you will have to correct.
Later we will touch the topic of Excel sheets saved in CSV format but it is practically certain that if you save the data in columns A, B and C of Sheet2 of the template in CSV format, you can import them into your computer system and have the perfectly updated estocs.
Well, I hope you have been useful explanation and for any questions you know you can leave a comment and we will help you all.
As always, if you want the template already made and save the steps that I have explained, you only have to subscribe to the blog (although you also know that I encourage you to try to do it yourself). And 1000 thanks again to all of you who have subscribed! If I get to know that you are so interested in these topics, I start before with the blog …
Stamp Inventory Spreadsheet
Gallery of Stamp Inventory Spreadsheet
( Click Image to Enlarge )
- Medical Supply Inventory Spreadsheet
- Excel Spreadsheet For Warehouse Inventory
- Lularoe Inventory Spreadsheet
- Liquor Inventory Spreadsheet
- Bar Inventory Spreadsheet