Standalone inventory software can cost an absolute fortune to buy, and with the way the software market now leans toward SAAS, you could be paying those fees forever. Google Sheets to the rescue.
Google Sheets is an extremely useful tool for many reasons. Among those reasons is the fact that it’s free. The beauty of it is, you don’t even need to be a whiz at Google Sheets to make the product work in your favor. You can just use templates. And, when it comes to keeping track of your inventory, we have you covered with this Google Sheet Inventory Template specially made for 2022!
This template is fully customizable, can keep track of any kind of inventory, and covers everything from small shops to warehouses. If you have no outgoing digital products, it could even just keep track of your office supplies. It’s really up to you.
Use the above link to download our easy-to-use template. Read on below to learn how to use the inventory template sheets file.
Using the Google Sheets Inventory Template
This template is straightforward to use and set out in a way that makes it difficult for you to interact with it poorly and break its functions and formulas. There’s also color coordination to read at a glance and notify you where to make adjustments to your stock.
Where to Input Data Into the Inventory Spreadsheet Template
You’ll notice that all of the sections that you have to make inputs in yourself are highlighted in yellow.
And the sections that have existing automatic functions are highlighted in purple.
If you try to edit any of the purple columns, you will get a warning pop up on your screen, just to make sure you really want to make changes to the cells. Although, we would recommend that you check the inputs in the yellow columns are correct before you make any changes to the purple ones.
To make changes to the purple cells, you have to check the “Don’t show this again for 5 minutes” box before clicking OK.
How to Input Data Into the Inventory Spreadsheet Template
Let’s break down what information you should enter into each of the columns to get accurate results.
Column A – Part or Item Number: This is where you enter the product or stock id to make ordering easier
Column B – Item Description: The common product name. Depending on your business, it may be best to use the brand and specific item like 600ml Diet Coke 6-Pack instead of Coke Bottles Pack.
Column C – Current Stock: How many of each item you currently have in stock
Column D – On Order: When you first start using the spreadsheet, you may have backorders that haven’t come through yet. Make sure you add them here so you don’t end up with double orders.
Column E – Max Capacity: How many of the items will fit on your shelves and in your storeroom(s) combined.
Column F – Price Per Unit: It’s up to you whether you want to enter the cost per deliverable amount or per single item. For example, if a product has a minimum order of a pallet of 24, you could choose to input the cost of the pallet or the individual product. Just make sure you account or this either way in the Current Stock and Existing Order columns too.
Column G – Last order Date: The date of the most recent order you placed of the item so you can track if it’s late
Calculations the Inventory Spreadsheet Google Sheets Template Completes
Now let’s take a look at what the purple section of the spreadsheet shows when you’re done with inputting the data into the yellow columns.
Column H – In Store + On Order: Pretty self-explanatory—this column automatically calculates the total inventory, whether it’s in transit or in-store.
Column I – Stock Status: This column takes data from column C to show whether a product is in stock or out of stock. It does so by using an IF function to show “In Stock” if the value in the C column is greater than 0. This column uses conditional formatting again to make it easier to see at a glance.
Column J – Overstocked?: Another column with conditional formatting to easily show whether there is too much of any one product in your store.
Column N – Cost of Current order: This calculates the product of the cost of each item by the amount on order.
Column O – Total Purchase Price: This column takes the current stock + on order stock from column I and multiplies it by the product cost in column F to show the total purchase price of the existing store stock for any one item
Customize the Free Inventory Template Google Sheets File
There are quite a few columns in this inventory spreadsheet. If you don’t need all of the data in your copy, you can simply delete the columns you don’t need. However, this may break the sheet. So, you’re better off hiding the columns you don’t want to use. You can do this with the following steps:
- At the top of the column, right-click the column name (A, B, C, etc.) or left-click and drag over them if the ones you want to hide are next to each other, then right-click the highlighted column names.
- Click on Hide columns in the menu that pops up.
Inventory Spreadsheet Google Sheets FAQ
Is There an Inventory Template in Google Sheets?
Yes, you can find it by following this link. The guide above will tell you exactly how to use it.
What Do I Do if I Run Out of Room in the Template?
You can simply make another sheet by right-clicking on Inventory Page 2, then clicking Duplicate. This will make a new sheet, you may have to rename it and clear any items that were copied over.
How Do I Make an Inventory Spreadsheet in Google Sheets?
If you don’t want to use a template, you can build your own inventory Google Sheets spreadsheet. You’ll just have to be familiar with ARRAY formulas or IF functions. You can peek into the formulas we used in our template to get an idea.
How Do I Create a Spreadsheet Template?
Every spreadsheet you’ve ever created can be a template. All you have to do is navigate to File > Make a copy and there will be an exact copy of the spreadsheet. So, to make a template, enter the formulas and formatting you want, and make a copy using this method each time from that source file.
Track Your Stock for Free With an Inventory Spreadsheet Template for Google Sheets
If you missed the download for our free Google Sheets inventory template, you can find it here.
We hope that our simple inventory template saves you plenty of time and money and helps you to run your business with ease.
Let us know in the comment section if you have any questions or what other templates you want to see next from us.
The author of this article conducted extensive research before putting this article together. One of the websites he got inspiration from is Spreadsheetclass.
Check aslo: Google Sheets Expense Tracker