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

Leave a Reply

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