Hello, today we’ll learn about how to make an inventory list on Google Sheets. An important task for a storekeeper is always keeping track of inventory and orders. The expense of employing a specialist to create an inventory list can also be high. So why don’t we create our own inventory list for the shop? Yes! Let’s get started with the instructions OfficeChaser has provided below on how to make an inventory list on Google Sheets.
Is Making an Inventory List in Google Sheets Preferable?
Google Sheets, a free web-based program that offers enough capabilities to create a professional inventory list, is the best option if you want a free inventory list. Google Sheets not only creates a unique inventory list but also automates it so you can quickly keep track of your goods and monitor your inventory.
You may also like>>> How to Pull Data from Website into Google Sheets [Data Scrapping]
How to Make an Inventory List on Google Sheets?
In Google Sheets, there is no technical way to create an inventory list; instead, logical expressions are required. First off, the contents of your inventory list are entirely up to you. I’ll provide you with enough information in the tutorial that follows to enable you to create an efficient inventory list for yourself in Google Sheets.
Step 1>
Create a structure for your inventory list in Google Sheets first, and then add headers for any items you wish to put in it. I’ve made the following columns for example.
Step 2>
We will initially add things with their codes in accordance with the information from my store since the first two columns of my inventory list are for item names and their codes.
Step 3>
After listing the items’ names, I’ll provide the quantity of each item, which you must manually enter by calculating the amount of stock left in your inventory.
Step 4>
I’ve included the maximum number of items that my store can stock in the following field so that I can make some calculations later. Once you have finished writing, these values will be fixed.
Step 5>
Now, there is a column for “Order’s Needs” in my inventory list, and by computing the record, Google Sheets will inform us how many quantities of the product are required to order.
Step 6>
Using the “Max” formula, where the first criterion is “Max capacity” subtracted from the “Actual quantity“, the value for this column can be calculated by simply subtracting the maximum quantity from the actual quality of the products. However, if the maximum quantity is exceeded by the actual quantity, the value for this column will be negative, as shown in the following image.
Step 7>
The second maximum criterion will be “0” once the first has been given, as is stated below. This will determine the amount of products that are required; if none are required, a value of zero will be displayed.
Step 8>
Press the Enter key after you have finished writing the syntax to obtain the final result. We only have 545 Hershey’s out of 800, as seen in the next image, so we need 255, as indicated below.
Drag the formula to further cells.
Step 9>
As shown in the table below, we have all the results. If you pay attention to row number 5, the quantity of Mars is overstocked, so the “Order Needs” field returns to “0” rather than a negative value.
Step 10>
Depending on your preferences, this column may be changed or added. I’ve used the “IF” function to specify the criterion in this case. If the quantity is less than its minimum of 10 items, or if it is less than roughly 7 orders, leave a blank cell. The following screenshot shows the syntactic pattern for you to see.
Step 11>
As you can see from the result for this column, if there are fewer than seven things, it automatically purchases ten more. In my opinion, you are free to bypass it or place more orders if you so choose.
Step 12>
When an order is placed for any item on your inventory list, you can check the number of items that will still be available in stock by looking at the “Order + Quantity” column, where I’ve added a simple sum formula to add the order items and quantity as shown below.
Step 13>
The Stock Status column is the following one in our inventory list, and we’ll use the “IF” function to apply three different criteria to this column. The first condition, “=IF (C2=0′′, “Out of Stock“, can be seen here. If the amount hits zero, “Out of stock” will be displayed in the cell.
Step 14>
In a similar way, I’ve specified in the second condition that “In Stock” will be displayed IF C2=D2 if the actual number of the products is less than or equal to the maximum capacity.
Step 15>
The third condition, for which I have written the syntax IF “D2<C2”, states that the stock will be displayed as “Overstocked” if it exceeds its maximum capacity, as shown in the following image.
Step 16>
When we press the Enter key, the result will be as follows. As you can see, where the stock is zero, it displays Out of Stock, when the stock is above the maximum capacity, it displays Over Stock, and the remaining areas are In Stock.
This makes it simple for us to keep track of which things are overstocked, out-of-stock, and in stock.
Step 17>
We should be aware of whether a product has been delivered to our store when we place an order. I added a column to my inventory list with the following formula as a result. If (F2=””,””,” “Pending”),
F2 is the value of the things that are in order. It will remain empty if there is nothing on order, but if we place any item on order from our inventory list, it will automatically show up as Pending in status.
Step 18>
As you can see from the result in the image below, the order status is “Pending” when placed in our inventory list, allowing us to identify which orders are Pending.
Step 19>
I will use some conditional formatting to create an inventory list that is more visually appealing and inventive. click the cells first, then click the “Format” tab from the menu bar to apply conditional formatting.
Step 20>
A drop-down menu will appear when you select the “Format” tab; select “Conditional formatting” from this menu.
Step 21>
I’m using a rule in the stock status column that says to fill a cell with the color green if the value is “In stock“.
Step 22>
Similar to that, I went with the red color for the out-of-stock values.
Step 23>
Fill the cell with the color blue as shown below if the value in the cell equals “Overstocked“. Once all the rules have been followed, select “Done” from the menu.
Step 24>
In this particular case, I’m also formatting the “Order status” column. Fill the cell with the color “Yellow” if it’s not already filled. When this is the case, the cell will be highlighted as yellow even though it should read “Pending“.
Step 25>
The following image shows how flawlessly all features and formatting are working and how much work I put into creating a functional inventory list in Google Sheets.
Frequently Asked Questions
Can I share my inventory list with team members or other collaborators?
Yes, sharing your inventory list with others using Google Sheets is simple. To ensure effective cooperation, you may control who has access to see, modify, and comment on the sheet.
What if I wish to monitor past information or adjustments to my inventory?
Version history is a feature of Google Sheets that lets you view earlier iterations of your sheet and follow changes through time. Maintaining a record of inventory changes is made easier by doing this.
Is using Google Sheets to store sensitive inventory data secure?
Encryption and two-factor authentication are only two of the strong security features that Google Sheets offers. However, it’s important to exercise caution when disclosing sensitive information and to carefully manage access permissions.
You may also like>>> How to Square a Number in Google Sheets [Multiple Methods]
Conclusion
Utilizing Google Sheets to manage your inventory will increase the effectiveness and professionalism of the business you run. Share this guide with them to ensure that your peers experience the same results. Keep learning with OfficeChaser for more tips and tutorials to improve your digital skills!
