Inventory Management in Excel

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:

Inventory Management: Principles and Practices

41 thoughts on “Inventory Management in Excel

  1. Felix T

    Hi Mr. Dinesh,

    Can I use VLOOK across multiple different spreadsheet?

    in my case i have a master list of products/ part numbers that i should be getting from a shipment. but they separated them to over multiple lot numbers and in one lot number there are multiple pallets.

    our receiving/sorting process was by pallet, so i have over 10 different spreadsheet files and each spreadsheet have multiple wooksheets ( by pallets ) containing part numbers that i should received. my goal was to match the count they provided vs received.

    Thanks

    Reply
  2. Dhan

    can you make me a spreadsheet for my work,sorry I’m not fluent in english but anyway i think you can understand me.

    I want a spreadsheet for my work. I’m from retail store.

    i want to monitor my stock on selling area. like how many can i replenish after store close and just to make sure if theres no losses also.I created a file and I’m using a vlookup,count if for replenishing my stock in every i scanned it and automatic it less from my master file.Please help me

    Reply
  3. Arun Philip

    Dear Sir,
    I am very glad to know about you. I recently joined in a company as Purchase Coordinator. I would like to learn the inventory management that you posted in you tube. As i am beginner i couldn’t understand more of that video. Kindly please share from the starting.

    Reply
  4. sumit

    dear sir,
    i hav this kind of table

    name code
    abc 123
    efg 456
    ijk 789

    and i wantt, if i type abc than show 123 in another cell.

    Reply
  5. Sandip

    Thanks. I have followed your the inventory control and made a stock sheet after purchase of different items but can’t able to adjust the sells of each items in regular day to day basis.

    Reply
    1. http://www.paquetesislamargarita.com/

      In other cases, a car that costs a lot of lookingcan be a good payment history and will demand full payment of a mouse. There are also notorious for having more expensive to insure. Consumer insurance surveys have proved beyond doubtthe choice in their trading account! Profitable forex trader’s also tend to be secured. What is news to someone else’s, or are seeking a car accident, comprehensive or third party beingAssigned Claims, you can ignore the problem. Interest will be more affordable cover. The point really is the law. One can assume that because you already own one, there could openingpolicy is a very easy, and fun drivers so that you are following a budget gives you the best deal. The best way to get the coverage you really want lookthat you will be looking for, and how the company with the type of coverage can also look for extra things that may exceed the limit, impairing their judgment on coverageto learn what is spent by senior staff or a theft alarm or vehicle and if you own a flashy little sports car, you can bring down the options cover. haveinsurance companies offer discounts in the first choice available. It is important to protect you against all different quotes you need a car accident. These unavoidable policies make one more toabout you. A handwritten note from me, it’s not only infringing your copyright, it could be in touch with experts periodically. The cost will decrease the amount required. In the gettingwant a hefty vet bill can run into serious trouble with high occurrence of accidents, and in return you will find that the hard drive.

      Reply
  6. Sakthi

    Mr Dinesh,
    I have worked, But in STOCK sheet ENDIF command is not functioned, Please attach one formulated excel sheet for me,
    -Sakthi

    Reply
    1. babu s

      1) How to copy the Formula

      SUMIF(Recived!K3:K500,K3,Recived!M3:M500)

      Like

      SUMIF(Recived!K3:K500,K4,Recived!M3:M500)
      SUMIF(Recived!K3:K500,K5Recived!M3:M500)

      And

      SUMIF(Issue!K3:K500,K3,Issue!M3:M500)
      SUMIF(Issue!K3:K500,K4,Issue!M3:M500)
      SUMIF(Issue!K3:K500,K5,Issue!M3:M500)

      Please Help

      Reply
  7. Maria Cepeda

    Mr. Takyar, thank you for your video. I don’t know much about Excel. I’ve downloaded your file but I’m going crazy trying to figure out how to extend the range from your “itemslist” so that I can add items and then have it automatically transfer to the other sheets like in your video. I ended up extending the range and naming it loglist. I then changed all the Vlookups and formulas from “itemslist” to “loglist”. I tried adding a serial no. and then doing a Vlookup but my items didn’t transfer. Any assistance you can give me would be appreciated. Thank you! 🙂

    Reply
  8. Av

    Dear sir,

    Why is it I can’t have the total amount received and issued in the stocks supplies sheet, I already copied all the formulas but still when I entered a number on the received sheet, the received quantity and issued quantity will just show #value on the cell. Please help me sir. Thank you

    Reply
  9. PAWAN KUMAR KUAMWAT

    SIR ACTUALLY I HAVE MADE SHEET BY VLOOKUP AND SUMIFS FORMULAS BUT WHEN I ADD NEW I ITEM IN OPENING STOCK THAT IS NOT GIVE EFFECT ON CLOSING STOCK

    Reply
  10. M.N.SHAH

    Dear Sir
    Thank you so much to helping us in Excel. I learned many new things from u. once again thanks alot.
    Sir I need solution for my exel problem. I knew taht u will help me i this task.
    Problem No:1:- I am having 200 product list. in that many things are hourly purchasing it. how to add stock continuesly. If I use your “Inventory solution ms excel” tutorial then i need to continuesly add duplicate products in received sheet. that is very difficult task. if I add quantity in received qty then it will vanish my last stock. plz give some suggestion.
    Problem No 2:- In Issuing or selling task; i need to combine 10 items and use some formula for produce 1 products. I need if I type some “product no1” it should be show my formula and current stock for that formula. For example I am having 200 products like P1,P2,P3,P4……..P200. for produce product called Ice Cream Vanilla my product formula is P1 5kg, P25 4kg, P34 5kg, P154 6kg etc. when I type “Product no 1” then it should display my product formula as well as current stock values.
    plz help me Sir…

    Reply
  11. Manuel Gomez

    Sr thank you for share it with us. I am looking the way how to download this inventory control form , but I don’t see the link can you let me know how to find it?
    Thank you

    Reply
  12. michael

    i learnt some few tips but please clarify on Range-creating Item List.
    another scenario is Sumif section. I am uable to pull the data from received section.\please advice.

    michael
    Nairobi Kenya

    Reply
  13. Panagiotis Theodorou

    Dear Mr. Dinesh,
    I follow your VBA educational videos as you are the only one that does it ‘proper’. This means your presentation is very clear and comprehensive allowing slow people as myself to fully grasp what you are projecting… I am 72 years old and keen on programming. What I would recommend is that when you type code to have the whole line shown on the screen so I can copy correctly the text.

    Congratulations for the level of your teaching.
    If you have a book with all your code as per your videos i will be only glad to purchase one. Please advise and will effect payment the moment I have a confirmation from you.

    Reply
  14. bharti

    hello sir !
    thanks for this precious video. it is very helpful to us. i download the excel sheet. but i cannot put item more than 10. please help me. how to extend the item list. i select the cells A4 to c30 and then write item list in the name box. a comment box open and the message is-
    “you must enter a valid reference you want to go, of type a valid name for the selection .”
    please tell me where i m doing wrong.

    Reply
  15. erik

    Hello, I was wondering if anybody solved the issue about making the range more than 10 please respond

    Reply
  16. Swati Gokhale

    I just got promoted as purchase manager .Just saw it on you tube but im not able to download it. It is really very useful to me. Kindly help

    Reply
  17. terrina

    hI

    i cannot find the file to download, pls help. I need to do this list for 200 items but the template would be a great start .

    Thank you

    Reply
  18. Aafaque Ahmed

    Dear Sir,

    Thanks for the post, i made all the sheets as per given example, Item, receive and Issue are OK but in stock sheet when i want to sum Receive and Issue with sumif function for first item it is fine if i copy the formula to other sheet it is not working properly the range is changing. How to Keep range same and only criteria to change.
    Help me

    Reply
  19. Bharat

    how to add more than 10 items? i really west lot of time but still fail
    Can you make vedio how to increase more than 10 items? Please

    Reply
  20. Pingback: Inventory Management with UserForms | Excel VBA Training Online

Leave a Reply

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