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!
This Google Sheets inventory 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.
Download the Free Inventory Template Google Sheets File
Use the above link to download our easy-to-use template. Read on below to learn how to use the inventory template sheets file. If you don’t need calculations done for you, check out this simplified inventory template that doesn’t need any explanation or this inventory template that ignores product cost. Lastly, we also have a check-in, and check-out inventory template for schools, libraries, or other workplaces that check items in and out to keep track of who has which product or item.
Table of Contents
What Is a Google Sheets Inventory Template?
Not to be confused with a project management template. An inventory template will easily help you keep track of your incoming and outgoing stock. It does so through the manual entry of data accompanied by preexisting automatic formulas that fill other zones in the spreadsheet.
Keeping track of your inventory helps you prevent stock shortages for fast-selling items and also helps to prevent over-ordering stock that may not be selling as expected. They can also help to identify products lost through theft, workplace accidents, and shelf life expiry.
Inventory management is an important part of many industries, including:
- Logistics
- Retail
- Education
The beauty of using a Google Sheets inventory template is that it is free, and the work has already been done for you.
Our main inventory Sheet template performs calculations based on product cost and maximum stock levels, but sometimes you won’t need to use these, such as when performing a quarterly stocktake. That’s why we’ve also included a simplified and cost-free template too.
The last of the Google Sheets inventory templates we’ve included in this article is a check-in and check-out one. It has a simple checkbox column to identify whether or not a product is available to check out and columns for people’s names and expected return dates. This is perfect for any situation where items have to be borrowed by individuals. Some good examples would be a school music room where students can borrow instruments or a construction site with several contractors that need to use the same equipment.
What Data Should an Inventory Template Contain?
It depends on what type of inventory management template you need. If we are talking stock for sale to the public or as part of a logistics chain, you’ll need to include the following inventory details:
- Item numbers
- Item or product descriptions
- The level of current stock
- How many of the products are currently on back order or in transit
- The max capacity for the product in store or in the warehouse
- Price per unit
Then, if you’d also like to include calculations based on those inputs, you could use the following:
- The sum of the current stock plus those on order
- The cost of the current order yet to arrive
- The total cost of all the stock
For other purposes, you may not need so many fields in your spreadsheet. Often, a simple inventory list will do. This is true for stocktakes, lending, and identifying lost or stolen items.
There are also plenty of other things you may want to add to a inventory template in Google Sheets, and that’s the beauty of this program, you can customize spreadsheets as much or as little as you’d like.
To make simple customizations to our template, you can simply add more columns. To do so, you just have to right-click the right-most column and select + Insert 1 column right.
You can follow similar steps to delete columns from your inventory management spreadsheet that your business doesn’t need. Just right-click the column header for unwanted columns and select Delete column.
Related Reading: Google Sheets Profit and Loss Template
Using the Google Sheets Inventory Template
The below guide is for our comprehensive inventory management 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. The three other templates we included at the start of the article are even easier to use and shouldn’t require a guide at all.
Where to Input Data Into the Inventory Spreadsheet Template
You’ll notice that all of the sections of our free inventory spreadsheet template you have to make inputs in yourself are highlighted in yellow.
And the inventory tracking 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 in our Google Sheets template for inventory, you must 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 inventory management column 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 Google Sheets inventory management template. 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.
Related Reading: Google Sheets Task List Template
Frequently Asked Questions FAQ
Is There an Inventory Template in Google Sheets?
Yes, you can find our inventory template in Google Sheets 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?
If you have a massive inventory list, 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 or 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?
Not just a Google Sheets inventory template, but 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.
Can You Scan a Barcode Into a Google Sheet?
Yes, you can scan barcodes into Google Sheets. Most USB barcode and QR code scanners won’t need any additional steps to place the codes generated from the scanner into Sheet. You simply have to select the cell you wish the data to show up in and then scan the barcode. In most cases, Google Sheets will automatically move on to the cell below so you can easily scan several items in a row.
But, many product codes start with leading zeros, which Google Sheets sometimes ignores. In such cases, you may need to format the cells as plain text before you begin by navigating to Format > Number > Plain text.
You can also add barcodes to a Google Sheet to help with stocktaking. It would be simple to add the barcodes to our inventory template if you wanted to.
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 also: Google Sheets Expense Tracker
