Inventory Management Using Tables in Excel

How to create an inventory management system using tables in Excel. One great way to organize our raw data is to use tables, earlier known as lists in Excel, especially if the columns or rows need to expand on a regular basis. You may not know exactly how many headers or columns you will need when you start with a project. Once a table is created you can expand the columns automatically, i.e. as soon as you add a column to your table it becomes a part of the table. Therefore you can say that the table in Excel is a dynamic object. Also when you apply a function in a row cell, it can be automatically used in the cell below it. Of course, this functionality is also available in the normal worksheet cells but you need to do an autofill. We have used the special properties of the table to create an inventory management system that includes an items list, items received, items issued and finally the stock in hand is calculated. Watch the video below:

 

You can watch this video also on YouTube.

The following formulas and functions have been used for the creation of the inventory management:

Received
=VLOOKUP(B3,Table1,2,FALSE)

=VLOOKUP(B3,Table1,3,FALSE)

Issued

=VLOOKUP(C3,Table1,2,FALSE)

=VLOOKUP(C3,Table1,3,FALSE)

Instock
=VLOOKUP(B3,Table1,2,FALSE)

=VLOOKUP(B3,Table1,3,FALSE)

=SUMIF(Table2[Sl.No],[@[Sl.No.]],Table2[Qty])

=SUMIF(Table3[Sl. No],[@[Sl.No.]],Table3[Qty])

We have used the function below to flag the reorder of items:
=IF([Stock on site]<0,1,””)

Here’s also a screenshot for the conditional formatting to create the flag:

Inventory Management Using Tables in Excel
Inventory Management Using Tables in Excel

Further reading:

Overview of Excel tables

Published by

Dinesh Kumar Takyar

Welcome to exceltrainingvideos.com! My aim is to help you learn MS Excel including VBA. I always felt that a good way to learn something was to find solutions to problems in that domain. That is why I share these Excel videos with you. Mostly these are questions asked during my corporate training sessions. I've been training individuals and companies in computers since 1991. I did my B. Sc. (Vordiplom), M. Sc. (Diplom) and Ph.D. (Dr. rer. nat.) from Hamburg, Germany. The best thing about solving some of my visitor's questions is that I also gain new insights. For more Excel VBA solutions you may like to visit my YouTube channel: http://youtube.com/familycomputerclub For a structured Excel VBA training course online you can visit: https://www.youtube.com/excelvbaonline

One thought on “Inventory Management Using Tables in Excel”

  1. Thank you Dinesh, for your great tutorial! I’m trying to set up a way to link this kind of inventory management system, with recipes for cosmetic products. The goal is to know how much product I need to order, to be able to make various products. Do you have a way to do this that you would recommend?

    Thank you so much!

Leave a Reply

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