(This guide assumes you already have an excel sheet to track your inventory in. Don't have an excel inventory sheet yet? No problem! Just join our mailing list by clicking here. After you join you'll get an email with a download link to your free file. There's no commitment, you can unsubscribe at any time.)
Microsoft Excel or OpenOffice Calc
The first step to managing your inventory with this free inventory Sheet is to open it! That can be easier said than done, however. To open this spreadsheet, you'll need one of two programs. The most common program is called Microsoft Excel. This is where the name "Excel spreadsheet" comes from. Microsoft Excel has been the defacto spreadsheet program for nearly 20 years, and forms part of the Microsoft Office suite of software.
Microsoft Office and Microsoft Excel are premium software products that you must purchase. That means that Microsoft Excel is not commonly found on most computers and laptops, and you'll have to shell out a modest amount of cash to purchase it.
Luckily, there is a free and open-source spreadsheet program that has most of the benefits of Microsoft Excel. That program is called OpenOffice Calc, and forms a part of the Apache OpenOffice software suite. OpenOffice includes many other Microsoft Office alternatives, such as OpenOffice Writer (Microsoft Word).
Because OpenOffice is free, we recommend that you use it with this spreadsheet. Additionally, the rest of the course will use examples based on the OpenOffice version. However, don't worry if you're using Microsoft Excel as these programs are very similar. If you don't have OpenOffice, you can grab it by clicking this link.
Opening your inventory sheet
Once you've got your favorite spreadsheet program ready to go, click on the inventory file you downloaded. Remember, if you haven't downloaded the file yet, it should have been sent in an email from us shortly after you joined our mailing list.
If you're using OpenOffice, you should immediately see a screen like this:
It's very important that you ensure "Comma" is the only thing checked (highlighted in orange in the image). Once you've ensured that just "Comma" is checked, go ahead and press the OK button. Your free spreadsheet should have just opened. Looks pretty confusing huh? Don't worry, it's easier than it looks.
Using your inventory spreadsheet
Now that you know which program to use and how to open your Inventory Sheet, go ahead and fire it up. You'll notice that the Sheet you downloaded came with several placeholder items, to give you an idea of what to place in each column.
For you absolute beginners, let's go over what a cell, a column and a row are. A Cell is the building block of a spreadsheet. Each cell holds one piece of information in a column and row. For example, the box that says "STRASKU" is a cell. The box that says "A Red Strawberry" is another cell.
A column is the vertical section of cells. For example, the STRASKU, POMESKU, DRAGSKU, CHERSKU and KIWISKU placeholders are all part of the SKU/Barcode/SerialNumber column. Not sure what an SKU is? We'll go over that, and a few other best practices for inventory management if you're on the mailing list (again, sign up for the mailing list right here).
A Row is the horizontal section of cells. In this case, each Row is a different item or part. For example, The STRASKU is "A Red Strawberry" with a unit price of "0.5" (50 cents) and a quantity of 50.
Okay, we've got the basics covered. Let's look at using the spreadsheet.
Adding an item
First off, let's add an item. Keeping with the theme of our Sheet (food products), let's say we just received a shipment of 50 blueberries. Adding an item is, thankfully, very simple. All you need to do is click an empty cell and fill in the information.
Click the first empty cell in the SKU/Barcode/SerialNumber column, and enter BLUESKU. That's the blueberry SKU, and again we'll cover what an SKU is and why you should use it two days from now.
Now if you're still editing the cell, you can press tab and your cursor will automatically move one cell to the right. If not, just click the next empty cell in the row, not in the column.
You should now be adding the Item/Description cell for our Blueberries, so fill it with "A Bushel of Blueberries". Press tab to move over to the right, and enter 0.25 (that's 25 cents per blueberry). Press tab again, and enter a quantity.
Awesome! Now you've got your Bushel of Blueberries item added to your sheet. That was pretty simple. Your Inventory spreadsheet should now look something like this:
Editing and deleting
Editing and deleting an item is just as simple. To edit an item, just click on the cell you want to edit and start typing. To delete an item, all you have to do is click on the cell and press the "Delete" key on your keyboard (Sometimes the delete key just says "Del").
Excel Formulas and Macros
You may have seen or heard of people using formulas and macros to calculate totals and other things. While Excel is capable of doing that, it takes minor programming and mathematical knowledge, and is best left to engineers and accountants. Therefore, it is beyond the scope of this educational series of emails and will not be covered.
If you need this functionality, I recommend you look into actual online inventory or ERP (Enterprise Resource Management) software that will do this for you.
The drawbacks and pitfalls of Excel
Most businesses will quickly move on from using Excel spreadsheets to track their inventory, to using dedicated programs and software. Let's go over a few of the most common reasons that will cause a business to make the switch from Excel spreadsheets to a dedicated inventory management solution.
No real-time support
Microsoft Excel and OpenOffice Calc spreadsheets are both local files. That means they're stored directly on your computer instead of being hosted online. That makes it completely impossible for an Excel sheet to be shared and updated in real-time. Therefore, keeping every location and necessary employee up-to-date with the latest information simply cannot happen.
The best you can do is emailing your file between different users as they make changes. That in itself will introduce other headaches: How can you know which file is the most up to date? Which count is accurate? Unfortunately, most inventory programs themselves cannot do real-time inventory either, because they rely on importing and exporting Excel files.
Online inventory management software and programs are able to overcome this flaw by using modern browser technology that most locally-installed software simply cannot do.
Corrupt Excel files
Lots of seasoned business owners have suffered huge productivity hits when they're hit by this infamous message:
There's not much you can do about a corrupt Excel file, except pray that you had a relatively recent backup stored somewhere. A corrupt Excel file means that all of that data has been lost. If you didn't have a recent backup, your inventory is no longer accurate and off by days, weeks or months.
Again, many inventory programs these days rely on Excel files. If you get hit with a corrupt Excel file, even those programs cannot save you.
However, dedicated inventory apps can once again circumvent this flaw by using cloud technology. Most of them do not rely on Excel files at all, and instead use cloud databases. That completely removes the threat of a corrupt Excel file. You can sleep easy, once again!
No Bill of Materials
You might be wondering, "What is a bill of materials"? Think of it like this: Your business makes and sells cakes. But you don't get these cakes pre-assembled, you make them from scratch. Let's say every cake takes 1 egg, 1 cup of flour, and 1 cup of milk (I wish!). Now instead of subtracting those three ingredients every time from your inventory sheet, you could instead just subtract 1 Cake. That would automatically deduct the three ingredients that make up a cake.
Sounds like a great feature, right? Unfortunately, without proper programming knowledge, this is completely impossible to do in an Excel spreadsheet. Opening up your file and saying "Okay, time to script up some Bill of Materials macros" is far easier to say than actually do.
Luckily, a Bill of Materials is a core function of every inventory or ERP program. Yes, even those that aren't online!
Thanks for reading!
I hope you can walk away from this article knowing how to manage your inventory using Excel spreadsheets. But keep in mind: Excel can't manage your inventory forever without significant risks.