Inventory Solution in Microsoft Excel

A user has 4 sheets in his workbook called:
1. Items List
2. Received List
3. Issued List
4. Stock on site
From the items list he would like to enter the serial number into the received list and issued list sheets and automatically get the item description and unit. This can be achieved easily using the Vlookup function and a named range from the items list..
The stock on site also displays the relevant item description and unit. The total stock from the received list minus the quantity from the issued list is automatically displayed using the sumif function.
Finally we use the conditional formatting feature of Microsoft Excel to highlight negative quantities in case we have issued more quantity than received.

Watch the training video below to learn how to solve an inventory problem in Microsoft Excel:

Download a sample file by clicking on the Excel icon:

6 thoughts on “Inventory Solution in Microsoft Excel

  1. Dear sir

    if I want to add in this file ‘opening balance’, So how I do it.
    like – opening balance+Received+Issueed =Store Qty.

  2. Good afternoon Boss
    I need your help I hope you will.
    I sow you video about inventory it’s very helpful for me.
    But now I have a problem I want to make inventory but I am fail in making.
    I have items with differents colors for example code 30411 have four colors red-11pcs green-12 blue-13 and yellow-14 I need inventory at the end with colors.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.