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

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

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

How to Maintain Store Inventory Report in Excel Quickview

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


Download Practice Workbook


3 Steps to Maintain Store Inventory in Excel

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

Dataset

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.

Dealing with Incoming Stock List

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

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

Dealing with Incoming Stock List

  • 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.

Dealing with Incoming Stock List

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

Dealing with Incoming Stock List

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

=IFERROR(VLOOKUP(C5,'Main Stock'!$B$4:$G$16,2,FALSE),"")

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.

Dealing with Incoming Stock List

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

How to Maintain Store Inventory in Excel Dealing with Incoming Stock List


Step 02: Deal with Outgoing Stock List

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

Dealing with Outgoing Stock List

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.

Dealing with Outgoing Stock List


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 also. So, the formula will be like the following in the D5 cell.

=SUMIF('Incoming Stock'!$D$5:$D$16,'Main Stock'!C5,'Incoming Stock'!$E$5:$E$16)

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.

How to Maintain Store Inventory Report in Excel

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

=SUMIF('Outgoing Stock'!$D$5:$D$16,'Main Stock'!C5,'Outgoing Stock'!$E$5:$E$16)

How to Maintain Store Inventory Report in Excel

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

=D5-E5

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

How to Maintain Store Inventory Report in Excel

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

=IF(F5<=10,"Update Stock","Not Needed")

Here, F5 is 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.

How to Maintain Store Inventory Report in Excel

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

Dealing with Outgoing Stock List

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

Dealing with Incoming Stock List

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.

How to Maintain Store Inventory Report in Excel

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

How to Maintain Store Inventory Report in Excel

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 (2 Easy Methods)


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

Md. Abdul Kader

Md. Abdul Kader

Hi! I am Abdul Kader and presently working as ‘Excel & VBA Content Developer’ at Exceldemy. I publish my articles related to Microsoft Excel here. In 2019, I completed my graduation in Urban and Regional Planning from Chittagong University of Engineering and Technology. Having extreme eagerness to learn, I want to develop my skills and capabilities higher.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo