How to Easily Manage Your Inventory in Excel

This tool will be very handy for managing your inventory. If you are searching for an inventory management tool or if you are paying several dollars for an inventory management tool, stop it and read this article to build your own inventory management system in Excel.

Download Now

If you want to start to use the software as soon as possible, then you can:

Download the Software Now

Otherwise, if you want to DIY, you can read the contents below.

Let’s Prepare the GUI

We need 3 sheets to prepare our own inventory management tool using Excel. Rename Sheet 1 as “GUI”, Sheet 2 as “Database” and Sheet 3 as “List”. On the sheet “GUI”, prepare fields to display Product names as drop down and another field to allow the user to enter the quantity as shown in the image. The drop down will help the user to easily pick a Product rather than typing the name.

Let’s prepare the database

On the sheet “Database”,  add headers “Date”, “Product Name”, “Quantity” and “Type” as shown in the image. On the Sheet “List” add these headers “SKU”, “Product Name”, “Description” and “Per Item Price”.

Let’s make it functional

Copy the macro to a new module and attach the macro “StockIN” to the button “StockIn” and the macro “StockOut” to the button “Stock Out”.

Sub p_prod_dropdown()
    Dim lr As Long
    Dim r As Long
    lr = lst.Range("B" & Rows.Count).End(xlUp).Row
    Dim prodlist As String
    For r = 2 To lr
        If prodlist = "" Then
            prodlist = lst.Range("B" & r).Value
        Else
            prodlist = prodlist & "," & lst.Range("B" & r).Value
        End If
        Next r
        With gui.Range("C6:H6").Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:=prodlist
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = ""
            .ErrorTitle = ""
            .InputMessage = ""
            .ErrorMessage = ""
            .ShowInput = True
            .ShowError = True
        End With
End Sub

Sub StockIN()
    Dim lr As Long
    lr = db.Range("A" & Rows.Count).End(xlUp).Row + 1
    db.Range("A" & lr).Value = Now()
    db.Range("B" & lr).Value = gui.Range("C6").Text
    db.Range("C" & lr).Value = gui.Range("C9").Text
    db.Range("D" & lr).Value = "IN"
End Sub

Sub StockOUT()
    Dim lr As Long
    lr = db.Range("A" & Rows.Count).End(xlUp).Row + 1
    db.Range("A" & lr).Value = Now()
    db.Range("B" & lr).Value = gui.Range("C6").Text
    db.Range("C" & lr).Value = gui.Range("C9").Text
    db.Range("D" & lr).Value = "OUT"
End Sub

How does it work?

The Sub “p_prod_dropdown” reads the last used row from the sheet “List”. It then prepares the list of Product names as drop down and saves it on the sheet “GUI”. Now user can use this drop down and select a product. They can enter a quantity and chose “Stock In” or “Stock Out”. The other two macros check for the last available row in the sheet “Database” and save the entry from the sheet “GUI”. When dealing with a huge database it is common to see Excel crashing and getting corrupted. A clean and small database will help you and avoid having a corrupted Excel file.

Tweak it

Graphs shown in the image are not created by the script. However, you can modify the script to create dashboard reports using the data from the sheet “Database”.

Author Introduction:

Nick Vipond is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including fix word and outlook recovery software products. For more information visit www.datanumen.com.