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

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

• Fill up the cells of the Incoming Stock sheet.

• Repeat for the Outgoing Stock sheet.

• Select the D5, E5, F5, and G5 cells and drag the Fill Handle down.

• Here’s the result.

Change the entry in the Incoming Stock and Outgoing Stock sheet and check the performance of your business.

Read More: How to Keep Track of Inventory in Excel

## Related Articles

Get FREE Advanced Excel Exercises with Solutions!

MD. ABDUL KADER is an engineer with a talent for Excel and a passion for VBA programming. To him, programming is like a time-saving wizard, making data manipulation, file handling, and internet interactions a breeze. His skill set extends to SWM, GIS, RS, and SPSS. He holds a B.Sc in Urban & Regional Planning from Chittagong University of Engineering and Technology and has shifted to become a content developer. In this role, he crafts technical content centred around... Read Full Bio

1. I want to prepare manual bin card for all items in stock. All bin cards are in same format and need to fill available information to bin cards. All available items information is in different excel worksheet. How to link and print automatically all items bin cards with item information?

• Hello Musthafa,

To prepare manual bin card you can follow the steps of our article,
1. Design a standard bin card with fields like Item Name, Item Code, Quantity, etc.
2. Ensure your item data is stored in a separate Excel worksheet with columns for Item Name, Code, Quantity, etc.
3. To automatically pull data from your stock worksheet to the bin card, use the VLOOKUP function (or INDEX-MATCH for more flexibility).
For example, if the item code is in cell A2 of your bin card, and your item data is in a worksheet named “StockList,” the formula to fetch the item name might look like this:
=VLOOKUP(A2, StockList!A2:E100, 2, FALSE)
This formula will search for the item code in column A of “StockList” and return the item name from column B.

You can use VBA code to automatically loops through each item in the stock list, populates the bin card template, and prints it. Ensure that your bin card is properly formatted for printing. You can do this by setting print areas and page breaks.
Run the VBA to automatically fill and print bin cards for all items in your stock.

``````Sub PrintBinCards()
Dim itemRow As Integer
Dim lastRow As Integer
lastRow = Sheets("StockList").Cells(Rows.Count, 1).End(xlUp).Row

For itemRow = 2 To lastRow
' Copy item data to bin card template
Sheets("BinCard").Range("A2").Value = Sheets("StockList").Cells(itemRow, 1).Value 'Item code
Sheets("BinCard").Range("B2").Value = Sheets("StockList").Cells(itemRow, 2).Value 'Item name
Sheets("BinCard").Range("C2").Value = Sheets("StockList").Cells(itemRow, 3).Value 'Quantity
' Add any other necessary fields here

' Print the bin card
Sheets("BinCard").PrintOut
Next itemRow
End Sub``````

Regards
ExcelDemy

Advanced Excel Exercises with Solutions PDF