How to Maintain Store Inventory in Excel (Step by Step Guide)

To check the performance of your business, you might have needed to maintain store inventory. It’s quite difficult to handle such a type of report manually. Rather, you can do it in Excel efficiently. In this article, I’ll show a step-by-step guide on how to maintain store inventory in Excel with a proper explanation.

At the end of this guiding session, you’ll be able to make the following store inventory report.

However, you can also download the template for your convenience.

How to Maintain Store Inventory in Excel: 3 Steps

Let’s say, you have some Fruit Items with their Product ID in the Main Stock sheet.

Now, you need to prepare a store inventory report based on the Incoming Stock and Outgoing Stock sheets.

Step 01: Deal with Incoming Stock List

In the Incoming Stock sheet, you might have Date, Product ID, Fruit Items, and Quantity fields.

So, you need to create a drop-down list for choosing the Product ID. To do that, do the following tasks.

• Firstly, select the C5:C16 cell range.
• Then, go to the Data tab > Choose the Data Validation option.

• In the Data Validation box, choose the List from the drop-down list under the Allow option.
• Lastly, specify the Source as =’Main Stock’!$B$5:$B$16 and press OK.

Eventually, you’ll get a drop-down list of Product IDs as shown below.

Next, you need to use the following formula to return the name of Fruit Items based on the C5 cell.

Here, C5 is the look-up value, ‘Main Stock’!$B$4:$G$16 is the table_array, 2 is the col_index as Fruit Items is located at the 2nd column based on the Product ID field in the table, and lastly FALSE is for approximate matching.

⧬ Formula Explanation:

In the above formula, the VLOOKUP function returns the Fruit Items from the Main Stock sheet according to Product ID. Besides, the IFERROR function is used to avoid the #N/A error.

So, if you pick any Product ID e.g. 1001-P2 from the drop-down list, you’ll get Apples in the D5 cell.

Step 02: Deal with Outgoing Stock List

In addition, create the same fields in the Outgoing Stock sheet.

Next, generate a drop-down list in the C5 cell as done in the Incoming Stock sheet. Also, copy the VLOOKUP formula (by pressing CTRL + C) from the sheet and paste it (by pressing CTRL + V) into the D5 cell of the current sheet.

Step 03: Maintain Overall Store Inventory Report in Excel

Then, you have to prepare the Main Stock sheet which will be updated automatically based on the entry of the Incoming and Outgoing Stock sheets.

For example, you may use the SUMIF function, a popular function for calculating the sum value from different aspects depending on criteria. So, the formula will be like the following in the D5 cell.

Here, $D$5:$D$16 and $E$5:$E$16 are the cell range representing Fruit Items and Quantity of the Incoming Stock sheet respectively. And, the C5 is the starting cell of Fruit Items in the active sheet.

⧬ Formula Explanation:

In the above formula, ‘Incoming Stock’!$D$5:$D$16 acts as the range argument, ‘Main Stock’!C5 is the criteria argument to specify which cells to add, and ‘Incoming Stock’!$E$5:$E$16 is the sum_range [optional] argument. Therefore, the SUMIF function sums up the Quantity if the criteria match the range.

Likewise, create a formula utilizing the SUMIF function for updating the Outgoing Stock Quantity in the active sheet based on the Outgoing Stock sheet.

Later, subtract the Outgoing Stock Quantity from the Incoming Stock Quantity to get the Quantity in Stock.

Here, D5 and E5 are the starting cells of Incoming Stock Quantity and Outgoing Stock Quantity respectively.

Subsequently, you may apply the IF logical function to get an alert message about whether you need to update the stock now or not.

Here, F5 is the starting cell of Quantity in Stock.

⧬ Formula Explanation:

In the above IF formula, the function will show “Update Stock” if the F5 is less than or equal to 10, else it will return “Not Needed”. I used the minimum quantity as 10, however, you can change it if you want.

Lastly, fill up the cells of the Incoming Stock sheet.

And, do the same task for the Outgoing Stock sheet.

Now, it’s time to check whether your created store inventory report is working perfectly or not.

• Just drag down the cursor in the case of the D5, E5, F5, and G5 cells.

Eventually, you’ll get that your Main Stock sheet is updated automatically!

Now, change the entry in the Incoming Stock and Outgoing Stock sheet and check the performance of your business. And, let me know if this Store Inventory Report is working perfectly or not.

Read More: How to Keep Track of Inventory in Excel

Download Practice Workbook

Conclusion

This is how you can maintain store inventory in Excel efficiently. I firmly believe this article will be highly beneficial for you. Anyway, don’t forget to share your thoughts in the comments section below.

Related Articles

• How to Make Inventory Aging Report in Excel
• How to Create Inventory Database in Excel
• Min Max Inventory Calculation in Excel
• How to Calculate Economic Order Quantity in Excel
• How to Calculate Stock to Sales Ratio Using Formula in Excel