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

Here’s the overview of a store inventory management sheet we’ll make.

How to Maintain Store Inventory Report in Excel Quickview

How to Maintain Store Inventory in Excel: 3 Steps

We have some Fruit Items with their Product ID in the Main Stock sheet. We’ll prepare a store inventory report based on the Incoming Stock and Outgoing Stock sheets.

Dataset

Step 1 – Create an Incoming Stock List

In the Incoming Stock sheet, we 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.

Select the C5:C16 cell range.
Go to the Data tab and choose the Data Validation option.

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

You’ll get a drop-down list of Product IDs as shown below.

Use the following formula in D5 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.

If you pick 1001-P2 from the drop-down list, you’ll get Apples in the D5 cell.

Step 2 – Create an Outgoing Stock List

Create the same fields in the Outgoing Stock sheet.

Generate a drop-down list in the C5 cell as done in the Incoming Stock sheet. 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 3 – Maintain the Overall Store Inventory in Excel

Use the following formula 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.

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)

Subtract the Outgoing Stock Quantity from the Incoming Stock Quantity to get the Quantity in Stock.

=D5-E5

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

Apply the IF logical function to get an alert message about whether you need to update the stock information.

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

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.