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.

Dealing with Incoming Stock List

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.

Dealing with Incoming Stock List

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.

Dealing with Incoming Stock List

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

Dealing with Incoming Stock List

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.

Dealing with Incoming Stock List

If you pick 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 2 – Create an Outgoing Stock List

Create the same fields in the Outgoing Stock sheet.

Dealing with Outgoing Stock List

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.

Dealing with Outgoing Stock List

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.

How to Maintain Store Inventory Report in Excel

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

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.

How to Maintain Store Inventory Report in Excel

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.

How to Maintain Store Inventory Report in Excel

Fill up the cells of the Incoming Stock sheet.

Dealing with Outgoing Stock List

Repeat for the Outgoing Stock sheet.

Dealing with Incoming Stock List

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

How to Maintain Store Inventory Report in Excel

Here’s the result.

How to Maintain Store Inventory Report in Excel

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

Download the Template Workbook

Maintaining Store Inventory.xlsx

<< Go Back to Inventory Management in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

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

2 Comments

Musthafa Sep 9, 2024 at 3:40 PM

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?
- Reply
  Shamima Sultana Sep 10, 2024 at 12:37 PM
  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

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

How to Maintain Store Inventory in Excel: 3 Steps

Step 1 – Create an Incoming Stock List

Step 2 – Create an Outgoing Stock List

Step 3 – Maintain the Overall Store Inventory in Excel

Related Articles

Leave a reply Cancel reply